Including a Plus Symbol Before Values In Mac Numbers

Hi, this is Gary with MacMost.com. Today let me show you how to customize how values will appear in cells in Numbers. Specifically how to add a Plus sign for positive values.

MacMost is brought to you thanks to a great group of more than 700 supporters. Go to MacMost.com/patreon. There you could read more about the Patreon Campaign. Join us and get exclusive content and course discounts.

Here's the situation. I've got two columns of numbers. Here I've got the sum of the two columns. The formula here is simply just adding these two values together. Now if the result is negative I get a negative sign. But if the result is positive I get nothing. Let's say you have a need to put a Plus symbol before positive numbers. How would you do that?

Well, one way to do that is using formulas. But it gets pretty complex. So let's go and edit this formula here. I'm going to use an IF statement and I'm going to test what happens if we take this value plus this value is it greater than zero. If it is then what we'll do is start with a plus symbol in quotes. Then we'll use an ampersand and that appends something to this. So I have a string that has a plus symbol. I'll append to that, in parentheses, the same addition so the result is there. So what you get is the plus symbol plus the numbers added together.

Now if that's false then what we'll do is just have the numbers added together. So it would just look normal except it already has a minus sign in front of it. Won't have to worry about it. So now we look here and we see we get +15. If I were to copy that formula and paste it into all of these slots you could see I get plus symbol before the positive numbers and a negative symbol before the negative ones. 

Because I've created a string here by adding a character to a number it's actually going to format it differently. So, I'm going to select all of the cells here, I can do that by just double clicking on D here at the top, and I'm going to change the Text justification to be right justified. So now they look right. They're all right justified just like numbers would be.

Now you may think well this is a series of characters here so if I need to use another calculation this is not going to work. After all it's the plus symbol and the number. But Numbers is smart enough to figure that out. So if I were to put another number there and then try to create a formula where it was this plus this, it's going to convert plus 15 to number 15 and you can see it works right. So even though I've done a trick here to get that plus symbol I can still continue to use this in future formulas.

Now I could do the same kind of thing here if I'm doing something more complex like a SUM. Normally I would do SUM and then, for this entire column here, just click on B and there's 25. If I copy and paste that and this will get me the SUM of C and you can see it's negative 3. I want to do the same thing where I have a positive sign in front of this one. So what I could do here is the same exact thing except that instead of adding two cells I'm going to use SUM of B. So, IF and then do SUM of B is greater than zero then the Plus symbol, ampersand and the SUM of B. Otherwise just the SUM of B. Then close the parentheses on the IF statement there and you could see I get +25. If I were to copy and paste that here you could see I get the same thing. It's just giving me the SUM of C not the plus symbol and the SUM of C. So I would want to go in here and set the justification to right justified. Just like before.

So this works but it's not the best way to do it. So let's Reset. Now I'm back to the original formulas here on the right. Just a simple this plus that. Very simple and it's the same all the way down here. Here I've just got a simple sum of B and sum of C. But I still want those plus symbols to the left of the number. I can do that using cell formatting. 

So let's select all the cells I want to do this for. I can Shift select this and I can hold the Command key down and select these two. So now I've selected all those cells. Let's go to Cell and then change the data format from automatic to Create Custom Format. Now we get to work with this Custom Format. Let's call it something like Plus Symbol to remember what it is. It's going to be for Type: Number and it's going to use this regular number custom format. If I were to say OK here I would get exactly the same results that I see here. 

But I'm going to Add a Rule. Click here to Add a Rule and say If the number is greater than zero then I'm going to use this formatting. Now these two are the same. Basic number formatting. I could add a character here to the beginning. So I'm going to place my cursor before and add a Plus symbol. I could add anything I want there. I could add a word there. Now I can hit OK. Now you could see it has added the Plus symbol only in cases where the number is greater than zero. Otherwise it's just using standard number formatting for if the value is less than zero or zero itself. 

So it quickly and easily works there and I don't have to mess with the formulas at all. As a matter of fact I could very easily now reapply this Plus Symbol format to anything else I need.