PivotTables

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's some ideas on how to speed things up.

Can't Update Excel 2007 PivotTables in Excel 2003
If you create a PivotTable in Excel 2007, you may have problems editing or updating that PivotTable in Excel 2003. The only way around the problem is to not use such PivotTables in the earlier version of the program.

Changing the Default PivotTable Functions
When you create a PivotTable, Excel automatically sums the data that you place into the Data Items area of the table. This tip discusses ways you can change the default SUM function to a different function.

Conditional Formatting in PivotTables
Conditional formatting is very powerful, and you can use it to dynamically adjust how your data looks. Excel allows you to apply conditional formatting in a PivotTable, but the outcome of that application may not be satisfactory when the table is refreshed.

Counting with PivotTables
Got a slew of data you need to analyze? You can count instances of various pieces of information quite easily using a PivotTable. The way you create the table depends on the version of Excel you are using, however.

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.

Excluding Zero Values from a PivotTable
If you have a large set of data that includes some zero values, you may want to exclude those zero values from a PivotTable based on that data. You can do this in a couple of different ways, as discussed in this tip.

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
If you spend a lot of time formatting your PivotTable, only to see your formatting disappear when you refresh the PivotTable, it can be very frustrating. You'll want to make the change to the table options described in this tip in order to preserve your formatting work in the future.

Missing PivotTable Data
Stephen's workbook, created by someone else, contains a PivotTable that he cannot edit. This tip explains possible causes (and cures) for the problem.

Reducing File Sizes for Workbooks with PivotTables
Adding PivotTables to a workbook can balloon the size of the workbook file dramatically. There are ways to reduce the file size, and you should take advantage of them when creating the tables. This tip explains the different techniques you can use.

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.

Removing Subtotals from Many PivotTable Fields
Need to get rid of pesky subtotals from your PivotTables? It's easy to do by applying the macro highlighted in this tip.

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.

Too Many Rows or Columns in a PivotTable
PivotTables are often used to aggregate lots of information, and they do it beautifully. What do you do if Excel starts balking all of a sudden when it comes time to refresh an existing PivotTable? The answer could lie in how much data you have and how that data is configured within the data table.

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.

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.