MrExcel Excel Videos

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

Wyn Hopkins built an Excel-based Wordle game. He saw my video on my Wordle Helper tool and challenged me to solve his game with my Excel file. The results are in. Wyn's video challenging me to play: Wyn's How to Build a Wordle Video: Wyn's Game is at this blog post: https://accessanalytic.com.au/build-wordle-using-dynamic-array-functions/ My Wordle Helper Download: https://www.mrexcel.com/news/wordle-solver-in-excel-get-wordle-hints/

Sheet view in Excel is useful if multiple people are editing the same workbook. Say that one person wants to filter to Open orders and the other wants to see closed orders. One wants the data sorted descending and the other wants it sorted ascending. Some want to hide some columns. Sheet view can help with this. Table of Contents (0:00) Sheet View is better than legacy file sharing (0:54) Saving for collaboration in Excel (1:45) Opening the same workbook on another computer (1:59) Changes on one computer sync to all computers (2:25) I want to see some records (2:41) Avoid the "See Just Mine" button (3:00) Excel Help is wrong: Default View isn't working (3:28) Creating a new Sheet View for See All (4:00) Create a view for Bill records...

The F4 key in Excel has a dual life. Most people know F4 as the way to put dollar signs in a formula reference. But when you are not editing a formula, F4 will repeat the last command. Some of you know this as Ctrl+Y, but F4 also acts like Ctrl+Y and is easier to press (for me, anyway). Today, I had an odd problem where I had to delete every other column for 10 columns. Instead of writing VBA, it is faster to do the first delete for Alt+O, C, H and then repeat with F4. Right-arrow twice to the next column and F4 again. It is a quick solution. There are so many solutions where F4 is useful. This episode also takes a trip over to Excel Online. Excel Online is an experimental version of Excel running in a browser, meant to compete...

An update on video 2458. There is a setting about how Excel interprets a two-digit year. Microsoft changed this setting from 2029 to 2049. But Excel refuses to accept the 2049 setting. Unless you change the setting in Control Panel. You can even set it to 2050 and then back to 2049 and then it starts to work.

It is a little-known feature that you can automatically break the text in an Excel Textbox into multiple columns. This episode shows you how to find the setting.

If you type a date with a two digit year, Excel treats anything up to 29 as being 2029. Once you get to 30, though, they assume you mean 1930. With 2030 only eight years away, it might be time to adjust this. Microsoft tries to document how, but the Help topic does not match the Windows 10 Control Panel. This video shows you how to find the settings. Fixing the 2029 Rule in Excel. For a Mac, it is Excel / Preferences / Edit, then follow the screenshot here: