MrExcel Excel Videos

Learn Excel from MrExcel - Excel tips and tricks from Bill Jelen.

Reverse A Column In Excel - With Mike Girvin - Duel 196

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
View full article >

Find The Last One In A Series Of 1s And Zeroes With Dan Mayoh - 2445

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...
View full article >

Excel Search In Reverse (Jenny) - feat. Tommy Tutone - 2444

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...
View full article >

Excel Two Way Lookup Three Ways - 2443

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
View full article >

Excel List All Lottery Combinations - 2441

Reid would like to list all 6-number combinations of the numbers 1 to 44. For example, 1-2-3-4-5-6, 1-2-3-4-5-7, and so on up to 39-40-41-42-43-44. The first thing to realize is that all lottery combinations are a lot of numbers. Over 7 million possibilities according to the COMBIN function in Excel. (For Power Ball, there are 292 million combinations!). Listing all combinations will be difficult because Excel only includes 1,048,576 rows. In this video, I show how to enable Macros in your...
View full article >

Excel Find Last Item In List Using MATCH But Not XMATCH - 2442

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...
View full article >

Paste Across Computers But Not Paste All - 2440

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...
View full article >

Lambda Functions Debut In Excel - 2381

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)...
View full article >

Solving Word Search With Excel (FMWC) - 2439

A challenging puzzle from FMWC - Financial Modeling World Cup. How to solve a Word Maze or a Word Search using Excel. This maze includes 20000 letters! You have 30 minutes to score 1000 points using Excel formulas or VBA. If you have any solution for finding words diagonally, leave a comment. If this looks like something you would be good at solving, consider joining the competition. Excel as esports: sign-up for an FMWC Open - a competition where every Excel user gets a chance to show...
View full article >
You must log in or register to post here.

Forum statistics

Threads
1,148,274
Messages
5,745,796
Members
423,973
Latest member
man_this_is_hard

Share this page

This Week's Hot Topics

  • separating multiple Proper names (uppercase letters) from a list
    • Started by Keebler
    • Replies: 35
    I have a list of proper names that only has spaces between their first and last names. I need a formula that can separate these names into...
  • 'for' Loop
    Hello guys, I am trying to do something new. With the help of a VBA code, in every case, last 2 Values of last 2 cells of columns D-15 to D-28 to...
  • Open specific pdf in folder with vba
    • Started by ipbr21054
    • Replies: 34
    Hi, Below is the code in use , unable to find rich icon to put code inside using a mobile. The code in use is shown below. Everything works as it...
  • What is wrong with this For Loop code?
    • Started by thardin
    • Replies: 20
    I am trying to loop through each cell in Column U from U4 to last row to check if the percentage in the cell is greater than -.050%. In a...
  • Data to match
    • Started by dinkss
    • Replies: 20
    Hi there, I have created 2 worksheets with data. First is all materials used and second has the bottle type used for each of material. How can I...
  • Shifting Columns
    • Started by Andresleo47
    • Replies: 19
    Hi Dear Community, I'm trying to find the best way to Shift (Cut - Paste) all the information below and to the right of the "Worker" cell in VBA...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top