O'Reilly Hacks
oreilly.comO'Reilly NetworkSafari BookshelfConferences Sign In/My Account | View Cart   
Book List Learning Lab PDFs O'Reilly Gear Newsletters Press Room Jobs  



HACK
#46
Share PivotTables but Not Their Data
Create a snapshot of your PivotTable that no longer needs the underlying data structures
[Discuss (0) | Link to this hack]

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 , then click OK.

Figure 1. Copy Picture dialog in action

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 , 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.

Figure 2. Original PivotTable contrasted with a picture of the PivotTable

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.


O'Reilly Home | Privacy Policy

© 2007 O'Reilly Media, Inc.
Website: | Customer Service: | Book issues:

All trademarks and registered trademarks appearing on oreilly.com are the property of their respective owners.