Excel Pivot Tables: How to keep your Pivot Table number formats from being reset (Excel 2010)

// Recommended Excel Courses //
1. Launch Excel Macros & VBA School: go.launchexcel.com/macros-vba...
2. Excel Campus Elevate Program: go.launchexcel.com/excel-camp...
3. My Online Training Hub Dashboards: go.launchexcel.com/moth-dashb...
4. Coursera Excel Specialisation: go.launchexcel.com/coursera-e...
5. Coursera Everyday Excel: go.launchexcel.com/coursera-e...
6. Coursera Excel Data Visualisation: go.launchexcel.com/coursera-d...
// Recommended Excel Templates //
If you are looking for pre-built, easy to use spreadsheets, check out Simple Sheets.
go.launchexcel.com/simple-she...
They have Excel templates covering cash flow planning, invoices, expense reports, personal budgets, and more!
// Show Notes //
Power Pivot
Already know how to use Pivot Tables? Why not take yourself to the next level in data analysis with Power Pivot for Excel? Power Pivot greatly enhances your ability to analyse huge data sets and means you can do a lot more in Excel without having to move to a different data analytics package.
I put together this video to show how we can format a Pivot Table values area so that custom number formatting does not get reset every time we add or remove fields.
You can download a sample file here:
www.launchexcel.com/preserve-p...
Here are some highlights for the video:
0:38 - It's quite hard to read the $USD figures when they are in General number format
0:50 - Let's format the values into Currency format
1:10 - That's fantastic we can read the $USD figures much easier now
1:17 - Now let's see how much Sales Tax we paid
1:23 - Oh no we lost the number format
1:58 - So the question is "How do we keep the number format the same even when we change the value field?"
2:12 - Here's my answer (see LaunchExcel article for step-by-step written instructions)
3:20 - One little note: You have got to make sure that your Pivot Table options are set to Preserve Cell Formatting on Update
3:32 - Check out the next great tip! What happens when you want to select the values area when you have lots and lots of values?
4:36 - Step-by-step instructions for selecting the values area
If you want to learn more about Pivot Tables head over to my website at www.launchexcel.com
Cheers,
Victor
P.S. The article and sample workbook can be found at:
www.launchexcel.com/preserve-p...

[@KL467KL]

This is great. Thank you!

[@hughseagraves7036]

This is great, thank you. The good news is that it appears that if you add data vertically or horizontally to the source data (new rows or new columns), the formatting is copied (i.e., the new data in the pivot table gets the same format as the existing data).

[@dadam697]

great i was looking for this number format so long time, but u have the best answer thanks

[@Raghavarallapalli85]

Hey , How about we need to multiple number format to be preserved, like the KG sold in normal and TAX in $ format

[@aidynyespek3214]

Great! How to roundup numbers in Pivot Table? I have percentage numbers. I tried it with calculated field, but no result.