Microsoft Office Tutorials and References
In Depth Information
SORT SUBTOTALS
Summary: You can trick Excel into allowing fi lters in a pivot table by starting
the selection outside the pivot table.
Source: Podcast episode 793 http://www.mrexcel.com/podcast/2008/07/
episode-793-pivot-fi lter-hack.html
SORT SUBTOTALS
Challenge: You want to chart the sales for the fi ve largest customers in a data
set.
Solution: You can sort the collapsed view of a subtotaled data set. Here’s
how:
Choose one cell in the customer column. Click the AZ button to sort in
ascending order.
Choose Data, Subtotals. In the Subtotal dialog box, change the At Each
Change In dropdown to Customer. Make sure the Use Function dropdown
is Sum. Choose at least the Sales column from the Add Subtotal To section.
Click OK. Excel adds subtotals for each customer.
Look at the left of column A. Excel has added three group and outline buttons,
labeled 1, 2, and 3. Click the 2 button to see one line per customer.
Choose one cell in the Sales column. Click the ZA button to sort the largest
customers to the top of the list.
Select cell A1 through the fi fth customer total. In Excel 2003, press F11 to
create a chart. In Excel 2007, press Alt+F1 to create a chart on the current
page.
As shown in Figure 69, Excel creates a chart of the fi ve largest customers.
Part
2
1.
2.
3.
4.
5.
Figure 69. You can quickly create a
chart of your i ve largest customers.
 
 
Search JabSto ::




Custom Search