Hi, my name is Jason Morrell, and in this very short video I'm going to walk you through
a little trick to convert big long numbers into shortened versions.
For example, where you've got something like a million, where it has one with six zeros
that follow it, it might make more sense for you to display that as one 'm', one million.
If you've got a number that goes into the thousands, so something like this, five, seven,
five, six, five, sometimes it makes more sense to display that as is fifty seven point six
'k'.
So the 'm' and the 'k' at the end of each number there are commonly used to represent
thousands and millions.
So how do you create these?
You can't just select the numbers and go to your standard number, formatting options here.
These are too general, and even if you go to your Format Cells box and go to your Number
and Currency options here, there's not enough depth here to get you to this point here.
What you need to do is to actually create a Custom Format here.
We have a list of numbers here that goes from a single digit right through to the multiple
millions down the bottom there.
And what we're going to do it to do a few examples of custom formatting using a mixture
of the 'k' and 'm' suffix.
So let me just copy this across to this second column and we'll work on this second column
here.
The first thing we do is select the range, press Control One (or you can right-click
and Format Cells) - that displays the Format Cells box here, come down to the Custom option
at the bottom and in the Type box, just wipe out whatever is in there.
Let's say, first of all, we want to show everything as millions, so whatever the number is with
an 'm' on the end.
We might have to show a few decimal places here to get the smaller numbers showing up.
Here's how we do it.
So what you do is you create a custom format that says zero point zero zero.
Now that will show a whole number and two decimal places, whatever the number is, followed
by two commas.
Now what the two commas do is it says, only show the detail down at the millions, not
the smaller units.
What you then do is you put a backslash 'm' which represents millions.
Now that's all we need to do there.
And when we click OK it shows everything here as millions, so really the smaller units don't
really show up at this kind of level of detail.
But you'll see down here, we've got a one point five million there and a fifteen million
at the end.
If we'd only done one decimal place or even just a whole number, then only these two numbers
really would show up anything at all.
Let's say you want to come down a unit and display everything as thousands.
So let me just copy this range across again to the next column.
And what I'll do here is I'll represent everything with a 'k' on the end.
So again, go back to my Format Cells box, go to the Custom format here, go to the General
box and remove what's there.
And this time I'm going to say zero point zero zero zero.
And I've allowed three decimal places here just for a bit more detail on the numbers.
You put a single comma to follow that with represents the thousands, so only show the
detail down the thousands, no further, and then you put a 'k' on the end to represent
the thousands and that's all we do, we click OK and everything now is showing as thousands.
So if you look at this example, we have fifteen thousand which is showing as fifteen point
zero zero zero 'k'.
OK, the next one is a hundred and fifty thousand and so on all the way down there.
Now when you get into the millions, obviously that's showing now as fifteen hundred thousand
or fifteen thousand thousand because the numbers are so big.
What you could do if you wanted to is to mix these up, any numbers into the millions show
with an 'm' suffix and any number going into the thousands show with a case of 'k' suffix.
Then maybe anything that's not up to a thousand, so anything that's below a thousand just show
as a regular number.
Let's do one final example.
Let's copy these across to a fresh column and let's manipulate these.
Now what we need to do here is to introduce square brackets.
Now what square brackets do in a custom format is they allow you to create conditional logic.
So what you do inside square brackets is to put your test.
First up I'm going to say anything greater than or equal to one million, close the square
brackets, and that means that whatever follows next will only apply that format to any numbers
over a million.
So what we do is we say zero point one decimal place, comma, comma, and then backslash 'm'
to represent the millions.
What we then do is to put a semi-colon separator, and I'm going to introduce a second test for
the thousands.
That's also in square brackets where I simply say greater than or equal to a thousand, close
the square brackets, and that means whatever follows this bit only applies to those numbers
greater than a thousand, if they haven't already been formatted by the millions.
So then I'll say zero point zero, comma, 'k'.
So that bit covers numbers in the thousands.
I'm then going to put a final semi colon and put a format that covers any other number
that hasn't been covered by the first two.
So for that I might want to put zero point zero.
Zeros just represent mandatory digits, so always show a digit in that position for the
number.
When we click OK there you'll see that these three numbers here, which are below a thousand
just show as per normal.
These three numbers here, which are in the thousands show with a 'k' on the end, and
these last two numbers which were in a millions show with 'm' on the end.
So that's a neat way to shorten your numbers.
I hope that's been useful.
If you like this video, don't forget to give it a thumbs up.
If you want more videos like this, don't forget to subscribe and you'll be notified as soon
as they're published.