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 pivot table. You could display the text fields in the Row and Column areas, and show a count of the records in the data area.
Is it possible to use multiple sources of data to produce a pivot table?
You can create a pivot table from data on separate sheets (multiple consolidation ranges), but the results may not be exactly what you want. There's more information here: http://www.contextures.com/xlPivot08.html
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 fields are outside the pivot table's data range, they won't show up in the field list. To adjust the range:
- Right-click a cell in the pivot table
- Choose PivotTable Wizard
- Click the Back button, and select the new range
- Click Finish.
Or use a dynamic range as the pivot table source, and it will adjust automatically. There are instructions here: http://www.contextures.com/xlPivot01.html
How do I get row field headings to repeat in a pivot table?
The row headings show once in a Pivot Table, 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, using the technique shown here: http://www.contextures.com/xlDataEntry02.html .
I am trying to group dates by month and get an error that says 'Cannot Group that selection'.
If the field contains blank cells, or cells with text, you'll get that error. There are some suggestions for fixing the problem here: http://www.contextures.com/xlPivot07.html#Problems
When I refresh the pivot table, how can I preserve my formatting?
- Right-click a cell in the pivot table, and choose Table Options
- 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:
- From the Pivot toolbar, choose PivotTable>Select
- If it's not already activated, click on Enable Selection
To format a section of a pivot table, e.g. subtotals:
- Move the pointer to the left of a subtotal heading in the pivot table.
- 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 pivot table.
- Format the selected subtotal
How can I make two data fields appear side-by-side, instead of 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: http://www.contextures.com/xlPivot02.html
How do I clear old values from the pivot table dropdown lists?
There are instructions here for clearing old items from a pivot table: http://www.contextures.com/xlPivot04.html
I've inherited workbooks with pivot tables based on an Access query. How can I find what the datasource is?
Ron Coderre has a pivot table add-in that lets you view and edit the connection string: http://www.contextures.com/xlPivotPlay01.html
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: http://www.contextures.com/xlPivot06.html
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 pivot table add-in, that you can download here: http://www.contextures.com/xlPivotAddIn.html
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
http://support.microsoft.com/?id=215904You 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 add a horizontal line to a Pivot Chart?
You could create a normal chart, based on the pivot table, and include the line in that. On Jon Peltier's site, there are instructions for creating a normal chart from pivot data: http://www.peltiertech.com/Excel/Pivots/pivotcharts.htm
How can I change my Pivot Chart without changing the Pivot Table?
Create another pivot table, based on the first one. Then, create the pivot chart from the second pivot table. 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.
Home Excel Tips Sample Spreadsheets
Last updated: April 26, 2008 3:42 PM
