From the course: Excel 2013 Essential Training

Exploring numeric and special formatting - Microsoft Excel Tutorial

From the course: Excel 2013 Essential Training

Start my 1-month free trial

Exploring numeric and special formatting

“

Excel has a wealth of numeric formatting options, and two of the most common options are the dollar sign and the comma button. They're found in the Number Group on the Home Tab in the ribbon. Column F shows salaries. It's probably unnecessary to show the dollar sign, but we might want to. Notice that when you point to dollar sign, the pop-up tip says, "Accounting Number Format" and that's what we see. If you make the column wider, the dollar sign stays on the left-hand side. You might or might not like that. That's certainly one option. You could easily make the case for saying, "Well, these are salaries. We don't really need to see the dollar signs. It's pretty obvious, isn't it?" So we might want to use comma. This is not the opposite of dollar sign or accounting format. It simply doesn't display the dollar sign if we use comma. I wouldn't do this for the whole column. And we don't need to make the column that wide, we can make it narrower. If we don't want to see the pennies there, they're all zeros anyway, we could certainly make that column even narrower by not displaying pennies. By the way, using these buttons to increase the display of decimals or decrease has no impact whatsoever on the actual content. Sometimes, people do make that mistake and if we did have pennies there, this would simply be hiding them and it would do visual rounding. Now, column G here, similar data, although this case does have negatives, what happens with accounting number format as we readjust the column width by double-clicking? Negatives appear in parentheses, here too, making the column wider and narrower. The dollar signs are always on the left-hand side. There are other variations though. If we press the Dialog Box Launcher right here, or possibly press Ctrl+1, that will take us to the Format Cells dialog box. On the Number tab, we see Accounting format here. How about Currency format? Isn't that the same thing? It's going to be different in two respects. You want to keep an eye on the dollar signs here in column G as well as what the negatives look like. And I think you can see ahead of time, the negatives might be in red or they might be black in parentheses or they might just be red. In other words, you make the call as to how you want this to look. Here we go. That's another variation. Notice on the whole numbers that are positive here, the dollar sign is right next to the number. If I were to change just this one-- watch Accounting Number Format-- shift the dollar sign that way. The objective of this is not to confuse but to have you recognize that, when you do get data from other sources, you might get a mix or maybe you're getting data that's in one format or as you use the other. You want to come up with some kind of a standard. I say by all means, if possible, if you like the dollar sign, Accounting Number Format, use it and stick with it, and maybe show the decimals or don't. But if you simply stick to one variation or another, you won't get too bogged down on these. Recognize too, when we have data like this, making the columns wider, sometimes we want the dollar sign to stay hugging the left-hand edge, sometimes we don't; and recognize here, that's accounting format in these two cases. The example here, this is simply the comma button choice with no decimals. Now, with certain other kinds of data, for example, the data in column B, you may or may not use Social Security Numbers, but it looks like something happened here that's not quite complete. Whoever typed these entries here decided not to type the hyphens. Well, why not? Well, it's a good idea but not quite fulfilled. If you're going to use Social Security Numbers, take advantage of the fact that Excel has a built-in format, and rather than typing the hyphens, let Excel enter those by way of a format. So this time I'll right-click to get to it, remember, we could also press Ctrl+1 to get to it. Format Cells, if you right-click. And this time, using the Number Tab, the category is "special" and there we are with the Social Security Number, click OK. Make the column wider in this case, and let me--using the zoom bar in the lower right-hand corner--zoom in a bit on that so we can see it even better. There we go. These are actually not here. They're not there in the Formula bar. If you double-click in the cell, you don't see them there. If you're making a change, maybe that should have been an eight, you don't see any hyphens. Press Enter, they are there. They're in the format but not in the actual content. If these are supposed to be phone numbers in column C here, I want to take the whole column here, and format these in the same way that we did earlier; either by right-clicking and going to Format Cells or pressing Ctrl+1, Format Cells, and here, Category, Special. Same place we found Social Security Number. Now, we have phone number. Click OK, and that displays the numbers once we adjust the column width this way. So, rather than typing 14 characters, you just type the numbers. So here too, if you're adjusting these, maybe that shouldn't have been an 805, maybe that was a 213, you want to double-click in here, change that to 213. Press Enter and you've adjusted this. So those are two built-in formats as well. At different times, you will want to change formats, but again, make it simple. Rely upon the buttons in the ribbon as much as possible. Remember, these are also accessible by way of the mini toolbar. So, if we were to right-click here, we could go into the mini toolbar here and make our choice here of dollar sign or comma as well. And then there is the occasional use of adjusting, the positioning of the decimals as well. So, a variety of numeric formats, we can get to them easily by right-clicking or going in the format cells to adjust those displays of numbers.

Contents