The key to success with Excel pivot tables is having good source data. I’ve written many articles with pivot table data source tips, and this list will help you find the information that you need. Some of the articles are on my Contextures website, and others are on my Pivot Table blog.
Set Up the Source Data
No matter which version of Excel you’re using, the source data needs to be in a well-organized list, that will grow or shrink as you add or remove data.
- In Excel 2007 and later, that list can be set up as a named table, which will expand automatically, as new data is added.
- For earlier versions, or if you don’t want to use a named table, create a dynamic named range.
Be sure to normalize the source data, so the pivot table is as flexible as possible.
If the source data is on multiple sheets, there are a few techniques that you can use to combine it into a single pivot table.
Find the Source Data
If you’ve inherited a pivot table, and aren’t sure where the source data is located, use the tips on the following pages to find it.
Or, if you received a workbook with a pivot table, but no source data, you might be able to re-create the source data.
Switch to Different Source Data
After you create a pivot table, you can change it, so it uses a different data source.
It’s a little trickier, but you can change a pivot chart’s data source too, so it is based on a different pivot table.
Source Data Problems
If you add, remove or edit the source data, the pivot table should show the revised data, the next time it is refreshed. However, some changes might cause problems. For example, if you change field names in the source data, the pivot table won’t automatically detect those changes.
Another problem that can occur is protecting the source data, so people can only view their data, as filtered in the pivot table. You’ll have to take special steps, to protect it.
When you’re saving a workbook that contains a pivot table, you might not be sure if it’s best to save the source data with the file. This article explains the pros and cons of each option.
Source Data VBA
For some source data tasks that you do frequently, there are sample data source macros that could help you save time. The examples on that page will:
- List Pivot Table Data Sources
- List Pivot Table Data Sources or MDX
- Change Data Source for All Pivot Tables
Another macro, by Héctor Miguel Orozco Díaz, applies a filter to the source data, when you double-click a data cell in a pivot table.
Or, create a User Defined Function that shows how many records are in the source data cache.
Another macro lets you format the pivot table values, based on the number format used in the source data.