Home > Pivot > FAQs Pivot Table FAQs, Pivot Chart FAQs How to fix common problems with Excel Pivot Table and Pivot Charts. Answers to frequently asked questions; source data, refresh, chart formatting and more.

Pivot Tables

--Display text in the data area of a pivot table

--Repeat row field headings

--Error when grouping dates

--Preserve formatting when refreshing

--Make data fields appear side-by-side

--Turn off GetPivotData formula

--Change default from COUNT to SUM

--Show Pivot Table headings at top of each printed page

How do I display text in the data area of a pivot table?

There's no built-in option to display text data in the Values 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.

Or, if there are only a few text values, use this number formatting workaround, to display number values as text.

For example, instead of showing a 1 as the Region ID, show "East" as the Region name.

This video shows the steps, and click here for written steps.

To follow along with the video, download the Pivot Table Values Text workbook with the NO macro. The zipped file is in xlsx format, and does not contain any macros. You can manually apply the conditional formatting

How do I get the row field headings to repeat in a pivot table?

In Microsoft Excel 2010, and later versions, you can change a field setting to repeat pivot items in all fields, or selected pivot fields.

This video shows the steps, and click here for written steps.

I am trying to group dates by month and I get an error that says 'Cannot Group that selection'.

These are the most common reasons that the pivot table group error message appears:

• Blanks or Text: If the field contains blank cells, or contains cells with text, you'll get that error.
• Data Model: You won't be able to group pivot items if you checked the option to "Add to Data Model" , when you built the pivot table.

To resolve this problem, there are some suggestions on the page for fixing the PivotTable grouping problems.

When I refresh the pivot table, how can I preserve my formatting, such as column widths?

1. Right-click a cell in the PivotTable, and choose PivotTable Options
2. Ensure that Preserve Formatting is turned on, and AutoFormat is turned off, then click OK

To format cells, enable selection should be turned on. To enable selection:

1. On the Excel Ribbon, click the Analyze tab
2. In the Actions group, click Select
3. If it's not already activated, click on Enable Selection, as shown in the screenshot below

To format a section of a PivotTable, such as subtotals:

1. Move the pointer to the left of a subtotal heading in the PivotTable.
2. When the black arrow appears (like the one that appears when the pointer is over a row button), click to select the subtotal rows in the PivotTable.
3. Format the selected subtotal

How can I make two Value fields appear side-by-side, instead of stacked in separate rows?

If you add the two or more fields to the pivot table's Values area, a "Values" button is automatically created in the pivot table field list Layout section. I've circled that button in the screen shot below.

• If the Values button is in the Rows area, the Value fields are stacked vertically
• To change them to horizontal layout, drag the Values field button into the Column Labels layout box.

There's a video on the Pivot Table Layout page that shows the steps.

When I link to a pivot table cell, a GetPivotData formula is created. How can I turn this off?

To turn off the Generate GetPivotData command, using the Excel Ribbon, follow these steps:

NOTE: This will affect ALL Excel workbooks, not just the active workbook

1. Select any cell in a pivot table.
2. On the Ribbon, click the PivotTable Analyze tab
• Or, under PivotTable Tools, click the Options tab
3. At the left click the arrow on the PivotTable command
4. Next, click the drop down arrow for Options
5. Click the Generate GetPivotData command, to turn the feature off or on.

NOTE; There are detailed instructions, and a short video that shows the steps, on the Generate GetPivotData page.

Can I change the default summary function for values from COUNT to SUM?

You can't change the default settings for the Value fields.

• If a field contains blank cells, or cells with text, it will default to COUNT.
• Otherwise, it will SUM

You can change the field's summary function after you add it to the pivot table.

When printing, how can I get the pivot table headings at the top of each page?

You can change the Print Titles option in the PivotTable Options dialog box.

There are instructions at this link, to get the pivot table headings at the top of each page

  🔼

Pivot Table Source Data

--New data doesn't appear

--Clear old values from dropdowns

--Fnd what the Access data source is

--Locate source data for pivot table

--Use multiple sources for pivot table

I added new rows/columns to the pivot table source data, and they don't appear when I refresh the pivot table.

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:

1. Right-click a cell in the PivotTable
2. Choose PivotTable Wizard
3. Click the Back button, and select the new range
4. Click Finish.

Or use a dynamic range or Excel Table as the pivot table source, and it will adjust automatically.

How do I clear old values from the pivot table drop-down lists?

If you remove data from the PivotTable source data, that information might still appear in the PivotTable drop down lists, after you refresh it.

This short video shows how to change a setting in a pivot table, so old items do not appear in the drop down lists. The written instructions are here for clearing old items from a pivot table.

To follow along with the video, download the Pivot Table Old Items workbook. The zipped Excel file is in xlsm format, and contains macros from the instruction page. To test the macros, be sure to enable macros, if prompted, when you unzip and open the workbook.

I've inherited workbooks with pivot tables based on an Access query. How can I find what the data source is?

Ron Coderre has a free PivotTable add-in that lets you view and edit the connection string

How can I locate the source data for my Excel pivot table?

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

Is it possible to use multiple sources of data to produce a pivot table?

You can use Power Query (Get & Transform Data) to combine the data from 2 or more tables, if those tables have some column headings with identical names. Then, create a pivot table from the combined data

NOTE: In older versions of Excel, use the Multiple Consolidation feature

  🔼

Pivot Charts

--Preserve chart formatting

--Hide/Show Chart Labels

--Create a Normal chart from pivot table

--Add a horizontal line to Pivot Chart

--Change Pivot Chart without changing Pivot Table

--Change date format on Chart axis

--Include grand total in pivot chart

When I refresh the pivot chart, my formatting is lost. How do I preserve it?

Loss of formatting is a known problem with pivot charts.

You can try the workarounds on Jon Peltier's site, to prevent the format changes, by creating a normal chart that references the pivot table data.

How do I hide or show the labels on the Pivot Chart?

With the pivot chart selected:

• On the Excel Ribbon, click the Analyze tab.
  • Click the Field Buttons command, to hide/show the PivotChart Field buttons.
  • OR, click the Field Buttons arrow, and select one of the display options.

How can I create a Normal chart from pivot table data?

For Excel 2007 and later versions, paste a copy of the pivot table labels and data, as values, onto another worksheet. Then, create a normal chart from the copied data.

For Excel 2003 and earlier versions, see instructions for creating a normal chart from pivot data on Jon Peltier's site

How can I add a horizontal line to a Pivot Chart?

You could create a normal chart, based on the PivotTable, and include the line in that.

How can I change my Pivot Chart without changing the PivotTable?

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.

How can I change the date format on the Pivot Chart axis?

The pivot chart date formats are controlled by the date field format in the PivotTable. To change the date format:

1. Right-click a date in the pivot table row labels or column labels (not the pivot chart).
2. Click on Field Settings, to open the Field Settings dialog box
3. Change the Number Format to the date format that you want
   • Select one of the standard date formats, or create a custom format that you need.

How can I include the grand total in the pivot chart?

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

  Back to Top

Slideshow -- 5 Annoying Pivot Table Problems

Watch this slide show to see 5 pivot table annoyances, and how to fix them.

More Pivot Table Help

Calculated Field vs Calculated Item

Pivot Table Grouping Affects Another Pivot Table

Pivot Table Errors

Pivot Table Date Filters

Show Values As

Difference From

Pivot Cache