Tips, Tricks, and Answers
The following articles are available for the 'PivotTables' topic. Click the article''s title (shown in bold) to see the associated article.
Bogging Down with Calculated Items
Create a complex PivotTable and you may find that your system slows to a crawl. The reason for this may be due to the way in which you directed the PivotTable to analyze your data. Here are some ideas on how to speed things up.
Counting with PivotTables
One of the ways you can use PivotTables is to generate counts of various items in a data table. This is a great technique if your data table has quite a few items in it.
Default Formatting for PivotTables
Wish there was a way to define how you want PivotTables formatted before you actually create the PivotTable? You may be out of luck, as discussed briefly in this tip.
Displaying a PivotTable's Name in the PivotTable
When you create a PivotTable, it can have a name. You may want this name to appear within the PivotTable itself. There is no automatic way to do this, but this tip provides a couple of ways you can work around this limitation.
Easy Filtering Specifications for a PivotTable
When you want to include specific records from a source table into a PivotTable, you need to employ some sort of filtering. Here's a common scenario and a way to include just the rows you want.
Editing PivotTables without Underlying Data
If you ever try to edit a PivotTable and get an error that tells you that the "underlying data was not included," it can be frustrating. This tip looks at possible causes for this error and how you can solve the problem.
Error in Linked PivotTable Value
Excel allows you to link to values in other workbooks, even if those values are in PivotTables. However, Excel may display an error value instead of the proper value if you donít have the linked workbook open. Hereís how to prevent the error value.
Excluding Zero Values from a PivotTable
If you are using a data set that includes a number of zero values, you may not want those values to appear in a PivotTable based on that data. Here are some ideas about how you can eliminate the values.
Expanding PivotTable Rows to Sheets
PivotTables are a great way to work with large quantities of data in an intelligent manner. Sometimes, however, you just need to look at the underlying data. Here are a few ways you can see it.
Formatting a PivotTable
You can format PivotTables using either manual formatting or automatic formatting. You need to be careful, however, as only one type of formatting will not be wiped out when Excel updates the table.
Maintaining Formatting when Refreshing PivotTables
When you refresh the data in a PivotTable, Excel can play havoc with whatever formatting you applied. Here's how to protect your formatting efforts when updating the information.
Missing PivotTable Data
Wonder what happened to the data behind a PivotTable? It could be in a number of places, and tracking it down could be a challenge. Here are a few ideas.
Pointing PivotTables to Different Data
Changing the data source PivotTables go to can be a bit tricky. This tip explains what can happen when you re-point your files and how you can make the switch without any problems.
Reducing File Sizes for Workbooks with PivotTables
Need to reduce the size of your workbooks that contain PivotTables? Here's something you can try to minimize the behind-the-scenes data that Excel uses for those PivotTables.
Refreshing PivotTable Data
If you modify the data on which a PivotTable is based, you'll need to refresh the table so it reflects the modified data. This tip explains the various ways you can do the refresh.
Rows in a PivotTable
PivotTables are used to analyze huge amounts of data. The number of rows used in a PivotTable depends on the type of analysis you are doing and the characteristics of the original data. If you need to determine how many rows are in a PivotTable, you can use the approaches described in this tip.
Setting Stable Column Widths in a PivotTable
When you update a PivotTable, Excel can take liberties with any formatting you previously applied to the PivotTable. Hereís how you can protect the column widths you want for the PivotTable.
Suppressing Zero Values in PivotTables
PivotTables are great for digesting and analyzing huge amounts of data. But what if you want part of that data excluded, such as information that has a zero value? Here's a couple of quick ideas.
Text Truncated in PivotTable
When you create a PivotTable based on data that contains lots of text, you may be surprised to find that your text is truncated. This is apparently by design, as described in this tip.
Updating Multiple PivotTables at Once
PivotTables are a great way to process huge amounts of data and make sense of that data. If you have a number of PivotTables in a workbook, you may want a way to update them all in one pass. This can be done through the use of a short macro, described in this tip.
Using Classic PivotTable Layout as the Default
Are you attached to the classic PivotTable layout? Looking for a way to make that layout the default for new PivotTables? You may be out of luck on setting the default, but you can make switching to the classic layout easier with the macros in this tip.
Weighted Averages in a PivotTable
PivotTables are used to boil down huge data sets into something you can more easily understand. They are very good simple aggregations, such as sums, counts, and averages. They canít do weighted averages, however. That doesnít mean you are out of luck; you can still calculate weighted averages using the ideas in this tip.