Pivot Table Source Data

How to locate and change the source data for an Excel pivot table. For missing source data, steps to try to recreate it. Change options, to save source data with pivot table file.

NOTE: For macros that help manage the source data, go to the pivot table source data macros page. For a macro to unpivot the source data, go to the Unpivot Excel Data Macro page

Author: Debra Dalgleish

Locate and Fix Source Data

In this video, you'll see how to locate the pivot table data source, then check the data source, to make sure it includes all the rows and columns that you need. If necessary, adjust the data source, to include new rows or columns.

NOTE: For a long term solution, use a named Excel table, as a data source. It will adjust automatically, if new rows are added.

The written instructions are below the video, and you can download the sample file to follow along with the video. Also, the full video transcript is further down on this page - go to video transcript.

Video Timeline

• 00:00 The Orders Pivot Table
• 00:21 Manually Check the Numbers
• 00:33 Find the Source Data
• 01:08 Change PivotTable Data Source Window
• 01:27 Fix the Data Source Range
• 01:57 Create a Dynamic Source
• 02:48 Create a Named Table
• 04:22 Use Dynamic Source for Pivot Table
• 04:56 Test the Dynamic Source
• 05:31 Conclusion

Locate the Source Data

After a pivot table has been created, you might want to add new records in the source data, or change the existing records. In a large workbook, it can be difficult to locate the exact source for the pivot table, if there are several tables or lists.

Follow these steps, to find the data source of a pivot table:

1. Select any cell in the pivot table.
2. On the Ribbon, under the PivotTable Tools tab, click the Analyze tab (in Excel 2010, click the Options tab).
3. In the Data group, click the top section of the Change Data Source command.

The Change PivotTable Data Source dialog box opens, and you can see the the source table or range in the Table/Range box. This may be a reference to a sheet and range of cells, such as

• Orders!$A$1:$H$9

or a table name, such as

• Sales_East

Behind the dialog box, you can see the source range on the worksheet, surrounded by a moving border.

In the screen shot below, the source data range ends at row 9, and a new record has been added in row 10. That record will not appear in the pivot table, unless the Data Source is adjusted.

Adjust the Source Data Range

If a pivot table's source data is a static reference to a specific sheet and range, it does not adjust automatically when new data is added. In the screen shot above, the Data Source range refers to Orders!$A$1:$H$9, and it can be manually adjusted, to include row 10.

To manually adjust the static source range:

1. In the Change PivotTable Data Source window, type the new ending row in the range reference -- in this example, change from Orders!$A$1:$H$9 to Orders!$A$1:$H$10
2. Click OK, to close the window.

You can adjust the source range when necessary, so it includes any new data, but a better solution is to create a dynamic source, and base the pivot table on that. Instructions are below.

Change the Source Data

Occasionally, you might need to change a pivot table, so it uses a different data source. In this example, a new table has been created, with only data for the East region. The pivot table will be changed to use that source, and then it can be send to a manager in the East region, without revealing the West region's data.

NOTE: After changing to a different source table, you might need to rename some fields, or add different fields to the pivot table.

To change the source data for an Excel pivot table, follow these steps:

1. Select any cell in the pivot table.
2. On the Ribbon, under the PivotTable Tools tab, click the Analyze tab (in Excel 2010, click the Options tab).
3. In the Data group, click the top section of the Change Data Source command.
4. The Change PivotTable Data Source dialog box opens, and you can see the the source table or range in the Table/Range box. In this screen shot below, the current data source is the Sales_Data table, which contains data from both regions..

   

5. Delete the existing data source, and type a new table name, or select a new data source range. The Table/Range is changed to Sales_East, where only the East data is listed.

   

6. Click OK.

Dynamic Source for Pivot Table

Instead of using a static range as the pivot table's data source, a better solution is to create a dynamic range, that will adjust in size automatically.

Create Dynamic Source -- Excel Table

Dynamic Source -- Named Range

Dynamic Source -- Dynamic Array

Use a Dynamic Source

Dynamic Source -- Excel Table

Instead of using a static range as the pivot table's data source, a better solution is to create a dynamic range, based on a named Excel table. There are written instructions here, or watch the video below.

Make note of the table name, so you can use it as the pivot table source

Dynamic Source -- Named Range

If you can't use an Excel Table as the source data, you create a dynamic named range, based on a an INDEX or OFFSET formula. There are written instructions here, or watch the video below.

The video shows how to create a dynamic range with the OFFSET function, in Excel 2007, and the steps are similar in later versions.

NOTE: Remember the range name, so you can use it as the pivot table source

Dynamic Source - Dynamic Array

If you have an Excel version that supports the new functions, such as SORT, or UNIQUE, you can use those functions to create dynamic arrays. This technique uses formulas only - no macros.

In the Download section, get the Filtered Source Data sample file. It shows how to set up a named range with only the visible rows from a named Excel table.

Here is the filtered data, on a different sheet, with only the 2 reps, and 3 categories from the visible rows.

Then, you can create a pivot table based on that filtered data only.

Use Dynamic Source For Pivot Table

After you create a dynamic source that contains the pivot table data, follow these steps, to use that source data:

1. Select any cell in the pivot table.
2. On the Ribbon, under the PivotTable Tools tab, click the Analyze tab (in Excel 2010, click the Options tab).
3. In the Data group, click the top section of the Change Data Source command.
4. In the Change PivotTable Data Source dialog box, clear the Table/Range box
5. In the Table/Range box, type the name of the table or named range that you created. In this example, the dynamic source is a named table -- tblOrders.
6. Click OK

Save Source Data With Pivot Table

When you create a pivot table in your workbook, the source data records are saved in a special memory area -- a pivot cache. Then, when you close the file, Excel can save the source data in this pivot cache, or clear that memory.

There are advantages and disadvantages to both options:

If you save the Source Data:

• File will be larger
• File may open more quickly

If you do not save the Source Data:

• Fill will be smaller
• File may open slowly while the pivot cache is rebuilt
• Pivot Table must be refreshed after opening the file

How To Change the Setting

To turn the Save the Source Data setting on or off:

• Right-click a cell in the pivot table, and click PivotTable Options.
• On the Data tab, in the PivotTable Data section, add or remove the check mark from Save Source Data with File
• Click OK.

Refresh Data When Opening

If you choose to turn off the Save Source Data with File option, you should turn on the Refresh Data When Opening the File option. That setting is just below the Save Source Data check box.

Otherwise, you'll see a message when you try to filter the data, or make any other layout changes.

• "The PivotTable report was saved without the underlying data. Use the Refresh Data command to update the report."

If you see that message, click OK, then manually refresh the pivot table.

And to avoid the annoying message, turn on the Refresh Data When Opening the File option.

Recreate Pivot Table Source Data

If you accidentally delete the worksheet that has the source data for your pivot table, or if you received a file without the pivot table's data, you might be able to use the pivot table's Show Details feature to recreate it.

NOTE: This will not work for all pivot tables, but is worth trying, to recover the source data.

To try to recreate the source data for a pivot table, follow these steps to use the Show Details feature:

1. Make sure that none of the items in the pivot table fields are hidden -- clear all the filters and Slicers that have been applied.
2. The pivot table does not need to contain all the fields -- just make sure that there is at least one field in the Values area.
3. Show the grand totals for rows and columns. If the totals aren't visible, select a cell in the pivot table, and on the Ribbon, under PivotTable Tools, click the Analyze tab. In the Layout group, click Grand totals, then click On for Rows and Columns.
4. Double-click the grand total cell at the bottom right of the pivot table. This should create a new sheet with the related records from the original source data.

Fix the Extracted Data

If you recover the source data, you might need to make some additional changes, to restore it to its original condition.

• Rename the table, using the original source data's name, if known.
• If the original source data contained formulas, you will have to re-create them, because the Show Details feature exports the data only.
• The columns in the extracted data will be in the same order as they were in the original source data.
• The extracted data will be formatted with the workbook's default Table format. You can apply a different Table format, or apply your own formatting.
• Note: If you had made changes to the source data and not updated the pivot table, those changes won't be in the extracted data.

Connect to the Extracted Data

If you rename the table that was created during the Show Details process, and use the same name as the table that originally held the source data, the pivot table might automatically connect to the new source data.

If not, you can connect to the recreated source data -- follow the steps in the section above: Use Dynamic Source For Pivot Table -- use the table name that you gave to the new table.

Video Transcript - Locate and Change Excel Pivot Table Data Source

Here is the full transcript for the video shown above, Locate and Change Excel Pivot Table Data Source.

----------------------------

Orders Pivot Table

In this video, you'll see how to find the source data for a pivot table and fix that source data, if there's a problem getting the new or changed data that you've entered. In this pivot table, I'm showing orders. One of the products we sell is paper, and I entered a new order, with 200 as the quantity, and it's not showing up here.

If I right click and refresh, it keeps showing 20 and 10 so it's not picking up that new record. I'm going to find the source data and see if there's a problem.

Find the Source Data

To find the source data, I have the cell selected in the pivot table. Up on the Ribbon, I can see Pivot Table Tools, and I'm going to click Analyze. If you're using Excel 2010, this would say Options.

I'll click here, and here's the Change Data Source. There's also a drop down list, but it's quicker to just click at the top of this.

That opens up the Change PivotTable Data Source window.

Change PivotTable Data Source Window

It's showing that there's a range selected, and I can see those little moving lines in the background, and we can see that one row is not included here.

It stops at row 9 and the new record that I put in is row 10. That's where my 200 is, and it's not being picked up.

To change this and fix the problem, I can adjust the range that's included here. So I could click, just back space, and type 10, instead of 9.

Or I can select all of this and delete it.

Then on the worksheet, select the exact range that I want to use. Then click OK, and when I go back to the pivot table, instead of 20, it's now showing 220, so it's picking up the new data.

Create a Dynamic Source

You could continue to adjust that range as you add new rows to the data source.

But a better solution is to create a dynamic source for your pivot table, which will adjust automatically, if you add new records.

It's easy to do that, if you've got Excel 2007 or later. I'm going back to the Orders sheet.

This is just a list that's typed on the worksheet. We've got column headings and a row for each order, but if I look up on the ribbon, there's no extra tab at the top.

We're going to create a named table. It will be a special feature, and when I select a cell, if it's a named table, I'll see another toolbar that I can use, to work with that table.

Create a Named Table

To create the table, it's quick and easy. Just select a cell in your list and on the Ribbon click Insert, Table.

It does a very good job of detecting the range, but if it didn't select all the cells, you can adjust what's typed in here.

Make sure that the check mark is here for My Table Has Headers, and then click OK.

You'll get a formatted table. I'll just click on one cell.

I don't usually like the formatting that it puts on the table at first, but you can go up to the Design tab, there's that Table tab that is appeared now, and select something that's simpler and easier to read.

The next thing you should do right away is change the name of the table. It will give a default name that ends with a number.

Just select that and I'm going to call this tbl, for table, tblOrders, and press Enter.

So now we have a table name, and I can see that name,

if I go to the Formulas tab, Name Manager.

And there's a list of all the names in this workbook. So it makes it easy to spot this, if you have several tables in your workbook.

I'll close this, and the next step will be to use our name, so going back to the Table Tools, we're going to use this name, tblOrders, as our source for the pivot table.

Use Dynamic Source for Pivot Table

I'm going to just click in here, and then Ctrl+ C to copy that name.

I'll go back to my pivot table now. And then with a cell selected in the pivot table, I'll go back to the Analyze tab, click Change Data Source, and here's the current data source.

I'm going to use Ctrl+ V to paste what I copied as the table name. So it's now going to use this dynamic range which will adjust as we add new records.

Test the Dynamic Source

Click OK, and nothing looks different here, but I'm going to go and add another order.

Just copy what we have above, by using Ctrl and the double quote. I'll put in 100, so now we should see 320, where we had 220 before.

Going back to Pivot Sales, it still says 220. I'll refresh, and now we've got 320.

So I didn't have to adjust the range of the pivot table source data. That adjusted automatically, because it's a named table.

Conclusion

So if your pivot table data will change frequently, make sure you use a dynamic source, like a named Excel table, and it will adjust as you add or delete records.

For more Excel tips and tutorials, and to download the sample file for this video, please visit my Contextures website, at www.contextures.com

-------------------

Download the Sample File

Pivot Source Example: To follow along with the tutorials, you can download the DateAmt.zip file. The zipped file is in xlsx format, and does not contain macros.

Filtered Source Data Example: This example is for Excel versions that have the new functions, such as SORT and UNIQUE. Download this sample file to see a pivot table is created from the visible rows only, in a filtreed Excel table. The zipped file is in xlsx format, and does not contain macros.

More Tutorials

Fix Pivot Table Source Data

Pivot Table Refresh

Pivot Table Intro

Summary Functions

Clear Old Items in Pivot Table