Excel Pivot Table in Office 365 Will Not Refresh

[NanCody]

My source data is an Excel table and when I add data to the table and refresh the pivot table, it does not refresh. I have locked calculated cells in my source data. Could that be the problem? I unprotected the sheet with my source data but that didn't fix the problem. I tried changing the source data from a table to a range before I refreshed and that didn't fix it either. I am stumped.

 

[Alex Blakenburg]

Welcome to the Forum.

I assume the table is not the output of Power Query but a stand alone table.
If you go into the PivotTable Analyze > Change Data Source dialogue box do you definitely see the table name in the address box there ?

If you go to the bottom of the table do you see the little triangle at the bottom right hand corner ? If not find where it is and drag it down to the last row. Then refresh the pivot table.
You can also check the table covers all the rows by positioning yourself near the bottom (assuming it is a big table) hitting Ctrl+A, it should select from the 1st Data row to the Last Row in the table.

 

[NanCody]

Thanks for the response. I didn't know about the Ctrl +A. I figured out the problem and I feel really stupid- I was using some columns with calculated fields and I had hidden the columns with the calculated fields. When I added new rows, the new rows didn't have the calculations because I hadn't copied the calculations down. My pivot table was using the calculated fields so of course it didn't update. I copied the calculations down and then I could use the table and refresh.

 

[Alex Blakenburg]

Thanks for letting us know. If you are using an actual Excel table, just make sure you copy the formula to "ALL" the data rows at the same time (including the cell you are copying from - if you don't have any filters applied Ctrl+Spacebar will select all the rows).
It shouldn't matter if the columns are hidden. If you have done the above and you add new rows to the table the calculation should automatically be applied to the new rows. Let me know if that is not the case and you need help with that.

 

[NanCody]

Thanks I didn't know about the Ctrl+Spacebar. I used it to copy down all of the formulas so we should be good to go now. You have been so helpful.

 

[Alex Blakenburg]

No problem. Glad you found it helpful.