Search Contextures Sites

 

Contextures
Excel news
by email

 

 

 

 

 

 

Learn how to create Excel dashboards.

 

 

 

 

Your worksheet formulas can create traffic-light charts, highlight chart elements, assign number formats, and more.

 

 

 

 

Learn how to create Excel dashboards.

 

 

Pivot Table FAQs and Pivot Chart FAQs

 

Menu: Frequently Asked Questions about Microsoft Excel

  1. Pivot Tables
    1. How do I display text in the data area of a pivot table?
    2. How do I get row field headings to repeat in a pivot table?
    3. I am trying to group dates by month and get an error that says 'Cannot Group that selection'
    4. When I refresh the pivot table, how can I preserve my formatting?
    5. How can I make two data fields appear side-by-side, instead of in separate rows?
    6. When I link to a pivot table cell, a GetPivotData formula is created. How can I turn this off?
    7. Can I change the default summary function for data from COUNT to SUM?
    8. When printing, how can I get the PivotTable headings at the top of each page?
  2. Pivot Table Source Data
    1. I added new rows/columns to the pivot table source data, and they don't appear when I refresh the pivot table
    2. How do I clear old values from the pivot table dropdowns?
    3. I've inherited workbooks with pivot tables based on an Access query. How can I find what the datasource is?
    4. How can I locate the source data for my Excel 2007 pivot table?
    5. Is it possible to use multiple sources of data to produce a pivot table?
  3. Pivot Charts
    1. When I refresh the pivot chart, my formatting is lost. How do I preserve it?
    2. How do I remove the 'Drop Page Fields Here' labels on the Pivot Chart?
    3. How can I create a Normal chart from pivot table data?
    4. How can I add a horizontal line to a Pivot Chart?
    5. How can I change my Pivot Chart without changing the Pivot Table?
    6. How can I change the date format on the Pivot Chart axis?
    7. How can I include the grand total in the pivot chart?
  4. More Pivot Table Tutorials

Contextures Pivot Table Blog

Create Excel dashboards quickly with plug-n-play reports.

1. Pivot Tables

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

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.

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

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.

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

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.

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 Table 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. From the Pivot toolbar, choose PivotTable>Select
  2. If it's not already activated, click on Enable Selection

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 data fields appear side-by-side, instead of stacked in separate rows?

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

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

There are instructions here for adding the Generate GetPivotData button to a toolbar, and toggling the feature on and off

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

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.

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, to get the pivot table headings at the top of each page?

2. Pivot Table Source Data

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 dropdown 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. There are instructions here for clearing old items from a pivot table.

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 2007 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 create a PivotTable from data on separate sheets (multiple consolidation ranges), but the results may not be exactly what you want.

3. Pivot Charts

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. 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.

How do I remove the 'Drop Page Fields Here' labels on the Pivot Chart?

With the pivot chart selected, on the pivot toolbar, choose PivotChart>Hide PivotChart Field buttons.

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

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

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.

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 field format in the PivotTable. To change the date format:

  1. Right-click a date in the pivot table (not the pivot chart).
  2. Click on Field Settings
  3. Change the Number Format to the date format that you want.

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

4. More Pivot Table Tutorials

Contextures Pivot Table Blog

Contextures Pivot Table Tutorials

 

 

Get All the Excel News

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.

 

 

Pivot Table FAQs compiled by Debra Dalgleish, Excel MVP 2001 - present

Learn how to create Excel dashboards.

Top of Page | Main Index

 

Privacy Policy

 

Contextures Inc., Copyright 2014
All rights reserved.

 

Last updated: July 19, 2014 10:44 AM