MrExcel Excel Videos

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

How to create your own custom data types in Excel using ScriptLab and the Javascript API. Important Links: Try out the workbook with my custom data types: TestTheseCustomDataTypes.xlsx Github for the example using a Custom Function: https://gist.github.com/smozgur/a34ad495fb75e19c3e21904875ef0170 Github for the example using an add-in: https://gist.github.com/smozgur/bf5276622820b1fbbec6690c1689762c Table of Contents (0:00) The quest for a perfect custom data type (1:50) Download a workbook with working custom data types (3:54) The back-end API (5:17) ScriptLab add-in (8:33) ScriptLab Function (10:49) Visual Studio version (12:00) Nerdvana check

The data types gallery with Geography, Stocks, Currency, and Wolfram is mildly interesting. But I think the real goal is to get your own company data in the Data Type. I want it to be a very rich experience, just like Wolfram Data Types: 1) Type plain text and convert to a data type 2) From the card for the data type, return an array of entities (such as all the people in Bob Marley and the Wailers music act. 3) Have those fields returned from the Data Card be Data Types as well, so you can drill down and get more information about the sub-entity 4) The data type should be able to return an image from an Image URL 5) The data type should be able to return a clickable hyperlink. 6) It would be nice if the little icon for the data type...