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.