MrExcel Excel Videos

From yesterday's live seminar, a new way to fill quarters and years with the fill handle.

We have a person in our roster with the first name of "Witha". But Excel constantly changes this to "With A". We are being stung by one of the thousands of entries in the Excel AutoCorrect Options dialog. This video will show you how to stop Excel from autocorrecting Witha to With A.

Brand new in Excel Insiders Beta: A chart can be based on an entire dynamic array in Excel. In this first iteration, the chart has to point to the entire array. If you have columns in your array that you don't want to chart, you should use =CHOOSECOLS to get a smaller subset of the array for charting.

Laura B has a tricky Power Query question today. She needs to delete all rows where B=C and D=E. Bill solves this in the Power Query Editor but knows it is not efficient. He turns to ChatGPT - an Artificial Intelligence tool that is in a free research preview. Bill is amazed at how quickly ChatGPT writes M code for Power Query. But then he bangs his head against the wall trying to get it to work. See why Stack Overflow has banned ChatGPT because it generates code that is usually wrong but looks like it could work. Finally, we turn to Smozgur, author of the upcoming "You wouldn't write professional VBA code using the macro recorder would you? A Programmer's Guide to M" book for his efficient solution. As a bonus, Bill shows...

Happy New Year! I was scrolling through Facebook when Maria O pointed out that 2023 is 7*17^2. That seems interesting. Are there other times in our lifetime that the year is N*NN*^N? It last happened in 2020. We have a run of them, coming up: 2023: 7*17^2 2025: 9*15^2 2028: 3*26^2 But even cooler is 2025, which is the perfect square of 45^2. Unless you were alive in 1936 or will be alive in 2116, the square year of 2025 will be the only square in our lifetimes.

I have a series of values that goes like a sinus curve. I would like to find each top value in the curve. Is there a function for that? In today's video, Bill uses AND, IF and FILTER to find the peaks from the data.

Another question from the UCF Accounting Conference. JoAnne asks... XLOOKUP can return multiple columns. What if you want to return several non-adjacent columns, for example, January, April, July, October? There are three solutions presented here. Table of Contents (0:00) XLOOKUP return non-adjacent columns (0:30) XLOOKUP with FILTER (2:39) Using HSTACK (3:17) VLOOKUP with Ctrl+Shift+Enter (4:40) How would you solve it?

You've probably seen the story of Chelsea Banning in the news lately. 37 people RSVP'd to her book signing and only two showed up. That is DOUBLE the turnout for my Manhattan book signing in 2003. Also in the episode: two faster ways to generate all 729 combinations of the outcome of World Cup Group Match Play. Thanks to Exceλambda and Kyle F. for faster ways to generate all combinations of 0, 1, and 2 for 6 World Cup Group Stage Matches.

The World Cup is now in group stage. Each group has four teams. The top two teams advance to the knockout stage. This video explains the 7 tiebreaker steps, and uses Microsoft Excel to analyze the 729 permutations that can happen in any one group. How likely is it that the tiebreaker will need to be used? Table of Contents (0:00) World Cup Group Stage Scoring (1:00) Tiebreaker rules (1:50) Odds of a tiebreaker (2:20) Excel Model for one group (2:50) TEXTSPLIT function (3:22) Excel Trace Dependents (3:45) Scoring & Sorting (4:10) Detecting a Tie (4:41) Why 729 permutations (4:53) 28% tiebreaker chance (5:46) Six draws leads to 12 points (6:04) 18 points total (6:40) Which results have a tie? (7:28) Excel behind the scenes (8:00) 7...