Change pivot table to values but keep formatting

**DaveF** · 07-01-2010, 11:48 AM

Hi All,

I have a pivot table in excel 2007.

I'd like to use copy-paste special-values over the top of it to get rid of the pivot table and data link, but keep the formatting, before sending the workbook in an email.

When I do this though, excel strips out all of the pivot tables formatting like bolded column headings and colors, and lines delineating the sections of the table.

Is there a way to turn a pivot table into just values in a workbook but keep the formatting provided by the pivot table?

Thanks,

Dave

**amator** · 07-01-2010, 12:59 PM

Excel doesn't have a command to unlink a pivot table, but it does have a flexible Paste Special command. Using that command, with the Value option, should do the job:

1. Select the pivot table cells and press Ctrl+C to copy the range.
2. Display the Paste Special dialog box.
3. In the Paste Special dialog box, choose the Values option, and click OK.

The pivot table is unlinked, but if you use Excel 2007 or Excel 2010, the fancy pivot table style formatting is gone:
To get the formatting back, you need to perform two steps:

1. Display the Office Clipboard, click the dialog box launcher icon in the bottom right corner of the Home - Clipboard group.
2. With the unlinked pivot selected, click the item on the Office Clipboard that corresponds to the pivot table copy operation. It will be the last item, unless you copied something else.

Now the pivot table is unlinked from its data source, yet retains all of its original formatting.

**DaveF** · 07-06-2010, 10:10 AM

Originally Posted by amator

Excel doesn't have a command to unlink a pivot table, but it does have a flexible Paste Special command. Using that command, with the Value option, should do the job:

1. Select the pivot table cells and press Ctrl+C to copy the range.
2. Display the Paste Special dialog box.
3. In the Paste Special dialog box, choose the Values option, and click OK.

The pivot table is unlinked, but if you use Excel 2007 or Excel 2010, the fancy pivot table style formatting is gone:
To get the formatting back, you need to perform two steps:

1. Display the Office Clipboard, click the dialog box launcher icon in the bottom right corner of the Home - Clipboard group.
2. With the unlinked pivot selected, click the item on the Office Clipboard that corresponds to the pivot table copy operation. It will be the last item, unless you copied something else.

Now the pivot table is unlinked from its data source, yet retains all of its original formatting.

Thanks, that worked.

**Don Brock** · 11-21-2016, 12:09 PM

Hi Amator,

Thanks for this response. It is the only thing I've found that works for this.

Do you know the VBA code to put this into a macro? I tried recording it, but it doesn't record that I'm going into the clipboard and selecting the item.

Thanks!

Don

**maw230** · 12-30-2015, 12:31 PM

This does not work for me. Some of the formatting remains, but not all. The column header fill colors disappear and the font color is changed. Some of the cell borders stay and some do not. I can't even predict what is happening and since the spreadsheet is sent to me each month I have no control over its initial formatting. Can't believe that I can't just unlink it from the source data.