Excel PivotTable questions

Several readers have submitted questions about Excel 2010 and 2007 PivotTables, as follows:

Q1: Why does Excel insert the GETPIVOTDATA function into a formula when you use the mouse pointer to refer to a cell in a PivotTable, and is there a way to prevent this action?

A1: As discussed in the October 2011 Tech Q&A item “Make a Difference With PivotTables” (page 76), Excel by default inserts the GETPIVOTTABLE function when you point to PivotTable cells while writing a formula. In that article, I explained the benefits of the GETPIVOTDATA function, but I also should have mentioned that you can disable the GETPIVOTTABLE functionality in Excel 2010 and 2007 as follows:

a. Click a PivotTable to display the PivotTable Tools tab.

b. From the PivotTable Tools tab, select Options, PivotTable.

c. Click the Options dropdown arrow and uncheck the Generate Get PivotData item, as pictured below.

Q2: Why do my Excel PivotTables sometimes lose their formatting upon refresh, even after I check the PivotTable Options box labeled Preserve cell formatting on update?

A2: Often, when a CPA thinks his or her PivotTable is reformatting upon refresh, what actually is happening is the column widths are adjusting on each refresh. To prevent this from happening in Excel 2010 and 2007, disable Excel’s Autofit functionality as follows. Right-click on your PivotTable, then select PivotTable Options from the pop-up menu. From the Layout & Format tab, uncheck the box labeled Autofit column widths on update, then click OK. In addition, make sure the box labeled Preserve cell formatting on update is checked. Thereafter, your PivotTable’s number formats, color formats, and column widths will remain the same whenever you change the PivotTable settings or refresh its data.

Q3: I can sort my PivotTable in ascending or descending using the numeric data columns just fine; however, my PivotTables do not sort alphabetically. Why is this, and how do I fix this problem?

A3: By default, Excel 2010 and 2007 PivotTables sort row label data according to the order of the source data, and you have two options for solving this issue. First, you can sort your source data in the order you would like it to appear on the PivotTable. Second, you can set your PivotTable to ignore the source data order by right-clicking on your PivotTable, selecting PivotTable Options from the pop-up menu, then from the Display tab, under the Field List section, selecting Sort A to Z.

Also, be aware that Excel’s sort functionality is somewhat different for PivotTables than for regular worksheets. When you click on a text-based column in a PivotTable and select Sort from the Data tab, the resulting dialog box (pictured at the top of the next column) provides additional sort options for sorting data manually (by dragging and rearranging data), or for sorting the data in ascending or descending order according to a PivotTable’s specific field names. Consequently, you should also check these settings to ensure the desired results are achieved.

Further, be aware that as a default, Excel’s PivotTable sort function sorts by custom lists, which explains why a PivotTable will sort months (January, February, March, etc.) in month order instead of alphabetical order. To disable custom list sorting in a PivotTable, right-click your PivotTable, select PivotTable Options from the pop-up menu, then from the Totals and Filters tab, under the Sorting section, uncheck the box labeled Use Custom Lists when sorting.

Q4: What’s the best way to update a PivotTable’s source data range when the data source grows larger?

A4: Before creating a PivotTable in Excel 2010 or 2007, first convert the source data to a Table by selecting the source data (or by selecting a single cell in your source data, assuming that your source data contains no blank rows or columns and has a blank row above the column headings). Then from the Insert tab, select Table, OK. Thereafter, refreshing PivotTables created from this source data will automatically include any new column or row data you add to your source data table. (The Table feature is not available in Excel 2003.)