Excel includes a handy feature called PivotTables that can be used in a number of ways. If you have large amounts of data, PivotTables can make quick work of condensing and presenting your data in comprehensible ways. The following articles discuss how to use PivotTables to analyze data in Excel.

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

   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.

   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.

   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.

   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.

   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.