Ask Jackie: How do I preserve currency formatting in my pivot table?
Question:
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.
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 *).
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.
Click to Grab Your Excel Ebook – Free!
