Forum Discussion
EXCEL PIVOT TABLES
I often take a Pivot Table and send it out to individuals as a report.
I like to send only values in my report so I often copy the P.T. and Paste Value the results.
This action means that the formatting is left behind.
How do I keep the formatting?
Sincerely,
GEMHORN
7 Replies
Theo_Bear if the entire pivot table is included in the selection, paste formats does not paste the formatting. That is the problem we're trying to solve!
First, select and copy the entire pivot table. You can use Control + A to select the whole table.
Next, in a new worksheet, use Paste Special, then Values. This will strip away all formatting and leave you with just the data.
With the pivot table still on the clipboard, use paste special again. This time, choose Formats. This will bring in all the cell and number formatting.
Finally, if you like, you can use Paste Special one more time to bring in the original column widths.
Pasting column widths is one of those quirky features that makes paste special so useful?
Right click in your pivot table & click Options. On the Data tab, uncheck 'save source data with file'. You can now copy the PT worksheet to a new workbook & maintain the formatting.
GEMHORN -
What if you Highlight the range of the Pivot Table then Copy as Picture as shown when printed (ALT > H > C > P > Right Arrow > Enter)?
This will Copy to the clipboard for you to paste into Word, Email, etc.
- If you only copy the table area of your pivot table (omitting the page filters), you can paste special values and then formats. Tedious indeed.
