MrExcel Excel Videos

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:

Tina C asks if there is a better way to add a page break every 35 rows. Way back in episode 360, I showed a few methods. But today, one line of VBA in the Immediate Window seems much easier. You have to edit this line in three places: For i = 37 To 1446 Step 35 : ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells(i, 1) : Next

Evil Angel on YouTube asks if you can do a VLOOKUP in a Pivot Table Calculated Field. I am fairly sure this won't work in a regular pivot table based on the pivot table cache. But if you build a pivot table using the Data Model, then you can build a DAX Measure that will do the equivalent of a VLOOKUP. This video shows how to create a relationship between the lookup table and the data table. It shows how you can pull in text fields from the lookup table. It shows how to use the =RELATED function directly in the power pivot grid, but this calculation fails on the total row. Finally, it shows how to use a Measure to perform the calculation. Table of Contents (0:00) Can you VLOOKUP in a pivot table calculated field? (1:02) Converting data...

Today a question from someone who frequently uses three currency styles in Excel: USD, EGP, and SAR. Excel makes it easy to choose USD, GBP, EUR, CHF but if your currency is not in that drop-down, then you are scrolling through an insanely long list. It seems that there is no good way to change the five currencies in the drop-down on the Excel home tab. Instead, I propose a scheme where you can add SAR or EGP from a button on the Quick Access Toolbar and that button will also add the currency to the Cell Styles gallery that travels with the workbook. This means that your co-workers can choose SAR or EGP without having anything in their personal macro workbook. Table of Contents (0:00) Difficult to choose most currencies (2:19) The...