MrExcel Excel Videos

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

The Round menu option in Power Query does not round the same way that Excel rounds. This is a shorter video covering the same information that Excel MVP Celia Alves covered in episode 2391. In order to have Power Query ROUND and Excel ROUND return the same results, you have to edit the formula in Power Query to add RoundingModeAwayFromZero. This video shows you how.

Excel has specialty rounding functions CEILING.MATH and FLOOR.MATH. Think of them like a ROUNDUP version of MROUND and a ROUNDDOWN version of MROUND. Why did they not call them MROUNDUP and MROUNDDOWN? That is a great question. Today, I show you how CEILING.MATH and FLOOR.MATH work and the equivalent formulas in Power Query.