Data source for pivot table

[tiredofit]

To find the data source for a PV, I click into the PV, go to the ribbon and click Analyse, then Change Data Source.

That reveals the data source.

However what I see on this occasion is simply Table1.

I go to the Formulas tab and choose Name Manager but can't see Table1.

FYI the data can be found here:

https://www.dummies.com/store/product/Excel-Power-Pivot-Power-Query-For-Dummies.productCd-111921064X,navId-322474,descCd-DOWNLOAD.html

Click on the downloads tab, unzip the file and the Excel workbook I am referring to is Chapter 3's Slicers.

Also, right clicking on the PV and choosing Refresh displays an error message:

Reference isn't valid

Thanks

 

[denzo36]

Table 1 means your the source for the Pivot table is formatted as an Excel table and not a normal Excel 'range'

You will know its an Excel table when you click anywhere in it, you will get the 'Table Tools' option in the ribbon

 

[tiredofit]

Table 1 means your the source for the Pivot table is formatted as an Excel table and not a normal Excel 'range'

You will know its an Excel table when you click anywhere in it, you will get the 'Table Tools' option in the ribbon

Thanks.

However I DON'T have any tables in the workbook!

Feel free to download it from the website if you like.

 

[denzo36]

Which chapter is the file you are looking at?

 

[tiredofit]

Which chapter is the file you are looking at?

Chapter 3's Slicers.

 

[denzo36]

It appears to be an external data source not contained in your workbook. Your source data for a PT doesn't have to be in the same workbook as the PT itself.

It looks like they are showing it as a demonstration as to what slicers can do

 

[tiredofit]

It appears to be an external data source not contained in your workbook. Your source data for a PT doesn't have to be in the same workbook as the PT itself.

It looks like they are showing it as a demonstration as to what slicers can do

Thanks for checking.

If you have a PV based on data that IS contained within the same workbook and you subsequently delete the data, then when you click refresh on the PV, you will get nothing.

I agree the data source need not be in the same workbook but I assume it MUST exist somewhere (because otherwise how is the PV constructed)?

Is there no way to find out?

BTW, when I write PV, I actually mean PT!

 

[RoryA]

If you have a PV based on data that IS contained within the same workbook and you subsequently delete the data, then when you click refresh on the PV, you will get nothing.

Not necessarily. If your source data is a Table on a sheet and you delete that sheet, you will get the same Invalid reference error when you try and refresh the pivot table.

 

[tiredofit]

Not necessarily. If your source data is a Table on a sheet and you delete that sheet, you will get the same Invalid reference error when you try and refresh the pivot table.

I agree.

I just tested this myself by creating a PT, then deleted its data.

Surprisingly (too me) the PT still exists, though obviously is now static.

This behaviour appears to be a bit weird to me.

If data was added and the PT refresh option is chosen, the PT reflects this.

So surely if the data is removed, refresh should alert the user that no data exist and as a result, the PT shouldn't still remain.

 

[RoryA]

The source does not exist any more. That is why you cannot refresh the pivot table - the source table has been deleted.