Items to consider before releasing your file to users.
Where/when to use the technique
When you want to review your file to make sure that you haven’t given too much access to your data.
The biggest consideration
Is the audience authorized to see your raw data?
- If not, then don’t send a full PivotTable!
- If your file can be sliced, the data is there and can be extracted IN FULL
Clear the data cache from the file
- Go to PivotTable Options > Data > Uncheck “Save data with file”
- This will clear the PivotCache upon saving
- The pivot will still show on the worksheet, but it won’t be sliceable unless the user can connect to the data source
Force the data to be up to date
- Stale data can be dangerous in many situations
- Go to PivotTable Options > Data > Check “Refresh at open”
- Does the user have proper authorization credentials to connect?
- Yes: The data will silently be updated
- No: The data refresh will fail with an error message
Turn off the Show Details feature
- It’s very easy to litter your workbook with extra worksheets that clutter the story
- Turning off this feature will prevent accidental drilling
- Go to PivotTable Options > Data > Uncheck “Enable Show Details”
Preserve your formatting work
- Consider preserving PivotTable column widths and slicer positions
- PivotTable Options > Layout & Format > Uncheck “Autofit column widths on update”
- Right click the Slicer > Size & Properties > Properties > Don’t move or size with cells
Lesson notes are only available for subscribers.