How To Show Zeros As Empty In Mac Numbers

Often you want to display formula results only if the value isn't zero. The best way to do that is with a Custom Format. You can also use functions like IFERROR to display something other than an error message in some situations.



Check out How To Show Zeros As Empty In Mac Numbers at YouTube for closed captioning and more options.
Video Transcript: 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.Related Subjects: Numbers (132 videos)
Related Video Tutorials:
Creating a Form In Mac Numbers Using a Script ― How To Mail Merge On Mac With Pages, Numbers and a Simple Script ― Sorting By Last Name in Mac Numbers ― Learning To Use Regular Expressions In Numbers