MrExcel Excel Videos

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

After adding subtotals by customer in Excel, can you sort the largest customer to the top? Yes, you can. Recap: Thanks to Derek Fraley from Springfield MO for this tip Add Subtotals Use Group and Outline button to show only subtotals Sort descending by revenue Excel sorts chunks of records Sort again by customer Grand total goes back to the bottom

If your Excel columns are in the wrong sequence, you can sort left to right. Excel can sort left to right Insert a row with the correct column sequence Use the Sort dialog Click Options, then Sort Left to Right Also note this dialog offers case-sensitive sorting Column widths do not correct, but Alt+OCA will fix Bonus tip with Shift+Spacebar and Ctrl+Spacebar How to remember which is which? Ctrl starts with C, just like Column Shift key is wider than Ctrl key, just as a row is wider than a column Once you select the entire row or column, Shift+Drag it into location

Using Cut and Insert Cut Cells is a slow way to rearrange rows. Podcast 1978 introduced Custom List for the Fill Handle. Sorting data into a special sequence is another benefit of custom lists. Type the list into the correct sequence. File, Options, Advanced, 83%, Edit Custom Lists, Import Use the Sort dialog In Sort Order, open the dropdown and choose Custom List Interesting (?) that you can sort the list reverse after choosing Thanks to @NeedForExcel for suggesting this tip