Amy from the St. Louis IIA asks: Is there a way to number the visible rows in a filtered list? This video shows how a formula of =SUBTOTAL(3,B$1:B2)-1 in cell A2 solves the problem.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
MrExcel Excel Videos
Learn Excel from MrExcel - Excel tips and tricks from Bill Jelen.
Holly has a data set with Subtotals. She needs to insert a blank row after each subtotal row and add a calculation to those blank rows.
Table of Contents
(0:00) Welcome
(0:15) Go To Special Tricks?
(0:47) Subtotals
(1:10) Helper column on Total rows
(1:50) Move 1's down a row
(2:28) Select just 1's & Insert Rows
(3:00) Select blank cells
(3:32) Entering the formula
(4:30) Favorite Go To Special
What if you have an Excel Slicer with hundreds of values? Today, an awesome trick from Tine at Zebra BI that allows you to replace that huge slicer with one cell.
Table of Contents
(0:00) Welcome
(0:15) One cell to filter
(0:42) Start with a slicer
(1:07) Copy the pivot table
(1:23) Remove fields except slicing field
(1:50) Copy PT to dashboard & hide half
(2:30) Printable list of steps
Display pictures on your Excel Dashboard using an Excel IMAGE formula. This episode has a deeper dive into the function and how to use it.
(0:00) Welcome
(0:24) Finding Image Address
(0:54) Building Image URL
(1:30) Using IMAGE function
(1:52) Hover for Pop-out Image
(2:30) Pivot Table
(2:55) Formula to Display All Images
(3:04) FILTER to convert to array
(3:15) IMAGE function
(3:20) TRANSPOSE for 2501 Bug
(3:35) WRAPROWS function
(3:45) Excel MVP by Country
(4:10) Caption under picture but how?
(5:19) Caching images #BUSY! error
(5:57) Insiders beta now
While working on video 2500, I ran into something that seems buggy with the WRAPROWS function in Excel. Is it a bug? Or a Feature?
D on YouTube asks: How do I stop Excel from removing leading zeros when opening a file?
Changing a column from general to text does not work for me, as the “0” has already been removed.
09805 becomes 9805, for example.
The files I’m opening does not make it clear that the zero has been removed.
I'm working in spreadsheets with 200k rows.
So stopping Excel from removing them in the first place, would be preferable
Wyn's book for shipment to USA:
https://mrexcel.e-junkie.com/product/1750249/Power-BI-for-the-Excel-Data-Analyst-Print-Book-to-USA-Only
This video covers six exciting things:
1) The Show Changes feature which had been in Excel Online is now in Desktop Excel
2) Data Validation partial matching is working again
3) There is a new look for Excel Options
4) A new IMAGE function comes to Excel to display an image
5) Wyn Hopkin's new Power BI for the Excel Analyst book... it drops on November 1, but I have 3 cases to ship now.... US only.
6) The 14 functions for text and array manipulation are coming to general availability.
Table of Contents
(0:00) Four new features today
(0:54) Show Changes made in last 60 days
(1:30) Validation List...
TKH is using the new STOCKHISTORY function in Excel. He is looking for stock prices from 7, 14, 30, and 60 days ago. When you are asking for a single day, the STOCKHISTORY function returns a #VALUE error if the market is closed on the desired day.
This video shows how to use the old WORKDAY function to make sure your date never ends on a holiday or weekend.
Jeremy asks Is there a way to select multiple headings and change all of their "Summarize value field by" at once? I have hundreds of headings, each time I want to change their value by Max, Min, Average, I have to select one heading at a time, it is extremely labor intensive. Is there a way to change field calculation for multiple headings at once?
This episode shows some simple VBA to do this quickly.
How to create a personal macro workbook:
Table of Contents
(0:00) Welcome
(0:41) VBA
(1:55) Looping each field
(2:35) Copying for Sum, Max, Min
(3:10) Other calculations
(3:42) Easier way to run
(4:30) Running all 4 macros
(5:25) Upcoming Webinar
