Pivot Table Source Data Articles

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.

Pivot Table Data Source Tips http://blog.contextures.com/

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.

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.


Pivot Table Data Source Tips http://blog.contextures.com/