+ Reply to Thread
Results 1 to 5 of 5

Change pivot table to values but keep formatting

  1. #1
    Registered User
    Join Date
    02-27-2007
    Posts
    38

    Change pivot table to values but keep formatting

    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
    Last edited by DaveF; 07-06-2010 at 10:10 AM.

  2. #2
    Registered User
    Join Date
    03-29-2010
    Location
    Mashhad
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Change pivot table to values but keep formatting

    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.

  3. #3
    Registered User
    Join Date
    02-27-2007
    Posts
    38

    Re: Change pivot table to values but keep formatting

    Quote Originally Posted by amator View Post
    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.

  4. #4
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Bentley, NM
    MS-Off Ver
    Excel 2010
    Posts
    229

    Re: Change pivot table to values but keep formatting

    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.

  5. #5
    Registered User
    Join Date
    11-21-2016
    Location
    SF Bay Area
    MS-Off Ver
    2010
    Posts
    2

    Re: Change pivot table to values but keep formatting

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1