Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Formatting the numbers in a table allows you to make better sense of the values being displayed. Let's use this version of our Running Club Data spreadsheet to explore some number formats. The values we see in each of the tables in this spreadsheet are kilometers. But we really wouldn't know this by looking at the values. Well, we can change numbers to another format. One option is to select the cells first. Let's click and drag over all of the cells containing numbers in our first table named Distance Run - Men. And let's change the format by using the Format bar. Here you will find some predefined formats like Percentage and Currency, for example.
The first button will display two decimal places. So let's select that one. Now you can see, each of the values has two decimal places. And in many cases numbers were entered using those decimal places. Some were not so we see 0s in those cells. Now we can also adjust the number of decimal places manually from the Format bar. Before we go there, check out the number that appears in cell C4, 56.56. Well, when we go up to our Format bar, we have two buttons one for increasing, the other decreasing the number of decimal places. Let's decrease this down to one.
Click once and notice the number is automatically rounded up. Here in cell C4 where we saw 56.56, we now see 56.6. So that's done for us automatically. Well, we can also choose from other formats using the drop down button that appears on the Format bar. Click this button and from the pop-up menu, you will see some additional options like Date & Time formats, for example. When we click the canvas to close up that menu, we can also access additional formats from the Cells Inspector. Click the Inspector button, make sure the Cells Inspector is selected, and not only will you see the current format, in this case Number with 1 Decimal.
You will also see information you maybe didn't know, such as Thousands Separator. It's turned on. So when we get into larger numbers we'll see commas for the thousands. And negative numbers will appear with a minus sign. We can change that of course. Click the button. Let's say we want negative numbers to appear in red in round brackets. Choose the fourth option. But we can also choose from other formats by clicking the current format Number and choosing from a different format on this pop-up menu. But really what we need is our own Custom format to display Kilometers. Well, with Numbers '09, you can create your own cell formats for displaying numbers with text or date and time values.
Custom can be accessed directly from our Cells Inspector here, but it's also available from the drop down button we saw on the Format bar, or even from the Format menu. Let's choose Custom. Here is where we get to give our Custom format a name. The Name field currently has the default Custom Format 1 in there, if you don't have any Custom formats. And it's selected, so we can type right over it. Let's type in Kilometers. Now the Type, which defaults to Number & Text, is perfect for us. The only other option is Date & Time, so we'll keep it as Number & Text. And now down below we have a field where we can start adding elements. There is one already added for us and that's the Integers representing the actual numbers that will appear. We see a preview over here on the right hand side of what a number might look like with our current format. But we are going to change this.
Let's click right in the field after the Integers element and we know we want one decimal place. So let's add the Decimals element by clicking and dragging it into the field. When you see the plus sign, release, and it gets added. There is an example of what a number might look like. Each of these elements has its own little drop down arrow. We can click that to see a pop-up menu. If you want Decimals to appear as Fractions, you could choose that. But really what we want to do is control the number of decimal places; we can do that by Adding or Removing digits or move directly to Number of Digits and select a number. Let's choose 1.
Now we'll click after that element in the field and we want to add a space. So we'll drag the Spaces element into the field, when we see the plus sign, release. And we'll click after that space. And here is where we want some text. The short form for kilometers is km. Let's type km in lower case. Check out the preview over here. That's perfect. So to save our new Custom format and apply it to all of the selected cells in our table, click OK. When we do, it now appears in our Cells Inspector as Custom. In our table, you can see all of those numbers have been converted to this new format. Whenever you see a plus sign in a cell, it means we are not seeing all of the data.
So in this case we might want to go in between the column headers at the top. In this case if we go between B and C and see the double arrows, we can double click to just stretch out that column to fit the widest entry. We'll do that for each of these and you can see that's perfect. Now the really nice thing about creating a Custom format is it's available for other tables in your spreadsheet. So for example, we can click and drag to select all of the numeric cells in our second table Distance Run - Women.
And now we can apply our Custom format, it can be applied from the Cells Inspector. Or directly from the Format bar, click the dropdown button, you will see that Custom option there as well. So let's choose Kilometers to apply that format to our other table. Again, we see a couple of plus signs where we might want to automatically adjust the column width to display all of those values. One more table, we'll click there. Let's do it this time from our Cells Inspector, click the drop down button, and choose Kilometers.
Let's close the Cells Inspector and we'll click on the canvas to deselect the table. And there is the end result. Of course you can have as many Custom formats as you want. And at any time if you want to remove a Custom format, go to the Format menu where you will see an option for managing Custom Cell Formats. Before you can do that though you will need to select a cell in at least one of tables. So let's go down to our first table Distance Run - Men, we'll select any one cell, go up to the Format menu, go down to Manage Custom Cell Formats, where we'll see our only Custom cell format called Kilometers. When you select it, the minus sign will allow you to remove it. Now when you click OK that cell format is no longer available. Although you will notice the cells that were formatted using it will remain.
So even though Numbers provides us with several number formats, remember you can also create your own custom formats to suit your specific needs.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.