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.

   Counting Ports of Call with a PivotTable
A PivotTable is a great way to aggregate and analyze data. Sometimes, though, it can be difficult to figure out how to best use a PivotTable to get the analysis you need. In this tip, I show you two ways you can analyze data that may be otherwise difficult to process.

   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.

   Empty PivotTable Cells Don't Show as Blank
You can configure Excel so that it displays special text within blank PivotTable cells. Here's how to make the changes necessary.

   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.

   How Many Times Does Each Name Appear in a List?
If you have a list of names in a column, and you want to know how many times those names appear in a larger list of data, then this tip is for you. It provides several ways you can determine the counts that you need.

   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.

   PivotTable Aggregating Incorrect Data
PivotTables can be a great tool for analyzing large amounts of data. If you have a PivotTable that is pulling information from the wrong information, it can mess up your analysis efforts. This tip presents a few ideas for fixing a problem of data coming from the wrong place.

   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.

   Seeing What Changed in a PivotTable
PivotTables are great for aggregating and analyzing tons of raw data. If you want to see what changes in the PivotTable when you refresh it, then it can be a bit challenging.

   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.

   Turning Off Automatic Sorting in PivotTables
Tired of your data being sorted when you create a PivotTable? Excel allows you to specify how the sorting should occur by following the steps outlined in this tip.

   Updating a PivotChart Automatically
If you expect your PivotCharts to update automatically when you update a PivotTables, you may want to alter, slightly, how you update those PivotTables. Here's how.

   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.