Pivot Table Number Format Used Most Often

Pivot Table Number Format Used Most Often

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:

  1. Which number format do you use most often in pivot tables?
  2. How many decimal places do you usually set?
  3. 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%)

Pivot Table Number Format survey previous results

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.

pivot power premium number formats

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!

pivot power free number formats

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?

number format latest chart

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.

number format results compared

Decimal Places

This Google Forms chart shows the current results for the second question:

  • How many decimal places do you usually set?

decimal places latest results chart

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.

decimal places results compared

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?

1000s separator latest results

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!

1000s separator results compared

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

Pivot Table Number Format

Pivot Table Number Format

__________________________

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.