10 Useful Numbers Cell Formatting Examples

Often the solution to a problem in Numbers isn't to use a formula, but to use custom cell formatting. See some practical examples such as adding units, abbreviating large numbers, replacing 0 values with blanks, showing hexadecimal numbers and much more.

You can also watch this video at YouTube (but with ads).

Video Transcript: Hi, this is Gary with MacMost.com. Today let me show you ten useful examples of using cell formatting in Numbers.
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.
Now I'm often asked questions about how to do something in Numbers using either formulas or perhaps conditional highlighting. However, often the answer is to use neither of those but instead use cell formatting to solve the problem. So it's important to understand how cell formatting works and how you can create custom cell formatting in Numbers to get the most out of it. Let me show you some practical examples.  
So first let's say you have a column of numbers here and they represent something in units like, for instance, distance like meters. So you may have the column heading, Distance, and then in parentheses put meters. You see this a lot. But it's not always convenient to have the unit right there in the column head. Instead you may want to have it right here. But if you've typed, say, 10m that's minutes. 10 meters could be thought of as a piece of text, not a number. So instead you just want to have the regular number here, like this. When you do that now it's not clear what unit this is in. So let's select all of these cells. I'm going to double click on the letter here at the top of the column so I select all of the cells but not including Header cells. Now go to Format Cell and under that there's Data Format and it's set to Automatic. What we want to do is create a custom format. Let's click here and then go to Create Custom Format. Then we'll call our Custom Format Meters. Then we're going to have here the standard number. We can click on that to get some extra things like the Separator, in this case the comma, and if we want a minimum number of digits. We'll just leave all this as default now and it shows us in an example right here, 45 using this as the last number.  We also want decimals here. So I'm going to drag this up. So now we can see it says 45.3.  We could go in and add and remove digits if we want for these decimals. 
Now we want this to be shown as meters. So, we'll just type some text here. I'm going to add a space and then I could type m for meters or type out the entire word. You could see the example here. So now when I hit OK you could see everything shown in meters. If I select a cell there you could see the actual value is just 10. Just the number 10 or just the number 45.3 but it's going to display with the word meters after it. 
Now another question I've gotten in the past is how do you have the sign in front of the number. For instance, if I have 10 there and then negative 12 and then another number like that I get a sign before the negative number but nothing before the positive number. Usually that's fine. But if you want to put a plus in front of the number you could do that with a custom format as well. Now I'm going to go to Data Format here and Create Custom Format. I'm going to call this one Always Include Sign. I'm going to leave it as typed number. Use the standard format here. I'm not dealing with decimals in the cases of these numbers so I'm not worried about adding the decimal set. But what I'm going to do is add a rule. The rule is if the number is greater than zero then do the same thing except before it I'm going to put the plus symbol. So now you could see here, in the example, +30. So I'll hit OK and you could see I get pluses in front of any number greater than zero. I get the minus. That's already included on any negative number. If I include a zero it's not going to do anything.
Now another request I've had in the past is to represent zeroes as blanks. Sometimes you get a data set that has lots of zeroes and only has numbers where that number applies. So you want to have the number zero there. That's got to be a value. But you don't want it to show zero. You just want it to be blank. So we can Create a Custom Data Formula for that. Call it Blank Zero. We'll use the Standard format here. Maybe let's add decimals just in case we want to have a decimal number there. Let's add a rule and say if it's equal to zero then display nothing at all. Now we can see we get that. If I select a cell you could see the actual value is zero. But the display is showing nothing. We can also edit that Custom Format and have it be something else. So, maybe instead of zero we can do two dashes like that. Now you could see two dashes are put there anytime the value is zero. 
You can also use these conditions to check for a value range. So, for instance, say I've got a set of values here. Anything over a hundred or below zero isn't valid. So I want to indicate that here instead of showing the number. What I can do is Create a Custom Format and in that format I will add a rule and say if it's less than zero then display something like TO LOW and add another rule and say if it's greater than one hundred then also put something like that. So now you could see TO LOW and TO HIGH are displayed whenever the value is too low or too high. If I change this one to 101 you could see it changes to that. The value is still there it's just displaying now as this instead of the number. 
Now sometimes you have large numbers like these and instead of displaying all the zeroes you want to use little abbreviations like K or M to represent thousands or millions. You could do that using a Custom Format as well. So Create Custom Format. Let's just call this one K. You see I've got the number there just as before but I could drag over Scale. With Scale I can click here and I can choose C for hundreds, K for thousands, and M for millions, B for billions, T for trillions. Or the word itself. Thousands, millions, billions, etc. I'm going to stick with K right here and what will happen is this will affect the number in front of it. So now I could see it puts K after it. The value is still the regular value. If I click on this you could see it says 506,000. If I select this you could see it still says 45,500. So it remembers its actual value. It's just going to display it differently.  
Now let's say I had a number like two million, like that. I want to display a M whenever the number was high enough. So I could do that. Let's Edit this Custom Format, add a rule, and say if the value is greater than or equal to one million then we're going to put the millions, the M there. Now you could see it's K for everything below a million. So 999,999. It's going to round it to a K. So what we probably want to do is actually alter that and say is greater than or equal to 999,500 then display it like that. So now that's K and that's M. If we have a fractional M we could see it shows it rounded. So, let's alter it again. Edit the format here and say for millions let's Add a decimal and let's allow, let's say, one decimal place like that. For K let's do the same thing. Let's allow one decimal place. Now we can see we get 45.5K  and 6.5 million.
Now let's say something with currency value. It's pretty easy to do this. So if, for instance, I have a hundred and I want to show this in dollars, I can go to Data Format, say Currency, and there's a hundred dollars. I can change the currency type right here. So I could change it to a hundred Euros. It's extra handy to do that when you have formula results. So let me Undo here so I'm at a hundred dollars. Let's say I want to convert that to Euros. So what I could do is say Equals this amount here Times (and I'll use the currency function there). Currency 1 is going to be US dollars. Currency 2 is going to be Euros. I'll stick with the default price there. So that will actually multiply this by the currency conversion. But you can see I just get a number there. So what I do is go to currency for the Data Format and change that to Euros to match the result that I know I'm going to get. 
Durations are another place where you may want to customize the format. For instance, if I were to type in 100h for one hundred hours you could see I get 4 days, 4 hours. But I want it to stay as hours. So what I'll do is I'll change the data format. You could see here it's automatic but let's go and force it to duration. Then I could set it to Automatic units and the style is either just the number, the number and a letter like W for weeks, or the number and a word. I could go to Custom Units and here I could specify exactly what I want. So I could, for instance, just say I only want hours and then I'm going to get one hundred hours there. No matter how many days or weeks it takes up. I could still change the style here to include the entire word if I want. If I wanted to include minutes all the time I could select minutes as well or deselect hours and I'm always going to get the duration in minutes. 
Now in some situations instead of dealing with the date as the month, day, and year you want to deal with the day number for that year. In other words January 1st is 1 and December 31st is 365. You could do that with a Data Format but not under the Date and Time section here. This is just going to give you different ways to show the month, day, year. But if you go to Create Custom Format and you switch the type of date and time. It should be there automatically because it recognizes this date. Then you could set this to day and year. Let's go and remove the month and the day here. Get rid of the comma here at the beginning too. Instead add day of year. So let's put it afterwards. In-between it let's put something like a dash. Now you could see this will be the 171st day of 2021. If I set all of this to day and year I could type a regular date, like let's say July 1, 2021 and the formatting is going to change that to 182. The actual is the date and time at midnight like that. But what's displayed is going to be in the format that we want. I could Edit the Custom Format, maybe, and change it to something like year colon space and then a space and day colon. Like that. You could see it changes the format to look like that. So you have a lot of different options here.
Sometimes you have just text like this and you want to format it in a special way. Like maybe add parentheses around it or something like that. So you could do that in Create Custom Format as well. Let's put this one called Brackets Around Text. The Type is text of course. There's nothing you could do to this. But you can go and put square brackets around it. Put something before it and after it. You know, like Name colon. That kind of thing. You could see the example here. Let's just do the square brackets. You can see I've added that. Let's add that to the entire column here. So Brackets Around Text. Now whatever I type in here gets those square brackets around it. You could see the actual value doesn't have those. It's just how we view it. 
Now you can convert between bases. One example of this is to convert a decimal number to a hexadecimal value. So, for instance, if I wanted to do colors here I might have red, green, and blue. I may have say 127, 127, 63 as the values here and I could get the actual color number by taking this multiplying it by 2 to the power of 16 plus this times 2 to the power of 8 plus this multiplied by nothing. That's the actual color number. But normally you wouldn't see it in decimal. You would see it in hexadecimal. That's how you work with colors if you're doing HTML development or app development or anything like that. So you would want to represent this as hexadecimal. You could go to Data Format and select Numeric System. Here you could change it to base 16 and it will show you the base 16 version of this. You can even set it to the number of places. So in this case I want it always to be six. So this is the result here. If I were to say do zero and then 255 and then 255 you could see that's the value for that color. 
So I've got one bonus one for you. Now sometimes you may want to use fractions instead of decimal numbers. Like, for instance, measuring pieces of wood or lengths of rope. Sometimes it makes sense to show halves or quarters. So in this case let's select all the cells here and I could go and just select Fraction. You could see I get fractional values there. I could set the Accuracy. So I could set it up to one digit, 7/8ths. Two digits 23/24, etc. I could do halves, quarters, eighths and force it into that.  I could also do a Custom Format and move the decimal there and then under Decimal I could say Fractions instead. For Fractions I could set those same values here. So let's have it go up to one digit like that up to eighths and then here I could add something additional. So for instance if this is inches I could do in like that.
So there's a look at a whole bunch of different ways to use cell formatting, Custom Cell Formatting, to get things done in Numbers. You can alter these techniques to fit your needs, combine some of the ideas as well and hopefully it will help you make your spreadsheets that much better. Thanks for watching.

Comments: 11 Responses to “10 Useful Numbers Cell Formatting Examples”

John Russell

3 years ago

This was a super helpful video. As is often the case, I now desperately need to search my life for cool uses for the information. Keep'em coming!

Bart Pulverman

I recently migrated from Excel and Word to Numbers and Pages and this video explained several functions that make the transition easier.

Do you have any videos that are specifically designed for people who are comfortable in Excel and Word, but need to learn how to work with their old documents once they are imported into Numbers and Pages?

Gary Rosenzweig

Bart: I don't have anything that specifically deals with that. I would imagine what you need greatly depends on the types of documents you have and your experience with each app. Just explore and experiment with test documents to figure things out.

Eric

I noticed in some comments that people had pointed out the use of "to" vs "too". In a similar vein, and again not to detract from the really useful content of the video, I'm pretty sure that the measure of distance is "metre" and not "meter" (even in American English).

Eric: In American English is it "meter."

Jean-Claude

Thanks Gary!

Margaret Smith

I am trying to enter a column with postcodes. In our area they begin with a zero e.g.0920 My cells will not accept the zero and covert to 920. Help please.

Margaret: Either set the format of the cells to "Text" before entering in the data, or set a custom format for 4 digits, no separator, with "Show Zeros For Unused Digits."

Thanks Gary. All fixed.

Peter

Hello Gary, can I add text to a cell which has the result of a calculation? For example, if I'm calculating a sum of prices. I'd like the cell to show the result $230 for example, but I'd like text after the amount, so $230 sales for example. Is this possible?

Peter: Yes. You can do that with a custom format. See around 11:20.

Comments Closed.

10 Useful Numbers Cell Formatting Examples

Comments: 11 Responses to “10 Useful Numbers Cell Formatting Examples”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information