Pivot Table Default Number Formatting
When a field is added to the values area the number formatting should default to the number formatting of the field in the source data. Every time a field is added to the values area, the user has to change the number format. It defaults to General, which is ugly. Most of the time the user will change the formatting to something similar to how the number is formatted in the source data. This feature would save users a lot of time.
Thanks for opening this one, Jon. I’m looking forward to seeing lots of votes on this to boost the priority of this great ask.
ash [Microsoft Excel]
when clicking on a new sheet to create a new pivot table in the same workbook, it would be great if the number formatting I set up in the format cell dialog box on sheet 1 pivot table 1 carried right over seamlessly to sheet 2 pivot table 2. So when my users create all of their new pivot tables they all have the same number format every time. This is a usability issue I would like to see remedied :)
Tony Smith commented
Please add default number formatting it would be a great help for accountants and analysts.
Apparently bringing this one back to life after a year. I agree with most of the earlier comments except for Anonymous'. It would already be really nice to be able to format all sum values in one step.
General format is awful. I am constantly click through to change to Numbers, have commas, no decimals FOR EVERY FIELD. And can we default to SUM and not Count even if the data looks like text?!?!
Frank Rowan commented
I totally agree with comments Jaclyn Newman posted. " I always want it to sum, have commas, and no decimals. On the rare occasion that is not what I want, I am fine to go through the clicks to edit it.".
Hans Peter Grieg commented
I don't agree. The numbering format in the source data might be a mixture of things that confuse the reader. But everybody knows the Genral format.
If you right-click on the field and then chose, "value field settings" or "field settings", and then, "Number Format", you should be able to set the format you wish. Then, when you add or move a new field, it won't default back to the "General" format. However, this doesn't change the default for all pivot tables, just this one.
Jon Acampora commented
Thanks Ash! I'll try to rally some votes. :-)
It is remarkable that after how many years that Pivot Tables have been available (30 years?) that Microsoft still has not addressed this issue. It is relevant to every Pivot Table ever created by every user since day 1.
Jaclyn Newman commented
I would like to be able to set default number formats for every pivot table I create. For example, I always want it to sum, have commas, and no decimals. On the rare occasion that is not what I want, I am fine to go through the clicks to edit it.
Laura Flores commented
As someone who creates a lot of pivot tables, this would make my like so much easier.