Hi, this is Gary with MacMost.com. Let me show you how to display
zeros as blank cells in Numbers and other formatting tricks.
MacMost is brought to you thanks to a great group of more than 1000 supporters. Go to
MacMost.com/patreon. There you can read more about the Patreon Campaign. Join us
and get exclusive content and course discounts.
So this is actually a pretty common question I get. Sometimes in a Numbers spreadsheet you want
to have zero values presented as a blank. So maybe a calculation is done or an IF statement
is used and the result is zero. But instead of the number 0 there you just want the cell to
be blank but still perform fine in calculations. Let me show you an example. Let's say you have a
spreadsheet like this. You want to have a charge applied but only to prices that are above $10. So
you could do a formula like this. You could use IF and then click on the cell with the price in it.
Is greater than or let's say equal to ten. Then we're going to have a 20% charge, so point 2
times the price. Otherwise we'll have zero. Now if we apply this to all the cells here we see
that these two, since they are under ten dollars, have the zero there.
But what we'd rather have is a blank space. So one way to do that would be to use something else
in the IF statement. So here in the IF statement instead of zero we could do just a set of double
quotes " " like that. That would put blank text there rather than a number. So I'll paste this in.
That seems to work. But there's a problem with that. Let's say for some reason in column C we
want to multiply that value by 7. So 7 times the charge there, so you're performing a calculation
on the charge, if I paste that in here we're going to get errors here. Why? Because there's no number
there. There is just this blank text string. We want there to be a zero there so it multiplies
correctly or does any other calculation just fine. But we still want it to look like a blank space.
So let's go back and change this to be a zero here and I will paste it in and now we have
zeros there again. So how can we get the zeros to look like blank spaces. We'll do that using Data
Formatting. So here we have it set to automatic for these cells. I'm going to double click on
B there so I select all the cells in Column B. Now instead of automatic, automatic is
picking up the currency format because we're using the currency values that are here,
we're going to change it to Create Custom Format. We're going to call this Currency for
Blank. We're going to put a currency format here. So we want to use a standard number like
that. But let's also drag the currency symbol beforehand and afterwards let's put the decimal
amount. I'm going to select that and say Yes, Show Trailing Zeros and Number of Digits is 2.
So it would always have two trailing zeros. You could see example right here.
So that looks good. But we're also going to add one rule. The rule is if it's equal to zero
then we're going to display nothing. We're going to delete what's there. So nothing here at all
is going to be displayed. So I'll use Okay. Now we can see it applies to all those cells
and these values here it's still the formula you could see but since the result is a zero
it's giving us a blank cell. The actual value in this cell is going to be zero but what it displays
is going to be blank because we have used that Custom Format here and there's nothing there.
Now if we wanted to we could edit the Custom Format and have two dashes there rather than
blank. You could see now it display two dashes. Either way the value here is going to be zero,
the result of this function. So, if we were now to multiply by 7, like that, and copy and paste
it throughout we can see it actually just like the currency carries over with the formatting.
If we were to change the formatting and force it to be currency here we could see we get
zero dollars. So it is actually picking up that zero, 7 times zero is zero and that's the result.
So this is how you get a blank or some sort of symbol
as the displayed thing inside of a cell if the value is zero. You could also use,
you know, if less than or equal to zero or something like that.
Now what about errors? You commonly run into this problem when we have errors. Like,
for instance, a bunch of values here and let's say we're going to do division
and here's a zero. You could see what's going to happen here. If I do 7 divided by 4 there I
get the result. When I paste this in I get results when there is a denominator that is not zero but,
of course, dividing by zero gives me an error. You may think that one way to deal with that
is to do IF and let's say the value of the denominator is equal to zero then
just put something else there like dashes and quotes else do the division and that will work.
But there's a better way to do it that will handle not just this particular type
of error but any error. That is to use IF error, then you put the calculation in here
and the only other parameters of what to display instead if there's an error. So I could put two
dashes or maybe two exclamation points there. You could see it puts it there. I could also just
keep leave that blank just two quotes there. So it doesn't display that error message there. It just
doesn't do the calculation. Here I could Copy and Paste that throughout so if I added another one
you could see it also puts a blank in there even though it is doing the division. It just
results in an error so it's giving a blank space there rather than the error
which would be the results of the division.
You could get pretty creative with this. For instance here you can go and instead of saying
blank or two dashes you can do all sorts of things. Like I could put the same deal here as
before and just add some extra text like that so now you could see what it does there. So you could
get really clever with the custom of formats here. You can, of course, add more rules if you want. So
have something different if it was less than zero than if it is exactly zero. All sorts of things.
A lot of people overlook Custom Formats and instead try to use Conditional Highlighting
or really complex IF functions. But there's really a lot you could do
with Custom Formats and if you use Numbers a lot you should get to know them.