Excel Hacks Ebook [Electronic resources]

Hack 46 Share PivotTables but Not Their Data

Create a snapshot of your PivotTable that no longer needs the underlying data structures.

You might need to send PivotTables for others to view, but for whatever reason you cannot send the underlying data associated with them. Perhaps you want others to see only certain data for confidentiality reasons, for instance. If this is the case, you can create a static copy of the PivotTable and enable the recipient to see only what he needs to see. Best of all, the file size of the static copy will be only a small percentage of the original file size.

Assuming you have a PivotTable in a workbook, all you need to do is select the entire PivotTable, copy it, and on a clean sheet select Edit Paste Special... Values. Now you can move this worksheet to another workbook or perhaps use it as is.

The one drawback to this method is that Excel does not paste the PivotTable's formats along with the values. This can make the static copy harder to read and perhaps less impressive. If you want to include the formatting as well, you can take a static picture (as opposed to a static copy) of your PivotTable and paste this onto a clean worksheet. This will give you a full-color, formatted snapshot of the original PivotTable to which you can apply any type of formatting you want, without having to worry about the formatting being lost when you refresh the original PivotTable. This is because the full-color, formatted snapshot is not linked in any way to the original PivotTable.

To create a static picture, format the PivotTable the way you want it and then select any cell within it. From the PivotTable toolbar, select PivotTable Select Entire Table. With the entire PivotTable selected, hold down the Shift key and select Edit Copy Picture. From the Copy Picture dialog box that pops up, make the selections shown in Figure 4-2, then click OK.

Finally, click anywhere outside the PivotTable and select Edit Paste. You will end up with a fully colored and formatted snapshot of your PivotTable, as shown in Figure 4-3, complete with formatting. This can be very handy, especially if you have to email your PivotTable to other people for viewing. They will have the information they need, including all relevant formatting, but the file size will be small and they won't be able to manipulate your data. Also, they will be able to see only what you want them to see.

You also can use this picture-taking method on a range of cells. You can follow the preceding steps, or you can use the little-noticed Camera tool on your toolbar.

To use this latter method, select View Toolbars Customize.... From the Customize dialog, click the Commands tab, from the Categories box, select Tools, and from the Commands box on the righthand side scroll down until you see Camera. Left-click and drag-and-drop this icon onto your toolbar where you want it to be displayed. Select a range of cells, click the Camera icon, and then click anywhere on the spreadsheet, and you will have a linked picture of the range you just took a picture of. Whatever data or formatting you applied to the original range will automatically be reflected in the picture of the range.