MrExcel Excel Videos

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...

In this video, several of my friends and I propose ways to cut a 75-minute Excel process down to under 15 seconds. As expected Diarmuid Early posts the fastest time with an under-10-second solution. But Diarmuid warns about a possible problem in the data, which makes a method proposed by Peter_SSs and Anup Agarwall the fastest and safest method. The formula methods from others: https://www.mrexcel.com/board/threads/excel-insert-blank-row-after-every-two-data-rows-shorts.1217116/ https://www.mrexcel.com/board/threads/insertblankat.1217374/ Table of Contents (0:00) Hurricane Ian delay (0:34) 2-minute solution for OP (1:11) 53-Second solution (1:30) Too Slow (2:10) Fastest Excellers (2:30) Anup Agarwal (3:07) Diarmuid Early I (4:06)...

Lasma wants her pivot table to group daily dates up to months and years. But she has some empty rows and the Group Field is greyed out. In this video: Surprise! Microsoft 365 now allows blanks in the date field and you can still group. Solution 1: Fill blank date fields with 2199 December 31 Solution 2: Add your own Year and Month fields to the data set And, a warning from Debra Dalgleish at Contextures: if you mix dates and text, the Group Field will still be greyed out. Table of Contents (0:00) Pivot Group Dates with Blanks (0:15) Fix from Excel Team (1:10) Select empty date cells (2:09) Add Year Month to source data (2:40) Month Name from date using TEXT (3:30) Debra: mix of dates and text (4:00) "empty" cells by spacing through...

Kelly wants to display a Delta in a Pivot Table. Unfortunately, Calculated Fields in a Pivot Table look at each individual row, so the MAX(B2)-MIN(B2) will always be zero. In this video, a DAX Measure solves the problem. But to unlock DAX, you need to choose Add This Data To The Data Model. Table of Contents (0:00) Need Max minus Min in pivot table (0:31) MIN and MAX are easy (0:51) Calculated Field fails (1:51) Add to Data Model (2:51) New Measure (4:25) Wrap-up