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.

Figure 4-2. 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
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.

Figure 4-3. 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.