After you add number fields to a pivot table’s Values area, you can format those numbers. To find out which number formats are used most often, I did a 3-question survey. Thanks to everyone who replied, and these charts show the results of that survey, compiled on two different dates.
Pivot Table Number Format Survey
Way back in September 2013, I was making updates to my Pivot Power Premium add-in. It makes number formatting easier, and I wanted to find out which number format settings are most popular in pivot tables.
So, I asked the readers of this Pivot Table blog to answer 3 quick survey questions:
- Which number format do you use most often in pivot tables?
- How many decimal places do you usually set?
- If you use Number Format, do you usually include the 1000s separator?
Early Results
A week after the survey started, I published the results. At that time, there were 84 responses
- Number format was the clear favourite, with 61 votes (73%)
- For decimal places, zero and 2 were almost tied, at 38 (45%) and 37 (44%)
- And, almost everyone used the 1000s separator, with 71 votes (85%)
Add-in Updates
After posting those survey results, I finished the changes on my Pivot Power Premium add-in. In the Number Formatting section, the Number formats are featured at the top of the list.
I’ve added an option to use the number format from the source data too. Both Bill Jelen and Jon Acampora mentioned that feature in the result post comments.
There’s a free version of the add-in too – Pivot Power Free. In that version, you can quickly apply the Number format with zero or 2 decimal places. Or, use the Percent format!
More Results
Recently, I found that long-ago survey post. The survey was still open in Google Forms! The current charts showed that it’s up to 142 responses now.
We’re all about numbers, here at Pivot Table Blog, so I had to see if the results had changed.
Let’s build some new pivot tables!
Top Number Formats
This Google Forms chart shows the current results for the first question:
- Which number format do you use most often in pivot tables?
I copied the response data into Excel, and built a couple of pivot tables.
- At the left, you can see the results that I posted back in October 2013.
- At the right, the pivot table shows all the responses, received so far
In the data, there are only 140 records, so I’m not sure what happened to 2 of the records. Maybe I deleted them, long ago, for reasons that I can’t remember!
Anyway, as you can see, the percentages are almost identical, with the Number format way ahead of the others.
Decimal Places
This Google Forms chart shows the current results for the second question:
- How many decimal places do you usually set?
And here are the pivot tables for the Decimal Places question:
- At the left, you can see the results that I posted back in October 2013.
- At the right, the pivot table shows all the responses, received so far
This time, “2 decimal places” has moved into the lead, and has 48% of the responses. The Zero option is second now, and has fallen to 42%.
It’s still a pretty even split though.
1000s Separator
And finally, this Google Forms chart shows the current results for the third question:
- If you use Number Format, do you usually include the 1000s separator?
And here are the pivot tables for the 1000s Separator question:
- At the left, you can see the results that I posted back in October 2013.
- At the right, the pivot table shows all the responses, received so far
This time, the results are almost identical to the first results, with only a 1% change in each of the responses.
And I voted Yes too, because big numbers are much easier to read with those separators in them!
No More Results
Don’t worry – I’ve closed down the Pivot Table Number Format survey, so there won’t be any more analysis posts! But, if you’d like to see the results from a different pivot table survey, that I did on my Contextures Blog, take a look at it here.
You can download the Excel file with the responses, and the pivot tables, from the Excel Sample Files page on my Contextures site. In the Pivot Table section, look for PT0042 – Results from Pivot Table Survey.
________________________
Pivot Table Number Format Used Most Often
__________________________