Home

EXCEL PIVOT TABLES

%3CLINGO-SUB%20id%3D%22lingo-sub-771558%22%20slang%3D%22en-US%22%3EEXCEL%20PIVOT%20TABLES%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-771558%22%20slang%3D%22en-US%22%3E%3CP%3EI%20often%20take%20a%20Pivot%20Table%20and%20send%20it%20out%20to%20individuals%20as%20a%20report.%3C%2FP%3E%3CP%3EI%20like%20to%20send%20only%20values%20in%20my%20report%20so%20I%20often%20copy%20the%20P.T.%20and%20Paste%20Value%20the%20results.%3C%2FP%3E%3CP%3EThis%20action%20means%20that%20the%20formatting%20is%20left%20behind.%3C%2FP%3E%3CP%3EHow%20do%20I%20keep%20the%20formatting%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESincerely%2C%3C%2FP%3E%3CP%3EGEMHORN%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-771558%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-771617%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20PIVOT%20TABLES%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-771617%22%20slang%3D%22en-US%22%3EIf%20you%20only%20copy%20the%20table%20area%20of%20your%20pivot%20table%20(omitting%20the%20page%20filters)%2C%20you%20can%20paste%20special%20values%20and%20then%20formats.%20Tedious%20indeed.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-771676%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20PIVOT%20TABLES%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-771676%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F381447%22%20target%3D%22_blank%22%3E%40GEMHORN%3C%2FA%3E%26nbsp%3B-%3C%2FP%3E%3CP%3EWhat%20if%20you%20Highlight%20the%20range%20of%20the%20Pivot%20Table%20then%20Copy%20as%20Picture%20as%20shown%20when%20printed%20(ALT%20%26gt%3B%20H%20%26gt%3B%20C%20%26gt%3B%20P%20%26gt%3B%20Right%20Arrow%20%26gt%3B%20Enter)%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20will%20Copy%20to%20the%20clipboard%20for%20you%20to%20paste%20into%20Word%2C%20Email%2C%20etc.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-771803%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20PIVOT%20TABLES%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-771803%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F381447%22%20target%3D%22_blank%22%3E%40GEMHORN%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERight%20click%20in%20your%20pivot%20table%20%26amp%3B%20click%20Options.%26nbsp%3B%20On%20the%20Data%20tab%2C%20uncheck%20'save%20source%20data%20with%20file'.%26nbsp%3B%20You%20can%20now%20copy%20the%20PT%20worksheet%20to%20a%20new%20workbook%20%26amp%3B%20maintain%20the%20formatting.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-773099%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20PIVOT%20TABLES%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-773099%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F381447%22%20target%3D%22_blank%22%3E%40GEMHORN%3C%2FA%3E%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20420px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F124466i7824C6A211308ABE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture.PNG%22%20title%3D%22Capture.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERepeat%20Step%201%20that%20you%20have%20mentioned.%20Under%20paste%20special%20select%20formats%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-773137%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20PIVOT%20TABLES%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-773137%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F362203%22%20target%3D%22_blank%22%3E%40Theo_Bear%3C%2FA%3Eif%20the%20entire%20pivot%20table%20is%20included%20in%20the%20selection%2C%20paste%20formats%20does%20not%20paste%20the%20formatting.%20That%20is%20the%20problem%20we're%20trying%20to%20solve!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-773220%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20PIVOT%20TABLES%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-773220%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%2C%20select%20and%20copy%20the%20entire%20pivot%20table.%20You%20can%20use%20Control%20%2B%20A%20to%20select%20the%20whole%20table.%3C%2FP%3E%3CP%3ENext%2C%20in%20a%20new%20worksheet%2C%20use%20Paste%20Special%2C%20then%20Values.%20This%20will%20strip%20away%20all%20formatting%20and%20leave%20you%20with%20just%20the%20data.%3C%2FP%3E%3CP%3EWith%20the%20pivot%20table%20still%20on%20the%20clipboard%2C%20use%20paste%20special%20again.%20This%20time%2C%20choose%20Formats.%20This%20will%20bring%20in%20all%20the%20cell%20and%20number%20formatting.%3C%2FP%3E%3CP%3EFinally%2C%20if%20you%20like%2C%20you%20can%20use%20Paste%20Special%20one%20more%20time%20to%20bring%20in%20the%20original%20column%20widths.%3C%2FP%3E%3CP%3EPasting%20column%20widths%20is%20one%20of%20those%20quirky%20features%20that%20makes%20paste%20special%20so%20useful%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-773378%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20PIVOT%20TABLES%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-773378%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F362203%22%20target%3D%22_blank%22%3E%40Theo_Bear%3C%2FA%3Edepends%20on%20what%20you%20select%20sometimes%20it%20does%20refuse%20to%20paste%20formats%3C%2FLINGO-BODY%3E
GEMHORN
Occasional Visitor

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

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

@GEMHORN 

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 Capture.PNG

 

Repeat Step 1 that you have mentioned. Under paste special select formats

 

@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!

@Jan Karel Pieterse 

 

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?

@Theo_Bear depends on what you select sometimes it does refuse to paste formats
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies