MS-Excel / General Formatting

---

Share PivotTables but Not Their Data

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, right-click on a clean sheet, and select 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 fullcolor, 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 highlight the Pivot Table. Select Home → Clipboard → Paste → As Picture → Copy Picture, and make the selections in the Copy Picture dialog box that pops up. Then, click OK.

Pre-2007, hold down the Shift key, select Edit → Copy Picture, click anywhere outside the PivotTable, and select Edit → Paste.

You will end up with a fully colored and formatted snapshot of your PivotTable. 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 icon.

To use this latter method, press the Office button, select Excel Options → Customize, and choose "Commands Not in the Ribbon" from the Choose Commands From: box. Locate the camera, click it, press Add to add it to your Quick Access toolbar, and then click OK.

In pre-2007 versions, 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 right 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.

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