Ask Jackie: Disappearing Pivot Table Formats

Untitled Document

Ask Jackie: How do I preserve currency formatting in my pivot table?


I set the format of the numbers in my pivot table to currency. When I rearrange my pivot table, however, the formatting disappears and I have to reapply it. Is there any way to prevent this from happening?

Short Answer:

Yes, there is. Please note, however, that the steps I show won’t work if you use the Clear All command to clear your pivot table. In order to preserve your currency format, you must do two things:

1. Set your options to preserve pivot table formatting.

2. Select your entire pivot table before applying the currency format.

Detailed Answer:

Here are the step by step instructions.

Step 1 – Preserve Pivot Table Formatting.

Click anywhere in the pivot table.

Click the Options tab.

In the Pivot Table group, Click the drop down list next to Options. Click Options again.

When the dialog box pops up, click the Layout & Format tab. Put a check mark next  to Preserve cell formatting on update.

Pivot Table Options - Layout & Format

Click OK.

Step 2 – Select the Entire Pivot Table.

Click within the pivot table.

Click the Options tab.

Within the Actions group, click the Select drop down list, then Entire Pivot Table (Shortcut: CTRL SHIFT *).

Select Excel pivot tables


Step 3 – Apply the Currency Format.

Click within the pivot table.

Type CTRL 1 to open the format number dialog box.

Select the currency format.

Step 4 – Test Your Results.

Re-arrange your pivot table and make sure that the currency formatting remains. Remember: if you use the Clear All command, your currency formatting will be cleared also.


Did you find this Excel Pivot Table tip useful? If so, won’t you share your comments?

Want to learn pivot tables the easy, painless way? Our Painless Pivot Tables course might be right for you. It’s offered in Atlanta, online, or at your site.


About the Author

Jackie Kiadii and her team of subject matter expert (SME) trainers provide Microsoft software training that delivers measurable results – increasing productivity, reducing inefficiency, and improving the bottom line. Topics include: Project, Access, Excel, Excel VBA, PowerPoint, Word, Outlook and Publisher.

Need onsite group training? Call 770.498.7333 or email us. Our courses can be customized to your needs. Jackie has provided software training for employees of The Coca-Cola Company, Emory University, Novelis, and more. This training is available for groups of 4 or more students.

Atlanta Computer Training on Facebook Atlanta Computer Training - LinkedIn Software Tips on Twitter

Click to Grab Your Excel Ebook – Free!

Get the free Excel ebook

12 Tips to Empower Excel Users



Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>