0

Create a sheet "2015":

enter image description here

Create a sheet "pivot" und sum the amount:

enter image description here

Create a sheet "2016" with different data

enter image description here

Try to select the data from "2016" for the pivot table and hit the OK button and nothing will change...

enter image description here

Bonus: Create a new sheet with a pivot table and select the data from sheet "2016" and you will get the data from "2015".

I tried the same thing with Excel 2010 and it worked as expected (I get the data from "2016") and that is the way it worked for years in my sheets. I am curious to figure out if this is my setup/configuration or a bug.

EDIT: Similar question here.

3
  • I replicated this bug and noticed that data source didn't actually change. (Changed data source, hit return, but then when I went to change data source again, old values were still there.) So problem isn't that pivot table is displaying wrong data, but that it's impossible to change data source as desired. One more interesting aspect of this: data source will change if you change the dimensions of table. E.g. change 2015!$A$B to 2016!$A1$B3.
    – Jonathan
    Jul 16, 2017 at 5:03
  • I confirmed with MS that this is, in fact, a bug. Our finance team heavily relies on Pivot Tables, so it caused immediate impact to our business. We opened a ticket in early July, and here we are on September 18th, and it's still not resolved in the latest update.
    – TechSalad
    Sep 18, 2017 at 19:46
  • With version 1708 Microsoft fixed the issue.
    – steffan
    Sep 19, 2017 at 19:52

2 Answers 2

0

This is a bug and, I think -- but haven't been able to confirm -- a new one. It's a slightly dangerous one in that the "change data source" dialog doesn't give any indication that the data source isn't actually being changed.

The best workaround is to name the ranges you use as data for pivot tables. (This is a good practice anyway.) At least in my tests, using named ranges will cause the data source to change as expected.

0

I had this issue until today. I discovered that the issue was caused by the pivot table source having a VLOOKUP nested inside of an IFERROR. When I changed the nested functions to a VLOOKUP nested inside of an ISERROR nested inside of an IF, the Changed Data Source started working normally again. I arrived at this solution from trying to save the workbook as Excel 97-2003 workbook and receiving the notification that IFERROR was unsupported by Excel 97-2003.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .