MrExcel Excel Videos

A great trick from Szilivia Juhasz. If you want to create a pivot table for each sales rep or each product line or each whatever, you can use the Show Pages feature. Episode Recap: There is an easy way to replicate a pivot table for each rep, product, whatever Create the pivot table To replace the Row Labels heading with a real heading, use Show in Tabular Form To replace Sum of Revenue, type a space and then Revenue Provided you select all value cells and the grand total, you can change the number format without going to Field Settings Greenbar effect using Design, Banded Rows, and then choosing from the Format gallery Add the "for each" field to the Filter area Go to the first Pivot table tab in the ribbon (Options in 2007 or 2010...

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