Microsoft Office Tutorials and References
In Depth Information
Summary: You can trick Excel into allowing ﬁ lters in a pivot table by starting
the selection outside the pivot table.
Source: Podcast episode 793 http://www.mrexcel.com/podcast/2008/07/
Challenge: You want to chart the sales for the ﬁ ve largest customers in a data
Solution: You can sort the collapsed view of a subtotaled data set. Here’s
Choose one cell in the customer column. Click the AZ button to sort in
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 ﬁ 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
As shown in Figure 69, Excel creates a chart of the ﬁ ve largest customers.
Figure 69. You can quickly create a
chart of your i ve largest customers.