MrExcel Excel Videos

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

You wrote some IF functions to assign a score based on numerical ranges. You want to combine those formulas into a single long Excel formula. This video will show you how to do it and how to avoid some pitfalls. Along the way, you will see IF, AND, IFS, LET, CHOOSE, INT, VLOOKUP, LOOKUP, XLOOKUP, and XMATCH. There are really two videos here. The first video shows how to combine 120 characters of IF formulas down to 49 characters. After 5:40 in the video, this turns into an advanced video on how to shorten the formula. Table of Contents (0:00) Two videos today from one question (0:23) How to assign scores to ranges in Excel (0:42) Solving with an IF formula for each range (0:58) Using AND with IF in Excel (2:18) Liam Bastick's Rule of...

Your co-worker has an Excel formula and when you press Enter, you get "You've entered too many arguments for this function." You can't even get out of the formula. Today, I show how the lowly Function Arguments dialog can help you to isolate the problem.

Everyone has their theories on the best starting word for the Wordle game. A lot of people suggest ADIEU. But when you look at the frequency of each letter in the 8000 possible words, a better start word has AROSE. This video starts out with the most popular words. After that video wraps, a deep dive into the Dynamic Array Formulas that made all of this possible. Table of Contents (0:00) What's the best Wordle Starting Word? (0:48) Statistical analysis of most common letters (1:20) A few alternate best starting words (2:11) Scoring ADIEU and other common starting words (2:49) Learn Excel on Retrieve (3:10) Organist Nancy Faust plays (3:21) Using UNIQUE with By_Col set to True (3:50) VSTACK from Charles Williams (3:55) Getting rid of...