Join David Rivers for an in-depth discussion in this video Formatting numbers, part of Learn Excel 2016: The Basics.
- When formatting numbers in your worksheet, you have access to all of the text formatting options we discussed in the previous movie, but you also get some number formatting options we need to explore. And we'll do that with our Landon Hotels Revenues sheet, here. You can open up RH_Revenues0202 from the Chapter Two folder of your Exercise Files, if you're catching up. Let's start by selecting all of the numbers we want to format, which happens to be all of the numbers in this sheet. So, we'll start at cell C6, go across to Column O, and down to Row 11, and release.
Once we have all of our numbers selected, we can now apply the same formatting to all of our numbers for a consistent look and feel. Because, you will notice, some of the numbers actually have three decimal places, while most have two. Everything's right-aligned in the cell. So, we can fix that up by going up to the Number Group, here in the Ribbon, with the Home Tab selected. You can see the Number format is already applied by default, and all that means is, we see a number with decimals. Click the drop-down, and you'll see some other preset formats, like Accounting, for example.
Click there. All this does, is adds a dollar sign to the left-hand side of the cell, and changes everything to two decimal places. So, there will be some rounding up and rounding down for those numbers that had three decimal places to give us the correct answer here. Let's go back up to the drop-down and try Currency. It's very similar, again it's two decimal places, but the dollar sign is next to the number as opposed to in the left-hand side of each cell. I kind of like that better. Now, there are other options, here, too.
Click the drop-down, you'll see we could use the British Pound and Euros, other options here as well. But we'll just leave it at the dollar sign, and look what happens when you choose English (United States), it changes it back to Accounting. So, I would go back up here, click the drop-down and switch it back to Currency. There are also percentages and comma formats to choose from here, but, you'll also notice a couple of buttons for increasing and decreasing the number of decimals. If you wanted three decimals, you could bump it up. Let's bump it up.
And you can see what happens here. Bump it up again, and we're up to four decimal places, if we needed that type of accuracy. I don't think we do, because the last digit we have in every number is zero. So, we could bump it down, I would actually go down, back down to two. If you wanted to go down to one, you could do that as well. Let's bump it up to two and leave it like that. Alright, if we wanted to change the alignment, we can go into some of those text options we explored in the previous movie. Let's center all of our numbers under our titles. You can see what that looks like.
Typically, not done with numbers, but, in this case, to make it look nice and consistent with our labels across the top, this looks pretty good. And we could also do things like bolding, italics, underline, changing the font, and size, and color. But I think, if we click in the background, here, in an empty cell, to deselect everything, that looks pretty good. So, when formatting numbers, you can format them, like you can with text, but you also get all of those number formatting options at your disposal, here, in Excel 2016.
- What is Excel?
- Creating and saving workbooks
- Adding formulas and functions
- Using AutoFill
- Formatting, text, numbers, dates, and times
- Merging cells
- Inserting charts and pictures
- Sharing and printing workbooks