MrExcel Excel Videos

Running totals, both in regular data and in Ctrl+T tables. Recap: This episode shows three ways to do running totals The first method has a different formula in Row 2 than all the other rows The first method is =Left in row 2 and =Left+Up in rows 3 through N If you try to use the same formula, you get a #Value error with =Total+Number Method 2 uses =SUM(Up,Left) or =SUM(Previous Total,This Row Amount) SUM ignores Text so you don't get a VALUE error Method 3 uses an expanding range: =SUM(B$2:B2) Expanding ranges are cool, but they are slow Read the Charles Williams whitepaper on Excel Formula Speed The third method is a problem when you use Ctrl+T and add new rows Excel can't figure out how to write the formula The workarounds require...

When you use Ctrl+T tables, the references in formulas are easier to read. VLOOKUP is awesome and my favorite function VLOOKUP haters complain that it is fragile due to the 3rd argument If the shape of your lookup table changes, the answers can change One workaround is to replace third argument with MATCH But imagine doing a MATCH for 1000 rows of VLOOKUP Make your lookup table into a table before doing the VLOOKUP The structured table reference will handle if the table shape changes Plus it does not require doing a MATCH over and over Peter Albert submitted this tip

In this episode, more about Ctrl+T tables. See how they can improve VLOOKUP, Charts, and Data Validation. Episode Recap: In the last episode, Ctrl+T made the pivot table source expand This also helps VLOOKUP and Charts and Data Validation Although it is slightly different in each Create your VLOOKUP then make the table a Ctrl+T table Remarkably the VLOOKUP formula will rewrite itself Build a chart. Make the source data a Ctrl+T table. Add new months. For Data Validation Source: Make it a table and then name the range without the heading Use the named range as the Validation Source Also mentioned in the episode: FORMULATEXT function for showing a formula

Update: Microsoft fixed this problem in version 1804. See: Why does the revenue field in your pivot table always Count instead of Sum? It is one of two reasons. Both reasons, along with workarounds, are found in this episode. Recap: In a perfect world, numeric fields will Sum in a pivot table Why do they sometimes Count? Reason 1: Empty or text cells How to Fix reason 1: Go To Special Blanks. 0. Ctrl+Enter Reason 2: You are selecting the whole column You are selecting the whole column so you can add more data later Ctrl+T to the rescue Notice the end of table marker

After limiting a pivot table report to the top five, the Grand Total does not show the total of everyone. This episode shows how to use a Data Filter in a pivot table. Recap: The pivot table Top 10 Filter gives a total of the visible rows Include Filtered Items in Totals is Greyed Out Odd way to invoke the Data Filter from the magic cell Data Filters are not allowed in pivot tables Excel fails to grey out the Data Filter from the magic cell Ask for the top 6 to get top 5 plus Grand Total Useful for filtering by a specific pivot item Excel 2013 or newer: Different Way to get the True Total Send your data through the Data Model Include Filtered Items in Totals will be available Get Total with asterisk I learned this trick 10+ years ago...

You have 2 years of detail data in Excel. Use a pivot table to quickly compare last year to this year. Recap: Start with multiple years of data Insert, Pivot table Drag date field to row area Excel 2016: Press Ctrl+Z to ungroup dates Drag revenue to values area Select any date in row area Use Group Field Choose Months & Years How to add subtotals to years field after grouping a pivot table Use Tabular form in a pivot table to give each row field its own column Repeat All Row Labels to fill in the blanks in a pivot table Drag years to the column area Right-click Grand Total column heading and remove How to avoid GetPivotData function when formula points to a pivot table To build a variance, type the formula without mouse or arrows Thanks...

Use the #2 Group and Outline button to show only the subtotal rows in Excel. It is tough to copy those to a new workbook. Recap: Add Subtotals Collapse to #2 View Copy the subtotals New workbook & paste. All the detail rows appear Finding Go To Special via the Go To Dialog Visible Cells Only Copy - you can see the difference with the marching ants New workbook & paste. Only the subtotals, pasted as values Thanks to Patricia McCarthy for suggesting this tip

Excel bolds one column in the subtotal rows. This short episode shows you how to bold all of the columns. Recap: Excel bolds one column on the Subtotal Rows How do you bold all columns? The intuitive way does not work Collapse to #2 view Select from first to last row Select Visible Cells (Alt+;) or QAT Icon for Select Visible Cells Format the subtotals Go back to #3 view

What if you need some extra text on the subtotal rows in Excel? Episode 1995 shows you how. Episode recap: If you need additional text on a subtotal row Collapse to #2 view Select the range where the text should be Home, Find & Select, Go To Special, Visible Cells Build a formula pointing one row above and use Ctrl+Enter Bonus trick for using SUM on most totals and Count on one Excel's method puts Total on one row and Count on another Better: Put Sum in all columns, then Ctrl+H to Replace Replace (9, with (3, Custom number format to show Count: 47