Advanced features
1. Use calculated fields instead of adding columns to source data
You can use a pivot table to calculate additional items. This is more flexible and efficient than adding extra columns with formulas to your data. For example if you wish to calculate profit as a % of sales then to do this you:
Ø Click any cell within your pivot table
- From the pivot options menu select ‘calculated field…’ from the ‘field, items, and sets’ drop down menu to bring up the ‘Insert calculated field’ dialogue box.
- Type the name of your new field in the top box (do not click add at this stage) e.g. Profit %
- The formula box will update as you select fields and type in symbols such as ‘+,-,*,/,^,etc’
- Select required fields form the fields box – after each field click the ‘insert field‘.
- When complete click OK. Your new field (e.g. Profit %) will now appear in your list of fields. You can use this new field in exactly the same way as any field coming from your source data.
- For profit % example: select profit field (this now shows in the formula box; click insert; enter’/’ in the formula box; select sales field and click insert. Click Ok
NB you can use normal excel formula within a calculated field formula e.g. text formulas, sum, etc
2. Use a table for your data to create a “dynamic range”
If you use an Excel Table for the source data of your pivot table, you get a very nice benefit: your data range becomes “dynamic”. A dynamic range will automatically expand and shrink the table as you add or remove data, so won’t have to worry that the pivot table is missing the latest data.
When you use a Table for your pivot table, the pivot table will always be in sync with your data.
To use a Table for your pivot table:
- Select any cell in the data use the keyboard shortcut Ctrl-T to create a Table
- Click the Summarize with PivotTable button (TableTools > Design)
- Build your pivot table normally
- Profit: data you add to your Table will automatically appear in your Pivot table on refresh
3. Create a self-contained pivot table
When you’ve created a pivot table from data in the same worksheet, you can remove the data if you like and the pivot table will continue to operate normally. This is because a pivot table has a pivot cache that contains an exact duplicate of the data used to create the pivot table.
- Refresh the pivot table to ensure cache is up to date (PivotTable Tools > Refresh)
- Delete the worksheet that contains the data
- Use your pivot table normally
4. Clone your pivot tables when you need another view
Once you have one pivot table set up, you might want to see a different view of the same data. You could of course just rearrange your existing pivot table to create the new view. But if you’re building a report that you plan to use and update on an on-going basis, the easiest thing to do is clone an existing pivot table, so that both views of the data are always available.
There are two easy ways to clone a pivot table.
- The first way involved duplicating the worksheet that holds the pivot table. If you have a pivot table set up in worksheet with a title, etc., you can just right click the worksheet tab to copy the worksheet into the same workbook.
- Another way to clone a pivot table is to copy the pivot table, and paste it somewhere else. Using these approaches, you can make as many copies as you like.
- When you clone a pivot table this way, both pivot tables share the same pivot cache. This means that when you refresh any one of the clones (or the original) all of the related pivot tables will be refreshed.
5. Unclone a pivot table to refresh independently
After you’ve cloned a pivot table, you might run into a situation where you really don’t want the clone to be linked to the same pivot cache as the original. A common example is after you’ve grouped a date field in one pivot table, refresh, and discover that you’ve also accidentally grouped the same date field in another pivot table that you didn’t intend to change. When pivot tables share the same pivot cache, they also share field grouping as well.
Here’s one way to unclone a pivot table, that is, unlink it from the pivot cache it shares with other pivot tables in the same worksheet:
- Cut the entire pivot table to the clipboard
- Paste the pivot table into a brand new workbook
- Refresh the pivot table
- Copy it again to the clipboard
- Paste it back into the original workbook
- Discard the temporary workbook
Your pivot table will now use it’s own pivot cache and will not refresh with the other pivot table(s) in the workbook, or share the same field grouping.
Excel Monkey 