Topic: PivotTables

The following articles are available. 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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Removing Subtotals from Many PivotTable Fields Put together a large PivotTable, and you can have many different fields to format or otherwise configure. If you want to get rid of all the subtotals on your PivotTable fields, the work can become quite tedious. That is where the macro highlighted in this tip comes in handy—it can remove all your subtotals in one easy step. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Weighted Averages in a PivotTable Weighted averages are very handy when analyzing certain types of data. If you want to add a weighted average to a PivotTable, there are no intrinsic Excel functions that allow you to do it. Instead, you need to develop your own formulas and add them to the PivotTable, as described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Tips, Tricks, and Answers

An Amazing Resource!

Helpful Links

Great Info!

RSS Feeds