MrExcel Excel Videos

Danny from Stuart Florida noticed that when you group all worksheets in an Excel workbook, you can not protect or unprotect the worksheets. This episode offers two very short VBA macros that you can use to unprotect all sheets and then re-protect them with a password.

Holly from Vero Beach gets a report each week with Member ID. She needs to pull in the Member Name from last week's report. This involves doing a VLOOKUP from one workbook to the other - Holly's employers is on Excel 2016 instead of 365.

Laura from Nashville is looking for a way to make sure that all future pivot tables default to having No Subtotals. This *was* a feature in the PowerPivot Add-in back in Excel 2010, but then was removed. However, thanks to Pivot Table Defaults, you can force all future pivot tables into a flat pivot table that is ready for re-use.

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.

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