If you’re looking at too many numbers on the screen and they are large enough to be reduced by thousands you can reduce them using a simple custom number format in Google Sheets.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 719,568 | 947,834 | 1,028,012 | 1,075,867 | 1,426,528 |
| 2 | 61,898 | 70,544 | 84,735 | 96,244 | 111,498 |
| 3 | 98,370 | 100,337 | 110,341 | 112,221 | 114,139 |
| 4 | 100,150 | 118,869 | 143,510 | 163,357 | 198,662 |
| 5 | 979,986 | 1,237,584 | 1,366,599 | 1,447,688 | 1,850,817 |
By applying the thousands custom number format you can reduce the strain on the eyes by compressing the numbers representing the units, tens, and hundreds so that the right-most digit is the thousands, like so:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 720 k | 948 k | 1,028 k | 1,076 k | 1,427 k |
| 2 | 62 k | 71 k | 85 k | 96 k | 111 k |
| 3 | 98 k | 100 k | 110 k | 112 k | 114 k |
| 4 | 100 k | 119 k | 144 k | 163 k | 199 k |
| 5 | 980 k | 1,238 k | 1,367 k | 1,448 k | 1,851 k |
"k"
it can help with readability
So what custom number format do you need to apply?
Here is the custom number format I have recently used:
#,##0," k";[Red](#,##0," k");-;@Let’s break up each component and explore more about how this works in case you need to apply additional formatting.
First, you’ll notice that there are four sections, all separated by the semi-colon character
";"
, the first 3 sections are
how
you can define numbers that are positive, negative or zero.
Therefore, looking at the first pattern that defines how positive numbers are represented, we see:
#,##0," k"
What Does
#
Character Mean?
The # character denotes any number, and by inserting a comma between two of these we are instructing Google Sheets to use the comma for each thousands division, some countries prefer using the full-stop as the way to denote the thousands separator.
By inserting a zero at the end of the # characters we are instructing Google Sheets to force this character at the end. Meaning there should be a zero if there is any positive value in that cell.
Finally, we end the string with a comma to denote a thousand separator, but instead of placing more numbers, we insert the representation of those numbers by using the letter
" k"
.
Knowing this, do you think you could tell what the following would do for the negative numbers ?
[Red](#,##0," k")
What Does
[Red]
Mean?
A square bracketed expression at the start of a section denotes a condition. There is a maximum of 2 conditions that can be placed in any one section. You can have a condition based on what this number should be for this representation to apply, and another condition based upon what the colour should be.
For example, we could have also used this instead:
[<-1000][Red](#,##0," k")
This would mean numbers
less than 1000
in this cell would be
Red
and then wrapped in parentheses with a
" k"
at the end of the number (and no thousands section).
The third section just has the character
"-"
.
If the first two sections had conditions and none of those conditions applied then this would be the default condition for everything else.
I could have used
"0"
, or nothing at all if I wanted the section blank, but instead I opted to show something so that there was no confusion the cell did have no value in it.
What Does
@
Character Do?
Finally, the fourth section represents how text is displayed. If this formatting is applied with text in the cell how do you want the text displayed?
By using just the
"@"
symbol we are leaving the text as is, but we could have inserted any type of text we wanted – or even displayed our own custom error message:
#,##0,"k";[Red](#,##0,"k");-;"Text! What is it doing here?"
How Do You Represent Millions Using
"m"
?
If you’ve understood how the custom formatting applies in Google Sheets, then you should be able to translate how the above could similarly apply to displaying millions.
How about this:
#,##0,," m";[Red](#,##0,," m");-;@Notice the slight changes?
Firstly, the obvious edit being the change of letter
"k"
to
"m"
, but did you see the second change: an additional comma placed at the end of the zeroes, so instead of
"#,##0,"
it now is
"#,##0,,"
.
Apart from that everything else is the same as before.
How Do You Show 1 Decimal Place Before The
"m"
?
If rounding to the nearest million is a little too much you can further modify the custom number format to include a decimal place or two if you are finding the formatting obscuring the numbers a little too much, here’s how you would do write the custom number format:
#,##0,,.0" m";[Red](#,##0,,.0" m");-;@
The obvious edit from the previous code being the addition of the
".0"
at the end of the last million separator.
But What About Billions As
(b)
?
Why not, eh? We’re on a roll.
By now you’ve probably got the hang of it, but in case you’re still scratching your heads, here’s how I would represent it:
#,##0,,,.0" b";[Red](#,##0,,,.0" b");-;@
The only changes being the additional billion separators (making it three commas after the
"#,##0"
) and the change to the letter
"b"
.
Did I hear someone say trillions (t)?
#,##0,,,,.0" t";[Red](#,##0,,,,.0" t");-;@Gazillions??
Ok, I’ll stop now. I’m having too much fun!
Summary
The only issue you may have with applying this type of formatting to your cells is if you need to copy the values elsewhere, such as into another spreadsheet the values as represented is what will be copied across – not the actual number .
The custom number formatting allows you to display numbers without having to show their complete number. If you find your series of numbers could be better compressed by using
"k"
to denote thousands, and
"m"
to denote millions (etc) then the technique in adjusting the settings on how these numbers are shown will help.
Finally, the methods shown above help to remove the default position spreadsheet applications do when handling large numbers by using scientific notation .
