Excel Video 25 wraps up our discussion of the Pivot Table Options Menu by discussing the Printing tab and the Data tab. The Printing tab has three pretty straight-forward check boxes that control whether to print the expand/collapse buttons on a printed Pivot Table and printing row/column labels on Pivot Tables that extend over one printed page.
T
The Data tab allows you to save external source data with your spreadsheet so that you can pivot the data if even you can’t access the external data. If you’re away from the office, on a laptop or other computer, or for some reason can’t access external data, this option is very helpful. You can also turn off the ability to double-click a Pivot Table cell to show details. This isn’t a complete security measure on its own, but combined with password protecting the worksheet and other precautions it may come in handy. If you have external data (the data is stored outside of the spreadsheet), you’ll often want to check the third box to refresh or update the data when the spreadsheet is opened.
The Retain items deleted from the data source area is a little tricky to understand. If you have items that were in the original data source but have been deleted (because the data was somehow updated, refreshed, changed, etc.), Excel by default automatically keeps the items in the filter list. If the items are never coming back, you can change this setting to eliminate the phantom items from cluttering your filter lists.
The final trick is a setting at the bottom of the Field List that defers any changes you make in the Field List from updating the Pivot Table until you click update. If you have a ton of data and need to make several changes, turning off the update until you’re finished may save you some processing time.
Stay tuned. In Excel Video 26 there’s one more data grouping trick I’d like to show you.