Friday, 4 July 2014

Excel - Reference is Not Valid when refreshing pivot table data

Issue
In Excel if you click the Refresh button to update your pivot table data and the following "Reference is not valid." appears:


...this means the pivot table cannot understand the data source. Often this is because the pivot table was previously referencing a 'table' and that table (data set) has been deleted and replaced with something else, maybe another table.


Solution
Click on the sheet where the data/table is

If it's formatted as a table check the table name - on the far left

Change to the sheet where the pivot table is

Click once on the pivot table

Under 'PivotTable Tools' click Options (or Analyze if you are using Excel 2013)

Click Change Data Source

It'll ask you for the range or table name, make sure the table name is correct


Explanation
Pivot tables reference either a table name or a range of cells for their data. If you replace the data sometimes the table name or range is changed. When you try to refresh the pivot table data, Excel can't find the data because it's referring to the wrong/incorrect table name. Using the Change Data Source allows you to select where your pivot table gets its data from.

An alternative could be to change the table name to the name the pivot table is expecting. This also works well. Which method you choose is up to you.

For more information on refreshing pivot table data please see the previous article here.
http://mgxp.blogspot.ch/2014/07/excel-refresh-data-for-pivot-table.html


Conclusion
If you are using tables with pivot tables and you update the data you may have this problem. But it's easily fixed with the Change Data Source option or by changing the table name.


Excel Version?
Excel 2013 and 2010, probably earlier versions too - feel free to write in the comments below about your experiences.
Post a Comment