Locate the 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

The following instructions are for Excel 2007.  If you’re using Excel 2010 or later, click here to see how to locate the source data for a pivot table in that version.

Also, these instructions will take you to the source data, if it exists. If you want to recreate the source data, click here to see how to rebuild the source data from a pivot table.

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.


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.

locate the source data for a pivot table

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.

2. In the Visibility section, click Hide & Unhide, and then click the Unhide Sheet command.

3. In the list, select the sheet that you want to make visible, and click OK.


More Articles on Pivot Table Source Data


This entry was posted in Refresh. Bookmark the permalink.

20 Responses to Locate the Source Data For a Pivot Table

  1. Edward says:

    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. Edward says:

    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. KC says:

    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. Edward says:

    Noooop…not working!

  5. Jane says:

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

  6. Chas says:

    Brilliant .. just what I needed.

    I’ve just Added this site to my favourites!

  7. Mehrnaz says:

    In my Excel i don’t have this option. Where can i find it out?


  8. Mehrnaz, if you’re using Excel 2003, right-click on the pivot table, and click Pivot Table Wizard. Click the Back button, and you’ll see the pivot table source.

  9. Michael says:

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

    Much appreciated!

  10. ashok says:

    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.


  11. Andrew says:

    Thanks great help

  12. vivek prakash says:

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

  13. Darryl Downie says:

    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?

  14. Sami says:

    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,

  15. Lama says:

    Thank you! This tip saved my day

  16. John says:


    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.

Leave a Reply

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