MrExcel Excel Videos

I recently made a guest appearance in Ann K Emery's class on data visualization. Over the course of an hour, I showed a few Excel tricks. This one, about Custom Lists was recently discussed in the YouTube comments for another video. Check out Ann's blog: Blog | Depict Data Studio

Another question from Columbus, Indiana. How can you choose a default style (from the PivotTable Design tab) for all future pivot tables? Yes, there is a Set as Default choice, but it only applies to future pivot tables in this workbook. My method involves adding a default to BOOK.XLTM. Also in this video, a pivot table formatting oddity from Excel MVP Wyn Hopkins. Table of Contents (0:00) Introduction (0:44) The colors on Page Layout impact the Pivot Table Styles (1:25) Changing a pivot table style default only affects this workbook (3:40) A FAST way to get to Browse during Save As (4:54) MVP Wyn Hopkins Discovers a bizarre pivot table formatting trick! (5:33) Change the colors and style of your borders using Format Cells (7:23) Pivot...

Another cool discovery by Bob Umlas. Say that you need to add a Total, Average, Max, and Min to the right of a data set. The AutoSum drop-down menu in Excel offers all four features. But how can you use them without the previous Sum getting in the way? Bob discovered a cool way.

A question from my Columbus Indiana virtual seminar: How can we sort a data set by Absolute Value? They have columns with Forecast, Actual, and Variance. A large variance is bad, whether it is positive or negative. My solution today involves using a Dynamic Array and the SORTBY function. Also: using a helper column for people who don't have dynamic arrays.

If you have a consumer edition of Microsoft 365 (personal or family), check out the new Wolfram Data Types which are reaching general availability. In this video, I show you how to navigate the Card for a cell with a data type and then show you how to use formulas to pull the card data into the grid. Note that these are only licensed for those using Excel in English. If you are using Excel in another language, add English as an editing language and restart Excel twice.

We noticed it first on April 20, 2021... the Get & Transform Data group's "From Table/Range" changed to "From Sheet". Hover over the tooltip, and you will discover that they now support arrays! This is great news. Power Query is the best thing to happen to Excel. Dynamic Arrays are another great improvement in Excel. The situation that they could not work together before this was unfortunate. Kudos to the Power Query team for providing the functionality! Not that this is currently released to the "Beta" channel (Formerly known as Insiders Fast). It will take some time to roll out to all Microsoft 365 customers.

Vivek asks: After choosing from the Filter drop-down menus, how can you get a count of the unique values from just the visible rows? This video starts with a helper column to detect if each row is visible and then uses COUNTA, FILTER, and UNIQUE functions to solve the problem.

Bob Umlas has a new book out. https://www.mrexcel.com/products/cool-excel-sh*t/ While I was working on indexing and layout of the book, I learned a lot of new Excel techniques, including this cool way to interpolate values. If you enter a starting value in A1 and and ending values in A22, select A1:A22 and use Fill Series. Excel will automatically calculate the step value to create a perfect line.

Here is a cool example of a LAMBDA function with a LET and XMATCH function. Point to two cells that contain movie names and the function will find the cast who is in common between the two movies and provides the photo of those people in common. If you want to play the Six Degrees of Separation of Kevin Bacon, download the Excel file from here: Tom_Hanks_From_Excel_Lambda_And_Let_2397.xlsx