Updating pivot tables in excel 2016
Seems like the slicers are excluded from the Excel side refresh. If a user is allowed the privileges above on a protected sheet, then updating the slicer source data should perhaps follow through to the slicer?
Having said that, upon protecting the sheet the following options were ticked allowing the user to operate the slicer and have the pivot charts / tables update: I’m unsure whether this is a bug or not.
While refreshing a Pivot table is as easy as two clicks, you still need to do this every time there is a change.
To make it more efficient and auto-refresh the Pivot Table whenever there is a change in the data source, you can use a simple one-line VBA macro code.
Without a table your range reference will look something like above.
In this example, if we were to add data past Row 51 or Column I our pivot table would not include it in the results. Now when you create a pivot table you can reference it with a name instead of a range.
Since your Pivot Table is created using the Pivot Cache, when the existing data changes or when you add new rows/columns to the data, the Pivot Cache does not update itself automatically, and hence, the Pivot Table also does not update.
To do this: Note that if you change the data source into an Excel Table and then use the Excel table to create the Pivot Table, you don’t need to use the change data source option.
You can simply refresh the Pivot Table and it’ll account for the new rows/columns.
You need to force a refresh every time there are changes. You can also by selecting any cell in the Pivot Table and using the keyboard shortcut ALT F5.
Once you force a refresh, the Pivot Cache gets updated, which is reflected in the Pivot Table. Quick Tip: It’s a good practice to convert the data source into an Excel Table, and use this Excel Table to create the Pivot Table.
When you add data to the table, you won’t need to update the range in your pivot table.