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.
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.
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.
___________________________
For more information on pivot tables, see the Pivot Table Topics on my Contextures web site.
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!!
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.
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!
Noooop…not working!
Thank you so much for “How To Locate the Source Data” – I couldn’t find this at all on the MS site!
Thanks Jane, I’m glad you found the information here.
Brilliant .. just what I needed.
I’ve just Added this site to my favourites!
In my Excel i don’t have this option. Where can i find it out?
Thanks
Mehrnaz