MrExcel Excel Videos

You have a column of data in Excel. You need to reverse the column, bottom to top. How can you do this? Can you do it without a helper column? Bill Jelen and Mike Girvin compare six ways Table of Contents (0:00) Description of problem (0:49) Bill: SORTBY and SEQUENCE (2:15) Mike: INDEX and Converging range (3:23) Mike: INDEX and SEQUENCE (5:22) Bill: Custom List (6:43) Mike: LAMBDA (8:49) Bill: Power Query

There is a classic Financial Modeling problem in Excel. You are checking values in each month and you end up with a row of zero and one values. How can you find the last month where you have a one? Dan explains the classic technique of a Helper Row to identify the last one and then INDEX and MATCH. Bill uses the Last to First Search Mode in XLOOKUP. But then the shortest method - MAXIFS as suggested by Diarmid Early. Table of Contents (0:00) Welcome and the problem (2:54) Solving with a helper row (6:00) Using XLOOKUP (7:47) Using MAXIFS

How can you reverse text in Excel? There is not a REVERSE function. You've recently seen me do this with a VBA loop. Lianna Gerrish from the Financial Modeling World Cup Semi-Finals would flip it and reverse it with strREVERSE in VBA. Dan Mayoh and Jose Carlos Canejo both sent in a way to do it with native Excel functions. But... how do you ever remember CONCAT, LEFT, RIGHT, SEQUENCE, LEN? In a special guest appearance on the 40th anniversary of the release of 867-5309 (Jenny), the lead singer of Tommy Tutone joins us with a song from his upcoming album - Music for Fellow Nerds. Listen to "Search in Reverse" a few hundred times and you will have it down! Also in this episode: Bill's secret skill to say the alphabet backwards. One of...

Matching data between two Excel worksheets where you need to match both the city and the tax rate in order to find the match. This video shows three different methods. Table of Contents (0:00) Welcome (1:01) Using XLOOKUP for two-way match (2:15) Using VLOOKUP with concatenated key (3:18) Using three queries in Power Query for two-way lookup

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

You have 100 people coming to a conference. There are 11 sessions. Tables seat 10 people each. How can you set up a seating chart for the 11 sessions so everyone has a chance to meet everyone else at least once? Bill tries brute-force Monte Carlo and can't get better than 65% of the way to the goal. Is there a better way? Download my workbook from here: Arrange100People.xlsm

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?