Get your PivotTable items in order with smart sorting
by Corrine Streff
Application:
Microsoft Excel 2000/2002/2003/2004/2007
Operating Systems:
Microsoft Windows, Macintosh
While in many ways, PivotTables offer the most flexibility for data analysis, displaying the data just the way you want it is often a daunting task. Sorting is a whole different beast when it comes to PivotTables, and we’ll help you learn the ins and outs so you can organize your PivotTable quickly and correctly.
To sort the data in our PivotTable, we’ll:
• Review how the sorting buttons on the Standard toolbar affect a PivotTable.
• Utilize the Sort menu command, which lets you sort items according to a defined list order.
• Configure a PivotTable so it keeps items sorted even when you refresh the table.
You’ll find that once you create a PivotTable, you’ll still have a little work to do to get the data displayed “just right.” For example, you may want to change your information’s sort order. Most of the time, you can probably live with the default sorting options that Excel applies, but there may be times when you’ll want to override the defaults to meet your specific sorting needs. We’ll look at how to apply the sort you want, and how to ensure Excel retains the sorting when you refresh your PivotTable.
Sort to your specifications
By default, PivotTable row and column headings are initially sorted in ascending order. PivotTables are usually sorted by labels instead of values, which is fine for most cases. However, you may occasionally prefer to see your data sorted in descending order or not sorted at all — sometimes presenting your data in a customized order makes more sense. To further complicate matters, a PivotTable often appears to lose its sort settings when you refresh it after changing the underlying data.
Create the sample PivotTable
To demonstrate PivotTable sorting options, let’s create an example. As shown in Figure A, our sample data includes inventory counts for each week in the first two quarters across three different branches.
A:
This is the source data upon which our PivotTable will be based.
To produce a PivotChart:
1. Launch Excel and open our sample workbook.
2. Select a cell in the data list and then choose Data | PivotTable And PivotChart Report from the menu bar.
3. Click Next twice and click the Layout button.
To design the PivotTable, drag the Product and Week fields to the Page area, the Month field to the Column area, the Branch field to the Row area, and the Inventory Count field to the Data area. Click OK and then click the Finish button to create a PivotTable resembling the one shown in Figure B.
B:
Here’s the resulting PivotTable created from the data.
Apply number formatting
Next, we’ll apply formatting to the inventory amounts displayed in the data area.
To apply the number format you want:
1. Select the Sum of Inventory Count label found in cell A4.
2. Click the Field Settings button fieldsettingfound on the PivotTable toolbar.
Expert tip: You can also access a field’s PivotTable Field dialog box by double-clicking on the field label.
3. In the PivotTable Field dialog box, click the Number button and select Number from the Category list box.
4. Change the Decimal Places setting to zero and select the Use 1000 Separator (,) check box.
5. Click OK twice to return
