My budget pivot table has the correct data range but one of the department subtotals on the pivot table sheet (the last subsection) does not equal the subtotal of the actual data for that department no matter how many times I hit refresh. If I go to the subset of data, highlight the amounts and then go back to the pivot table and hit refresh it comes up with the right number, the one that equals the subset. But it reverts back to the wrong subtotal upon refresh if I have my detail worksheet filtered on anything other than than department. Mind you, none of the other subtotals change and there are about 10 different departmental budgets. PLUS, the incorrect subtotal leaves out
Has anybody experienced this phenomenon? I am discomfited when Excel doesn't work as I've come to expect. Of course, a lot of things I thought I could count on have left me baffled these days.
Hi Jeana,
No, I have not seen any errors in the pivot tables I have been working with that didn't originate from bad data inputs. That said I have seen blog posts in different forums where users complained about different strange issues, so I suppose it can happen. Just for test, how does it work if you create a new pivot table? What I am after here is to check if it could be the pivot table cache that somehow have become corrupt and therefor causes this error.
Some extra info about the pivot cache. https://www.myonlinetraininghub.com/excel-pivot-cache
Br,
Anders
Thanks for responding. My faith in Excel has been restored ... for the time being. What I had were 4 rows with subtotal formulas where they should have been straight sums. With the data filtered the subtotal went to zero for those line items when it wasn't filtered of course it gave me the correct numbers. Totally my bad for not double checking those formulas before I pasted in the data.
Whew...
Hi,
Great that you found out the reason of the error. It is almost always those little things you don't think of that are the culprits.
/Anders
