Learning To Use Cell Formatting In Mac Numbers

The cell format in Numbers determines how the value in the cell is displayed. You can set the number of decimal places, add thousands separators, control how negative numbers are displayed and more. You can also format dates and durations. With custom cell formats you can take complete control and even change the format based on the value of the cell.
You can also watch this video at YouTube (but with ads).
Video Transcript: Hi, this is Gary with MacMost.com. Today let's take a look at cell formatting in Numbers. 
MacMost is brought to you thanks to a great group of more than 700 supporters. Go to MacMost.com/patreon. There you can read more about the Patreon Campaign. Join us and get exclusive content and course discounts.
Every cell in a Numbers table has a value. How that value is shown depends on the cell formatting. There are various different types. So here I have some example cells. If I select any one of them and go to Format, Cell I have it set to Data Format, Automatic. It tries to look at the data in the cell and automatically format it. So a number here is just shown as a number. A date is shown as a date. You could always look at the bottom left here to see the actual value. I could see here that this is 20 and this is August 19th, this is August 19th at nine o'clock, this is point five, this is six d which means six days. It's a duration. If it was text then it would show up as text instead of a value.
Instead of using Automatic you can customize the format here. So let's click on Data Format and let's say we want this formatted as Number. This won't seem to make any change. But now we have options here. We can have a number of decimals. So it's set to Automatic but I could say I want to force two decimals at all times. I could also do the same thing with a number like this and set that to number and force it to zero decimals and you could see it rounds that to 6 even though the actual value shown down here is 5.7.
You could also use this pull-down here to display negative values in different ways. So here a negative value is with a dash. You can have a negative value as red or in parentheses or parentheses and red. You can also add a thousand separator. That's definitely something I like to do in numbers like this. You switch to Number Format, add a thousand separator. You get a comma there, at least in the United States, between every three digits.
Now you can also set the format, instead of to Number, to Currency. You have similar options here in terms of decimals and how negatives are represented. You also have the thousand separator. But you can also select a Currency and change it to something. So you can have dollars or Euros for instance. You can switch to an Accounting style which puts the currency symbol all the way to the left.
Now when you use Percentage what it will do is it will take a number like 0.5 and show it as 50%. This could be a little tricky because a number, say like 20 and you change that to percentage it will show it as 2000%, which is correct because one is equivalent to 100%.
You can also choose to show something as a fraction. So here I have 0.5. Set that to Fraction and you could see it is one slash two. In other words ½. I could set the Accuracy up to one digit, two digits, three digits or just go with things like Halves, Quarters, Eights, etc. So for this I could change this to a Fraction and you could see it says five and seven-tenths. I could switch it to hundredths and it will show me seventy one-hundredths. I can have it go to quarters and its closest to five and three-quarters. 
Now you can also choose Numerical systems although not many people outside of coders have use for this. So I could go to Numerical System for 255 here and say I want this to be base 16 and now it's represented in hexadecimal. You can also use Scientific notation here. So it makes this number eight hundred and seventy million a little easier to read especially if you're recording science data here. Now you can set the number of decimals as well. So you can have many different numbers showing the same number of decimal places.
Formatting also works for things like dates. So let's go and set this, instead of to Automatic, do Date & Time. Now that it's set to that I can choose a certain format here. I could go with some long format like that. Something short. I could include the year or not include the year in many different formats. Or show just the month or just the year. You also can include the time value. Every cell that has a date or time in it has both. So this is August 19th and this says August 19th at 9:00 AM. But if I were to switch this to something with the time it's going to go and put the time in there. It's just going to be 12 AM. In this case I've added an actual time when I typed the value so it's going to put a time value here and I can choose one of these.
Also you have Duration. So you can switch to force this to Duration instead of Automatic. You can use automatic units and you have things like using the letter which is the actual value. So 6d is six days. But I can force it to show 6 space days if I want to see that format. But I can also go to Custom Units here and I can decide exactly what's shown. So, for instance, I can show days and hours and it will show six days and zero hours even if there aren't any hours recorded there. I can add minutes, seconds, etc.Then I can choose a style for that. So I can just have the letter instead of the whole word for each unit.
When you're dealing with numbers if none of these work for you, you can always go to a Custom Format. So choose Create Custom Format. It brings up this whole control here. You can name your custom format. We'll stick with the default name here but with a Number custom format, Date & Time, or Text. So with Number we can format like this and you can drag and drop these things here into the Custom Format. So, for instance, I could add a scale like K. So for this number here I can go and create a custom format and I could say use the standard formatting here, drag up K and now you could see it's 870K. 
But I could click Edit Custom Format and let's change that to M for millions and you could see 870M. 
There's a lot more you could do here. I could use actual words, hundreds, thousands, millions instead of C, K, or M. I could add a currency value before this and select the currency value. I could add decimal places right after it. Set the number of digits. For the number itself I click there and there's a lot I could set. For instance,  I could Show or Hide the separator. I could show zeroes for unused digits. I could Add Digits, Remove Digits, Set the number of digits.
I could also Add a Rule. So when you click here to add a rule you can have the format change depending upon the value in the cell. So, for instance, I could say If the number is less than zero then put parentheses around this item here. So now if I put negative in front here you can see it puts those parentheses around there because that's part of my custom format. The rules can be all sorts of things. They can be if it's equal to, if it's less than, if it's greater than. So you can use this as an alternative to conditional highlighting. For instance here I can go and set a custom format to add a rule and say that if it's equal to 42 put some exclamation points after it, like that. Now you're not going to see any change there. But if I change this number to 42 you can see it does that. 
I could easily reuse these custom formats. So let's put a bunch of numbers here and I could select these cells and change the format to Custom Format 1 and you could see the number with 42 has those exclamation points after it.
So play around with Custom Formatting to see all the different things that you could do with Rules.
You could also use Custom Formatting for Dates. So here you see some complex controls where you can control exactly how each element is shown. You can drag and drop these up here. You can add text in-between things. So if I wanted to create a format that was basically year and then dash and then the month and set that up with leading zeroes and a dash and then the day of the month and set that up with leading zeroes I can get a format that looks like that. 
You can even set a Custom Format for text. So I can go and Create Custom Format and then all I can have in here is the actual text itself. But I could put something before it. So I could have some text like that. Put a little space there and you could see it says Name colon space text. But the actual value is just text. I could double click in here and type something else and you could see now it displays with that custom formatting.
So there's a lot you could do with the regular formatting and a lot you could do with Custom Formatting. Go ahead and create a sample numbers document and play around with each one of these and see what they can do so you know how they work the next time you need them.
Learning To Use Cell Formatting In Mac Numbers

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information
