How to fix common problems with Excel Pivot Table and Pivot Charts. Answers to frequently asked questions; source data, refresh, chart formatting and more.
Watch this slide show to see 5 pivot table annoyances, and how to fix them.
You can't display text data in the data area of a PivotTable. You could display the text fields in the Row and Column areas, and show a count of the records in the data area.
In Excel 2010, and later versions, you can change a field setting to repeat pivot items in all fields, or selected pivot fields. Click here for a video and written instructions.
In Excel 2007, and earlier versions, the row headings show once in a PivotTable, and there's no setting you can change, to force them to repeat. To create a table with a heading on each row, you could copy the pivot table, paste it as values in another location, and fill in the blanks.
If the field contains blank cells, or contains cells with text, you'll get that error. To resolve this, here are some suggestions for fixing the PivotTable grouping problems.
To format cells, enable selection should be turned on. To enable selection:
To format a section of a PivotTable, such as subtotals:
After you add the two fields to the pivot table's data area, drag the Data field button to the right, onto the cell that contains the word 'Total'. There's a picture here that shows the 'Total' cell: Change pivot table data to horizontal layout
There are instructions here for adding the Generate GetPivotData button to a toolbar, and toggling the feature on and off
You can't change the default settings for the data fields. If a field contains blank cells, or cells with text, it will default to COUNT. Otherwise, it will SUM. There's a feature that changes all the data fields to SUM, in my PivotPower add-in, that you can download and install.
You can change the Print Titles option in the PivotTable Options dialog box, to get the pivot table headings at the top of each page?
If the new columns are outside the pivot table's data range, they won't show up in the field list. Likewise, new rows that are added to the bottom of the existing data might not automatically appear when you refresh the PivotTable. To adjust the source data's range:
Or use a dynamic range or Excel Table as the pivot table source, and it will adjust automatically.
If you remove data from the PivotTable source data, that information might still appear in the PivotTable drop down lists, after you refresh it. There are instructions here for clearing old items from a pivot table.
Ron Coderre has a free PivotTable add-in that lets you view and edit the connection string
You can use the Change Data Source command on the Options tab of the Excel Ribbon, to locate the source data for your pivot table
You can create a PivotTable from data on separate sheets (multiple consolidation ranges), but the results may not be exactly what you want.
Loss of formatting is a known problem with pivot charts. There's
information in the following MSKB article, which suggests recording
a macro as you apply the formatting: Changing
a PivotChart removes series formatting in Excel
You could record a macro as you format the chart, and change the recorded series number to names. Then run that macro after you change the pivot table.
With the pivot chart selected:
For Excel 2007 and Excel 2010, paste a copy of the pivot table labels and data, as values, onto another worksheet. Then, create a normal chart from the copied data.
You could create a normal chart, based on the PivotTable, and include the line in that.
Create another pivot table, based on the first one. Then, create the pivot chart from the second PivotTable. You can hide the sheet that contains the second pivot table. When you change the pivot chart, only the hidden pivot table will be affected.
The pivot chart date formats are controlled by the field format in the PivotTable. To change the date format:
The pivot chart can't show grand totals or subtotals. You could create a normal chart from the data, and include the grand totals when copying the pivot table data
For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.
Last updated: December 5, 2016 10:35 AM