MrExcel Excel Videos

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

For a special holiday-week treat, change the color of the gridlines in Excel to red. It could drive your co-workers insane. Actually, as time goes by, the gridlines are becoming more faint. Changing the gridline color from automatic to black might make the Excel gridlines easier to see.

Today, a quick tip for sorting left to right in Excel. With the correct headings, it is a faster way to rearrange columns. Check out all 12 expert tricks at:

A question from the Las Vegas IMA: How do you do date rollups in Excel when the company is on a fiscal year that does not end on December 31? This video shows how to use Power Query to link to a Calendar table. The pivot table uses the Data Model and you create a relationship.

A challenging puzzle from FMWC - Financial Modeling World Cup. How to solve a Word Maze or a Word Search using Excel. This maze includes 20000 letters! You have 30 minutes to score 1000 points using Excel formulas or VBA. If you have any solution for finding words diagonally, leave a comment. If this looks like something you would be good at solving, consider joining the competition. Excel as esports: sign-up for an FMWC Open - a competition where every Excel user gets a chance to show off their skills and compete for $10,000 prize fund. Sign up here: FMWC Open – FMWC Save $25 on your registration by answering three questions: Microsoft Forms Table of Contents (0:00) Welcome (1:19) Strategy (3:38) Skip the easy ones first (4:07)...