MrExcel Excel Videos

The Excel team snuck a new feature into Excel and I didn't realize it for a while. Thanks to Excel MVP Abiola David for realizing that you can left-click any number in the status bar to copy that number to the clipboard. You can then paste to an e-mail or a text file or, I guess, even to Excel. This also has a throw-back reference to the 2012 book, Don't Fear the Spreadsheet, available here: Don't Fear the Spreadsheet

Jon wants to generate 6-letter sequences using only the letters B and P. So, for example: BBBBBB, PPPPPP, BPBPPB, BBBPPP, and so on. There are 64 such combinations and Bill shows you one way to solve this use BASE and SUBSTITUTE. Formulas used for one word: =SUBSTITUTE(SUBSTITUTE(BASE(RANDBETWEEN(0,63),2,6),"0","B"),"1","P") The red box suggested a shorter formula of: =CONCAT(CHAR(66+14*RANDARRAY(6,1,0,1,TRUE))) Formula for all 64 words, in sequence =SUBSTITUTE(SUBSTITUTE(BASE(SEQUENCE(64,1,0),2,6),"0","B"),"1","P") Formula for those 64 words sorted randomly =SORTBY(SUBSTITUTE(SUBSTITUTE(BASE(SEQUENCE(64,1,0),2,6),"0","B"),"1","P"),RANDARRAY(64)) If you have the number of letters in A3, then generate all words in sequence...

December 4th 2021 is a red-letter day for Microsoft Excel. It is the first day that an Excel competition was carried live on the ESPN family of networks. Watch again on Wednesday December 8: Schedule | Watch ESPN On on December 11 2021: https://www.espn.com/watch/schedule/_/type/upcoming/categoryId/634d383d-6f26-39cd-9efa-feb4eff23547/channel/3e99c57a-516c-385d-9c22-2e40aebc7129/startDate/20211211 In this video, I take a stab at getting 310 points on the Feeding Excelopolis case study. Thanks to the new FILTER function, LARGE with SEQUENCE, and a What-If Analysis Data Table, I have a way to get to 310 points.

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.