- [Narrator] All right,
today, we're going to be talking about
custom number formatting in Google Sheets.
(upbeat music)
All right, so here we have a simple example
to get us started.
And let's just say you wanna control
how these numbers are being shown,
and this isn't going to change the actual data itself,
it's just going to change how the user sees it.
And you would do this through, up in the menu,
go to format, go to number.
And then the first thing you should do while you're here,
is check to see if there is a preset option
that already has what you want.
Because the last thing that you wanna do
is just reinvent the wheel
and make custom format when you don't need to.
We'll just say that we want to have a 100 separator in here
in two decimal places.
All right, so at least that lines these numbers up
to make them easier to look at.
But let's control things
in a little bit more of a custom way.
All right, so let's go back to the custom formatting,
go to more formats and custom number format.
All right, so this is the box
that pops up with all the possibilities,
and it's a series of four different values.
So the first thing that we'll do,
well, say we want to show this number
with no embellishments, right?
So no commas, no periods.
And you do that just by typing a zero.
And you use a semi-colon to control
how we're going to show the negative numbers.
So let's say the negative numbers.
Yeah, we wanna put a parenthesis around those, right?
So just do a zero in the parentheses,
and we'll get into more of what that means in a little bit.
The third value you can control.
And when you look here,
this is just putting examples
of what it's going to look like.
Third value is zero.
So let's just make that a dash.
You have to surround the dash with quotes
to tell Google Sheets
that you're putting in a string and not a number,
because it's not the actual number zero anymore.
And the fourth one would be what to do if it's just text.
So we'll give the user a little hint here and just say,
that's not a number, and we'll close that off with a quote.
And there, the four things that you can specify
for number formatting.
And the example on the left,
if you notice, our example purposely is in the same order
as a positive, a negative, a zero and a string of text.
So we'll apply this here and take a look.
And it's pretty simple, right?
There's no commas, there's no decimals.
Here's a little dash showing a zero.
And then a little hint of, it's not a number.
So I typed in 11, but this spreadsheet
doesn't translate that into an actual value.
That's one more than 10, right?
But it's just a string of letters that spreadsheet.
All right, so, let's move on
to a balance sheet that we made.
So this is the raw data for the balance sheet.
And then this is after we formatted it
to make it more readable.
And we'll talk about what we're doing here.
And the first thing that we're going to do is,
let's say we're presenting this
to people who don't care about the pennies, right?
So we're going to cut off the decimal points,
just start at the first dollar.
And I can just show you this right here,
just do it the easy way, go up to the menu
and click on decrease decimal point twice.
And the next thing that we're going to do
is we're going to make it more obvious
that there are subtotals in here.
This is actually not custom number formatting,
it's a bottom border, but I won't tell anyone.
So we'll go up here, apply a bottom border.
And I applied a single bottom border
to what I would call the subtotals,
and a double bottom border to the grand totals.
So there's two values in here
which are the end of what's being added up.
And those get a double bottom border.
It kinda tells your eye that it's the end of the story
as far as that column of numbers goes.
All right, so let's hold down the control key
and select these three cells.
And we'll go up to the menu.
And for these,
we're going to do another custom number format,
and we want dollar signs to show on them.
We want the dollar sign shifted to the left.
And in this case, we did that by having the dollar sign,
and then the asterix with a space after it.
And not saying, repeat that space
until you run into the number.
So it does a dollar sign, empty space, and then the number.
The next trick that we've done here is,
when you look at the numbers, you should see them
behind the window right now,
we've aligned them on the right,
even though only the negative numbers
have parentheses, okay?
So we've done this, if you come over to the rule
by inserting a space, which is an underscore,
and then a parentheses for the positive number.
So that shifts it over one space so that everything aligns.
Remember the number on the left is the positive number,
and the number on the right is the negative number.
So at this point we don't have any rule for zeros or text.
Just everything after the positive numbers,
and optional rule.
So you don't have to specify them
if you're just dealing with positive and negative numbers.
You can be done right now.
And before we move on from this balance sheet,
found a little straggler down here
that doesn't have the right formatting,
has low currency symbol on it still.
And what you can do to save time is that you can find a cell
where you like the formatting, and you go up
and you use the paint format option,
and just come down and left click on the cell
where you want to apply the format from the source.
In that case, it was G12.
So that fixes that,
and that's a pretty readable balance sheet.
All right, so we'll move on to the next example.
All right, in this example, we're going to go through,
let's say we have some data,
and we're just showing some changes in it.
So this is 2020, this is 2021,
and we want to illustrate what's going on.
So the first thing that we're going to do,
is let's say we want to add some color into this.
So it's pretty easy.
We'll go into the custom number format
and we'll go to the end of the first rule,
and let's make the positives.
So you don't see any semi-colons.
So for the positive, that this role we'll make it green.
So anything that you surround in these square brackets,
you just type the name of the color.
There's only a few colors,
but I know one of them is green, so that works.
And let's just snag the same format here,
copy, paste it for the negatives.
And we'll make that red.
And I need to come and change this comma to a semicolon,
and there we go.
And if there's a zero in this case,
I say do as third semi-colon,
and let's make that show a dash instead of a zero.
And so we'll just put that as a string in quotes
because you always have to tell a spreadsheet
if it's a string and not a number.
And let's also just make sure that that shows in black.
So we'll type that out here and we'll click apply.
And these numbers are telling you a little bit more
about the data right now.
Green is an increase and red is a decrease.
And actually seeing that somehow manage
not to put any negative sign, so that's not very helpful.
So let's go in and change the rule for the negatives,
and just put in a little dash for a negative.
And a little preview there is kinda helpful.
All right, so we'll look,
and now there's negatives for the red numbers.
And now we wanna put in percentages,
and we're just going to do them in column E.
We talked about earlier,
just do it through these preset options
if there's the right one.
It's going to save you a lot of time.
And this one, put decimals on the end, right?
But that's not bad, but we lost our colors,
but that's not also.
Hold on a second,
we're going to grab this special character
that I have to the right.
So I'm using windows right now,
and I got that through something called the character map.
So if you hit your Windows key and type character,
this will come up on the screen,
and you scroll through these,
use any of the ones that you want.
I picked these arrows, left clicked on them,
pressed select, and then copied that onto my clipboard,
pasted it into the cell.
So we're going to grab that.
We'll go back into custom number formatting.
So leave that as it is and pin the color back onto it.
And so let's have some fun now,
and put in quotes because this is a string,
and paste that special character in the quotes.
And so that looks pretty nice, right?
And now we have to do the negative one.
So remember this, do a semicolon.
And I'm actually just going to copy this
and paste it and put a negative
and change some of the things around so that it shows right.
But I did see one mistake already
is I have to grab the up arrow
for the greens or the positive changes.
We'll go back in,
we'll paste the up arrow where it belongs.
There we go.
Up for positive, down for negative.
And we'll just say, oh, poor zero,
no one's paying attention to zero.
Let's just make him a black and dash, okay?
So it shows up, click apply.
And there it is down at the bottom.
It looks okay now.
We'll get rid of those two special characters.
And now you have something pretty cool looking, right?
So now these numbers just tell them we're clear story.
It's obvious that the green is positive,
the red is negative,
and the arrows are showing you that direction as well.
So in this next video,
we'll talk about conditional formatting,
which is a very different workflow
but it can also be used to make the numbers
tell a different visual story
without changing the values underneath.
It was nice to have you along in this video.
Thanks for watching, and I'll see you in that next one.