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.

NOTE: For Excel 2013 instructions, see: Find the Source Data for Your Pivot Table

How To Locate the Source Data

To locate the source data, 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.

PivotChangeSource

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.

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.

SheetUnhide

___________________________

 

Share and Enjoy

  • Facebook
  • Twitter
  • Google Plus
  • LinkedIn
This entry was posted in Functions, Grouping, Pivot Chart, Pivot Table, Protection, Refresh, Source Data. Bookmark the permalink.

13 Responses to Locate the Source Data For a Pivot Table

  1. Edward says:

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

    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. 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?

    Thanks
    Mehrnaz

  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.

    Regards,

  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.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>