Understanding Cell Value Types In Mac Numbers

Anyone using Numbers should have a good understanding of different cell value types, such as numbers, text, dates, durations and boolean values.
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 close look at value types in Numbers. 
MacMost is brought to you thanks to a great group of more than 600 supporters. Go to MacMost.com/patreon. There you could read more about the Patreon Campaign. Join us and get exclusive content and course discounts. 
So if you work with Mac Numbers a lot it's important to understand the different value types that can be in cells. For instance you could have numbers, you could have text, you could have dates. Let's take a look at all of them and how to properly use them. So here I have a blank table in Numbers. I'm going to enter something simple into this first cell. Just a number. This is the most basic value type in numbers, a number itself. Now when I select it I want you to pay careful attention to what you see at the bottom left corner. See it here. It says Actual. It's very important to always pay attention to what you see in the bottom left hand corner.
This will give you a clue as to what Numbers thinks is in the cell. Whenever you have a number, no matter what type of format it is, whether it's a simple number like 7, a large number like this, a number with a decimal point like that, it will always say Actual. Then to the right of the word Actual you'll see the value itself. 
Numbers can be formatted all sorts of different ways. If I go to Format, Cell on the right I can change the Data Format. Automatic means that Numbers if going to take its best guess as to what is in the cell. Most of the time that's all you need. But you can set it specifically to something. For instance I can choose Number and choose all sorts of things like the number of decimal places, whether to include a thousand separator, things like that. I could also say this is actually currency. You can see it will automatically give me two decimals places and put a dollar sign in front of it. I could also say percentage. In this case 1% is the equivalent to point zero one. Number 1 would be 100%. In this case the number 7 is 700%
There are other types of number formats as well. But notice no matter what I switch to, like here with percentage, it still says the Actual value is 7. If I switch to currency the actual value is 7 but it gives you the dollar sign in front of that to indicate currency.
Let's look at a different Data Format, another one that you will commonly use. This is Text. This is when you type words into the cell. So when you type something in you'll see here with it selected it will say Text, not actual but text. You'll see the text there. Now Text can't be used for calculations. You can't add pieces of text together and you can't add a number to text. You commonly use things like text for sorting, filtering, and comparisons. When you want to go and indicate that a row, which usually represents a record, has some sort of text information like if each row represented a person the text would be the person's name or perhaps the name of the department they work in.
Now another type of data is date and time. So you could do something like type in 5/30 with a slash in-between and it will recognize that it's a date format and automatically format it for date. If you look at Actual down there and it now says 5/30/2020. So it assumes the current year if you don't include the year. Now date and time is the same thing. So if I were to go down here and type 5/30 and 5:00 like that and hit return you could see here it says Actual is 5/30/2020 at 5:00:00 AM. You could format cells with the date right here and you could change the date to all sorts of formats, the time to all sorts of formats, or None to not include the time there. 
Now another format type is Duration. So duration is a length of time. So, for instance, five minutes. I could type 5 minutes and you could see it converts it to the duration format. Now Numbers formats are just numbers. Date formats we're familiar with. You could have a slashes between the day and the month or you could write it out like May 5, that kind of thing. But a lot of people aren't familiar with how durations are written out. They're typically written out with letters. So if I select this here and go to Cell, Format, and Duration you could see I could go to Custom Units and I could choose to include the weeks, days, hours, minutes, seconds, and milliseconds. That gives you a complete duration with 0w, 0d, 0h, 5m for 5 minutes, 0s for seconds, 0ms for milliseconds or thousands of a second. If you leave it on Automatic it will just include what's proper. So I can put it like that. If I were to type something, like say, 5.2d, 5point two days, and hit return you can see it converts that to 5d 4h 48m which is five and two tenth's of a day.
Now you can perform calculations on Dates and on Durations. With dates you can add or subtract durations. So here with this date I can go and create a formula that is this date, and let's say, plus five days. I'm going to put it in quotes because otherwise Numbers will confuse it with a cell location. So five days and you can see it works. I can subtract five days as well and you can see that works too. You can add durations together. So, for instance, these two durations here I could create a formula that adds one to the other and that a will work. But I can't add two dates together. That doesn't make any sense. I can't multiply a date by something. I can only add or subtract durations to it.
Now there's another type of Data Format call a Boolean. A Boolean is a true or false value. You can actually enter in Boolean values by typing in the word True or False. So if I type true and hit Return you can see it makes it upper case to indicate that it has actually done something with it. If I select it, it says Actual and True there. I can also type False and it will do the same thing. These are not the same as zero and one. True and False are different values and they can be used in formulas to compare things. Or you can use them in IF statements and things like that. 
There is a better way to represent true and false values inside of Numbers. That's to convert from Automatic Data Format to Checkbox. Checkbox is kind of like this special format for Boolean values. So trues will be checked boxes and falses will be unchecked. If I click on that cell there you can see Actual is False, this one Actual is True. So it's up to you whether or not you want to represent them as words True and False or you want to use them as checkboxes. 
Often Boolean values are the results of formulas, not something you type in. So, for instance, if I have a number here like 8 and I have a formula here for this value is greater than seven, then the result is a Boolean value, True. If I were to change this to a six you can see it changes it to False. But in this case you can't use checkboxes. If I converted this to a checkbox it removes the formula. The idea is you can click them to change the value in it and you shouldn't be able to do that if it's the result of a formula. So Boolean values that are results of formulas need to be set to Automatic. But Boolean values that you're going to be able to change manually you can set those to Automatic and enter True or False and Checkbox to be able to check and uncheck that box.
There are some weird cases in Numbers where it treats Boolean values as text. So, for instance, if I type True in here and I want to do a Conditional Highlight here I can add a Conditional Highlighting rule. You can see there is no way to say equal to. If I say equal to and I say True it's not going to accept it. Instead I add the rule for Text and use is, and say text is, and then True. That will make it Bold if it's True. So you can see if I change the value to False it's not Bold. If I change it to True it is. So for Conditional Highlighting you treat True and False as text even though it's really this special Boolean value instead.
Now if we want to be complete there's one more type of value you can have in a cell. We're always using it. That's formulas. When you use a formula the cell kind of has a dual nature. So, for instance, if I put 1 here and 2 there and here I have a formula, taking this cell and adding it to this cell, you could see the result is 3. If I select it it will say not Actual or Text but Formula and will give you the formula here. However, in the cell we see the number 3. That's the dual nature. The actual content of the cell is the formula, E4 + E5 in this case. But what we see in the spreadsheet is the result of the formula, not the formula itself. So this makes it different than all other cell types. There's a value that's hidden in the spreadsheet and the result that's visible in the spreadsheet. But you could always see the formula itself by selecting the cell and looking at the bottom. Of course if I were to double click in it I would go and Edit the cell. I can't edit the result. Instead I'm editing the formula.
So I hope this provides a little bit of a deeper understanding of how Numbers works and helps you to build your spreadsheets.
Understanding Cell Value Types In Mac Numbers

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information
