MrExcel Excel Videos

There are a pair of well known Excel tricks with MATCH: Find the last item in a long list of items that might contain empty cells. Find the only number in a range of Error or Text values. Excel tricksters have made use of these. Why do they work? And why do they not work with the new, superior XMATCH? Thanks to Jose for sending this question in. In this video, we dive in to how the binary search works in MATCH. We see how to adjust XMATCH to find the last item in a list. But there is no way to have XMATCH find the only number in a column of error cells. Which brings up the bigger question.... Why does this work at all with MATCH? It violates the rules of binary search. Table of Contents (0:00) Why can't XMATCH do what MATCH can...

The Windows Clipboard (in Windows 10 or Windows 11) can now sync across devices. Copy from one computer and paste to another computer! No more mailing stuff to yourself just to get from this computer to the other. (Here at my office, I have a file called PassToOtherMachine.txt in Dropbox and use that daily. Sync across devices is amazing. But the Windows clipboard also has a Clipboard History feature that is missing an important button: Paste All. I will show you the very obscure feature in Excel that will let you collect Clipboard History and Paste All.

Ground-breaking news in Excel. The partner to the LET function was just released. Check out the new LAMBDA function in Excel. This makes the Excel formula language Turing Complete. Store formula logic in a name and pass parameters to that logic. Link to the Brian Jones article: Announcing LAMBDA: Turn Excel formulas into custom functions Table of Contents (0:00) Introduction to Lambdas and Revenge of the Nerds (0:26) Alonzo Church and Alan Turing (1:52) First LAMBDA: Case Quantity (3:10) Hypotenuse of a Triangle using HYP (3:49) Area of a Pizza (4:55) Using Data Types - Distance Between Two Cities (5:52) Recursion example from Brian Jones (6:24) Wrap-up

My former co-worker thought it was a good idea to hide all of the whitespace below our Excel data and managed to hide a million rows and 16000 columns. How can I unhide something at the edge of the worksheet?

"My spreadsheet must be haunted" is a great opening line to an Excel question sent in just before Halloween. You have 1510 rows in Excel. You try to Delete Sheet Rows from A1511 to A1048576, but the rows keep coming back. How can you delete all of the whitespace at the bottom of Excel? How can you delete unused rows at the bottom of Excel? How can you delete unused columns to the right of your Excel data? The solution is to hide the extra rows and columns.

How to use XMATCH with Application.WorksheetFunction in Excel VBA? Today, a question from Hydrogen Peroxide. How can you refer to an array inside of a formula in Excel VBA. This workbook takes a look at how to simulate an Excel function in VBA. We start off with a simple one, just as MID which is already built in to VBA. Then, for a function like VLOOKUP that is not in Excel VBA, you can use Application.WorksheetFunction. However, today's question needs to concatenate two ranges and then pass that to Application.WorksheetFunction and this does not work. The solution, thanks to Brad Yundt, is to do the concatenation inside of Application.Evaluate. Check out Brad Yundt's answers on Quora: Brad Yundt

Can you use the MAX function in Excel to find the largest value across all worksheets? You can use a 3-D Reference or a spearing formula. In this episode, I will show you an easier way to create 3D References in Excel. Also, a trick from Mack Wilk for making your 3D Reference go through a blank sheet at the end. For more information about the book, MrExcel 2021 Unmasking Excel, visit: MrExcel 2021 – Unmasking Excel

Your manager asks if you can add a blank row after each subtotal. Episode 2433 shows you how to do this in Excel. The first way, is clearly cheating: Increase the height of each row. But the second way is the real way, using Ctrl+Enter, Select Visible Cells, Insert Cells Shift Down, and Insert Rows.

Geert asks: "in my experience the datasets are non-uniform in the sense that only a subpart of the data needs subtotals. (categories of data are sequentially listed and only 1 category has a further breakdown that needs subtotals – the other categories do/must not) Is it possible to do that using the buttons on the ribbon? This episode shows how to add subtotals to each customer in the manufacturing region.