MrExcel Excel Videos

Antony in Toronto brings us two more Excel AutoSum Oddities. This was part of my hour-long session on Excel Oddities, Curiousities and Mysterious Wonders for the Vancouver Modern Excel Users Group. Watch the entire hour here:

Doug from Boston needs to calculate an age in Excel. Two easy formulas today: YEARFRAC and DATEDIF.

The icons for Rows & Columns in the Pivot Table Field List are backwards. They've been backwards for quite some time. Unless, it is actually a garden hose, spraying data across the top of the report… Thanks to Nicole in Appleton for that memory aid! Also in this Friday episode: Classic Pivot Table layout, Blank Cells no longer cause a Count instead of a Sum. To download this workbook: https://www.mrexcel.com/download-center/2018/11/the-excel-data-hose.xlsx List of upcoming seminars: Excel Seminar Schedule

Today's question from Ken: I've lost a slicer. I know it is somewhere on the worksheet, but how can I find it? Thanks to Lou Pham, we have an answer. Use Home, Find & Select, Selection Pane. Select the object. Press F2 to bring it back into view. Also covered today: You can format a drawing shape and then set AutoShape Defaults on the right-click menu to change the color of future shapes. Right-click the Shape in the gallery and choose Lock Drawing Mode to create many shapes with a single click for each shape. If a worksheet becomes super-slow, check Home, Find & Select, Selection Pane to see how many shapes there are! With 1400 shapes to draw, Excel becomes very slow. Use Hide All to make the spreadsheet fast again If the active cell...

Gagan has sales for three years. For each month in those three years, what is the first date that had a sale? For some reason, Gagan needs to do this without a helper column. My first solution is a pivot table using MIN of Date in the value area. Note: Excel is changing the label when you group daily dates in a pivot table. Later, I show a formula using XLOOKUP and "Equal or Just Larger". both in a column or in conditional formatting. But then, when I try to use MINIFS, I can not get it to work without a helper column, possibly due to the array of arrays problem.

Fourteen new functions in Excel: TEXTSPLIT, TEXTBEFORE, TEXTAFTER, VSTACK, HSTACK, CHOOSECOLS, CHOOSEROWS, DROP, TAKE, EXPAND, TOCOL, TOROW, WRAPCOLS, WRAPROWS. Links to Help topic for each: TEXTSPLIT: TEXTSPLIT function TEXTBEFORE: TEXTBEFORE function TEXTAFTER: TEXTAFTER function VSTACK: VSTACK function HSTACK: HSTACK function CHOOSECOLS: CHOOSECOLS function CHOOSEROWS: CHOOSEROWS function DROP: DROP function TAKE: TAKE function EXPAND: EXPAND Function TOCOL: TOCOL function TOROW: TOROW function WRAPCOLS: WRAPCOLS function WRAPROWS: WRAPROWS function Table of Contents (0:00) Overview of 14 functions (1:00) Split text with TEXTSPLIT (2:17) Join arrays into a column with TOCOL (2:42) Stack arrays with HSTACK (2:58) DROP the first row...

After my previous video showing AROSE as the best first guess in Wordle, I started doing some more cool Excel formulas to test all Wordle words. This is a long video for the weekend, mostly of me geeking out about Excel and doing 1.5 billion calculations with a $10 spreadsheet. For a great Wordle Analysis from Laurent Lessard: Solving Wordle - Laurent Lessard Table of Contents (0:00) Introduction (1:15) Colors in 53 formulas (6:10) Laurent Lessard or 3Blue1Brown (7:25) The 2315 words (8:50) Solving for 5.3 Million with 284 MM formulas in Excel (10:40) The 10,972 words (11:57) 2315 x 12972 x 53 of 1.3 billion (13:00) Wordle Universe 1.596 billion calcs (14:25) Can be the answer? (14:59) Count unique constellations per guess (17:17) It...

I love the Wordle game. One game a day. Three minutes. As I played for the first week, I wondered if Excel could help. It can! Note that this workbook requires a recent Excel version due to the FILTER formula. You either need Microsoft 365 or Office 2021. Download the workbook from here: https://www.dropbox.com/s/9ef329ct1isnrfb/WordleHelperFromMrExcel.xlsx?dl=0 Watch the video for details on how to use the workbook. Play Wordle Here: Wordle - A daily word game

Big news today for Win / Mac / Online versions of Excel. A new Advanced Formula Editor will make LAMBDA and LET far easier. Among the highlights: New Advanced Formula Editor (Windows, Mac, Online) Share Lambda through GitHub Better Recursion Limits (16x larger!) Formula AutoComplete Read Chris Gross article: https://techcommunity.microsoft.com/t5/excel-blog/announcing-lambdas-to-production-and-advanced-formula/ba-p/3073293 Try downloading Lambda from Gist.GitHub https://gist.github.com/chgrossMSFT/d172fd552cf6893bcdc7942223cb0e55