Locate Source Data For a Pivot Table

You might have to make changes to a workbook that contains a pivot table. If someone else created that workbook, you may not be sure where to find the Excel table that was used as the pivot table’s source data. In some workbooks there can be several Excel tables, and it’s not immediately obvious which table was used. These tips will help you locate the source data for a pivot table.

Before You Start

  1. The following instructions are for Excel 2007. For newer versions of Excel, go here – Find the Source Data for Your Pivot Table
  2. The following instructions will take you to the source data, if it exists. Instead, if you want to recreate the source data, go here – Rebuild source data

How To Locate the Source Data for a Pivot Table

To locate the source data for a pivot table, follow these steps:

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

In the Change PivotTable Data Source dialog box, you can see the the source table or range in the Table/Range box. This may be a worksheet reference, such as

  • Sales_North!$A$1:$O$500

or a table name, such as

  • Sales_East

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

Change PivotTable Data Source dialog box
Change PivotTable Data Source dialog box

4. Click OK, to close the dialog box.

Unhide a Sheet

Usually the source range will be visible, and surrounded by a moving border. If the source range isn’t activated, it may be on a hidden worksheet. You can follow these steps to unhide a sheet:

  1. On the Ribbon, click the Home tab
  2. In the Cells group, click Format.
  3. In the Visibility section, click Hide & Unhide, and then click the Unhide Sheet command.
  4. In the list, select the sheet that you want to make visible, and click OK.
select the sheet that you want to make visible
select the sheet that you want to make visible

Video: Pivot Table Source Data

This video shows how to:

  • find the source data for an Excel pivot table, and
  • make sure it includes all the rows and columns that you need.

More Articles on Pivot Table Source Data

_________________________

21 thoughts on “Locate Source Data For a Pivot Table”

  1. Hi!
    Regarding pivot chart datasources.
    If I create a pivotchart on sheet 1 (for example)using the pivot table on sheet 1 and then make a copy of that sheet, sheet 2 for example, the datasource of the pivot chart is still linked to sheet 1.
    I need now to be linked to sheet 2. How can I do that!!!!?????
    Thanks for letting me know!!

  2. Hi!
    Thanks for your comment.
    On Excel 2007 it diesn’t work.
    They have really muffed up pivot tables.
    The chart always remains linked to the original pivot table.

    The latest one is that line charts colour series differently to the legend…brilliant.
    Blank cells appear as zero values on the line chart instead of being left blank…genius.

  3. Another source told me how to do it in Excel 2003! It’s so easy and saves SOOOO much time! Just open the Pivot Table Wizard, click back (duh), change your data source. Man, I wish I knew this sooner!

    Good luck!

  4. Thank you so much for “How To Locate the Source Data” – I couldn’t find this at all on the MS site!

  5. Thanks guys. This question has been vexxing me for some time. I found the answer quickly and easily at this web site.

    Much appreciated!

  6. In Excel 2003, I like to know where chart source data is located. When I select the chart and goto Chart on menu bar the source data is not highlighted. I have tried many different ways and not able to come up with a way to make excel show source data. Thanks for any suggestions.

    Regards,

  7. how to get the total data in the original format when pivot table has been created on the same worksheet.

  8. OK, what if someone has emailed you an Excel 2010 spreadsheet which includes pivot tables but no source data. You can still move fields around etc and surely the data must be somewhere but you can’t get to it. Any ideas?

    1. @Darryl, remove any filters from the pivot table, then double-click the grand total cell. That should dump all the data onto a new worksheet, from the pivot cache.

  9. Good Day, hope everyone is doing well 🙂

    Excel 2007 Question: I’m from a Freight Forwarding Firm. I work with various shipping lines like Maerskline, APL, Hamburg Sud, MSC etc…. Each Shipping Line or you can also name it as a Carrier, routes its cargo on specific Sectors like Gulf, West Africa, US East Coast, US West Coast; on these Sectors are various Ports, e.g US East Coast has the following ports (Atlanta [10], Chicago [5], Miami [19], New Jersey [4], New York [87], Savannah [14]). Each Ports DOCKS a Certain amount of Containers on Monthly Basis which i’ve mentioned in [ ].

    I already have this Data in Excel Sheet, moved to Pivot Table and then to respective Charts based on each Shipping Line that totals to 12 at the moment.

    I’ve 12 Excel Files and 12 Power Point presentations on basis of Charts.

    The number of Containers on each Shipping Line “CHANGES” on Monthly Basis depending on its Sales for that Month.

    Is There A Way To Have One Worksheet; 12 Shipping Lines Data One Below The Other, I Just Copy Paste The Data Into The Allotted Cells [end of every Month] & MY CHARTS CHANGE AUTOMATICALLY.

    Your kind assistance would be highly appreciated. Thanks

    Kind Regards,
    SAMI

  10. Hello,

    I was forwarded an excel file with various pivots. The pivots are referencing a tab in the Table/Range in the Change Data Source drop-down that is not visible. When trying to Unhide worksheets, the Unhide option is greyed out. How do I retrieve this worksheet that apparently exists, according the Table/Range field.

    Thank you.
    John

  11. i’m having figuring out how to double check what rows are included in the datasource when the source is named as a table not a range for a pivot table.

Leave a Reply to Edward Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.