MrExcel Excel Videos

Use conditional formatting to mark formula cells New Rule, Use a Formula, =ISFORMULA(A1) Thanks to Craig Brody: mark formula cells that are links to other sheets New Rule, Use a Formula, =ISNUMBER(FIND("!"),FormulaText(A1))) Extending this: To mark cells pointing to external workbooks, use this formula: =ISNUMBER(FIND("]",FORMULATEXT(A1))) You can use AutoSum in Several Cells at Once When you are editing a formula in conditional formatting box and press the arrow to move, you will get cell references. To prevent this, use F2 to change Enter mode to Edit mode

Quickbooks loves to insert tiny column between the columns To speed up the process, delete first column, then use F4 for Fast Re-Do Better: Select a row. Go to Special, Blanks, Ctrl+-, Entire Column, OK Thanks to Carmella in Green Bay

The goal is to build a Year-over-Year report in a pivot table and show the % Growth I almost always do the percentage growth calculation as a formula outside of the pivot table But that has issues, such as GetPivotData and the formula needs to handle if the pivot table shrinks or grows Using a technique that I learned from Tobias Ljung, you can build the calculation in to the pivot table. Add revenue a second time to the pivot table. The calculation is % Change from, Years, Previous Item Hide the % change for the first year (they are all blank)

A viewer downloads data from a system where each item is separated by Alt+Enter Need to VLOOKUP each item in the cell Use Power Query to split the items out, do VLOOKUP, then join them back together This is like doing a ConcatenateX in DAX (which does not exist) or a TEXTJOIN(CHAR(10) in Office 365 Excel Built a solution using Power Query, including the Structured Column tool of Extract As That feature only works on a list, not a table, so I used the Table.Column function to convert the table to a list.

A viewer downloads data from a system where each item is separated by Alt+Enter Bill: Why are you doing this? Viewer: It is how I inherit the data. I want to keep it that way. Bill: What do you want to do with the 40% of values not in the table? Viewer: No answer Bill: There is a complicated way to solve this if you have the latest Power Query tools. Instead, a VBA Macro to solve it - the macro should work all the way back to Excel 2007 Instead of doing VLOOKUP, do a series of Find & Replace with VBA

When you make your data into a table using Ctrl+T, any charts, pivot tables, VLOOKUP using that range will automatically expand.

Ctrl+N will inherit settings from your book.xltx file But File, New will not.

The popular Ctrl key shortcuts in Excel

How to learn the new keyboard shortcut for any command, such as Freeze Panes