Technology

Spreadsheets: Tame Pivot Table Sequencing

How to solve the headache of sorting pivot table and filtered data as new data is added to worksheets.
Bill JelenJanuary 19, 2011

Editor’s Note: To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com’s Spreadsheet Tips Page.

Reader Mike E. wins an autographed copy of Pivot Table Data Crunching from CFO and MrExcel.com for his question, “I have a worksheet where I track year-to-date sales invoice detail. I add new data to the spreadsheet each month. I then run various pivot tables against the data which include filters. The first time I created the pivot table, the drop down filter selections sorted in ascending order. As I add records to the worksheet, however, the data from the new records do not sort in ascending order in the drop down filter. Instead, they are added to the bottom of the filter list. Is there a way to have all data in the drop down sorted as I add information to the worksheet?”

Mike has found an annoying bit of pivot table behavior. Luckily, the solution has to be done only once and the problem will never appear again in this pivot table. First, let’s see the problem Mike describes. In Figure 1 (below), a pivot table from the first few records shows products A, C, and E.

CFO Insights on Inflation, Workforce Challenges, and Future Plans 

CFO Insights on Inflation, Workforce Challenges, and Future Plans 

Download our 2022 survey report for a high-level view of finance team projections and strategies, directly from our CFO.com executive readers.

After the next day’s records are added to the data set, refresh the pivot table. Excel finds a new product called B in the data set. Since the pivot cache was built with only products A, C, and E, the new product is added to the end of the list, as shown in Figure 2. The Report Filter dropdown offers no method for sorting the list.

Fig 1.

MrExcel 1-19-11 Fig01

 

Fig. 2

MrExcel 1-19-11 Fig02

Temporarily Rearrange the Pivot Table
To solve this problem, drag the product field from the Report Filter drop zone to the Row Labels drop zone in the Pivot Table Field List. As shown in Figure 3 (below), you can see that the “wrong” sequence for the products persists when you drag the field into the pivot table. However, with the field in the row area, you can sort.

 

 

 

 

Fig. 3

MrExcel 1-19-11 Fig03

 

Row Area Sort
Once the field is in the Row Labels, resequence that field. The process can vary depending on your version and the pivot table layout that you are using:

• Excel 2007/2010, Compact Layout, One Row Field: Open the Row Labels dropdown in cell A4 and choose Sort A to Z.

• Excel 2007/2010, Compact Layout, Two or More Row Fields: Open the Row Labels dropdown. In the Select Field dropdown, choose the correct field. Click Sort A to Z.

• Excel 2007/2010, Tabular or Outline Layout: Open the Product dropdown in A4. Choose Sort A to Z. (See Figure 4, below.)

• Excel 2003: Double-click the Product heading in A4 to display the PivotTable Field dialog. On the right side of the dialog, click Advanced; under AutoSort options, click Ascending. Click OK. Click OK.

Fig. 4

MrExcel 1-19-11 Fig04

 

Fig. 5

MrExcel 1-19-11 Fig05

As you can see in Figure 5 (left), the product sequence is now correct in the pivot table. But this was not what Mike was trying to solve. Let’s get back to the problem at hand. Drag the Product field from the Row Labels area back to the Report Filter area as shown in Figure 6 (below). When you open the Product dropdown in the Report Filter, the products are returned to the alphabetical sequence. This might seem like a lot of work to solve a tiny problem. The great news is that once you’ve set up an AutoSort rule for the Product field, the list will continue to automatically sort after you add new records and refresh the pivot table.

 

Fig. 6

MrExcel 1-19-11 Fig06

 

In Figure 7 (below), new records were added to the original data set that introduced a product D. This item was automatically added to the list in the proper sequence:

Fig. 7

MrExcel 1-19-11 Fig07

 

Bill Jelen is a CFO contributing editor and author of 32 books about Excel, including three editions of Pivot Table Data Crunching for Excel 2003, 2007, or 2010. You can win a copy of one of his books if your question is selected for a column. Post your question to the community content block on the right.

4 Powerful Communication Strategies for Your Next Board Meeting