Join Dennis Taylor for an in-depth discussion in this video Working with numeric formatting options, part of Excel for Mac 2016: Advanced Formatting Techniques.
- [Instructor] In this workbook called Chapter Three Numeric, the very first worksheet has abbreviated name, Accounting Currency Comma. Now, as we look at the data here, the numbers have not been formatted. As we look at columns D and E, some people prefer to put in dollar signs. I don't think too many would because it makes the columns look pretty busy, but we can certainly choose Dollar Sign from the Number group on the Home tab. Now as you slide over the dollar sign symbol, we see the pop-up tip Accounting Number Format. Let's apply it, and we see the dollar signs.
I'm going to make the columns wider too simply by dragging these like this, we see what's happening. The dollar sign does not stay next to the numbers, it stays down the left-hand side, and that could be something you want to change, or you might prefer it. Now we'll come back to that in a moment, but another option is to say, well, the word salary is in the heading anyway, we don't really need to see the dollar signs, let's get rid of them. This is not a toggle on, off button. Click it again, you're simply re-affirming the choice, but the Comma button here, even though it's not truly the opposite, will keep that same general look with the obvious difference no dollar sign.
So many, many times, Comma style is your alternate choice where you don't want to see the dollar signs. Now, in the examples here, all the numbers are whole numbers, so we probably don't want to see the decimals, it takes up more space, harder to read and all that, so off to the right, Decrease Decimal. We see what's happening there. So many, many times, your choices are likely to come from the Home tab in the Number group, and for many people using the Comma button works just fine, using the Dollar Sign button at times too.
They're right close to one another here, easy to get to. Now from time to time, you get data from other sources, and you might get data that looks similar and yet different enough to be annoying. Notice that there is what looks like a half a space behind these entries. It's not exactly obvious why that's the case. The reason is that sometimes you will see negative data. Now with salaries you wouldn't see that, but possibly with profits as we see over in column J, we see some entries over there, so let's move over there and consider these three sets of data.
Let's say these do represent dollars, and once again you can make the case for saying we probably don't want dollar signs, but let's highlight all these, and use Comma format here. Then we'll readjust column J, make it a bit wider so we can see all those numbers, so there we see them, and that might be a surprise. Comma format here does put negatives in parentheses. Now, in accounting circles that's the standard, that's the norm, and I think many of you are familiar with that look, and that's the way you might want to keep it, but some confusion can arise here because sometimes we do formatting when we need to get the other features by way of the Format Cells dialog box.
Now we don't necessarily need to use all three columns at the same time, but let's suppose we do. Highlight these three, right-click and go to Format Cells, or you could press Command 1, and on the Number tab here, notice that we see Currency, Accounting, and Number, and yet we do not see Comma. Comma is a variation on Accounting, and so that's a little confusing to begin with, but let's choose Currency here. Now notice something else pops up, we do have different alternatives for Currency, so we could have leading minuses or the entries in reds here if we use Currency.
If we flip over to Accounting, we don't see any option for that. And how about Number? Under Number we see some different choices here as well too. So if we do choose, for example, Number, and have the negatives with a leading minus, that means we wouldn't need any spacing on the right-hand side for negative numbers. So what if I choose this? Two decimal places, we do want the commas, let's say that we want the negatives to have a leading minus, we click OK, and we have that look, and you probably saw that data shift to the right a little bit.
And here too it looks like all the entries don't have any pennies involved, they're not significant at all, let's highlight all these and then decrease the decimal for better display and possibly readjust the column widths. Now, let's just focus on this and use Accounting again, I'll put it right here, and readjust the column there, make it even wider to accentuate what happens here if we use Accounting format. We see how the dollar sign stays to the left. And now, going back to Format Cells and selecting the whole column first, Command 1, another way to get there, let's choose Currency.
Everything looks fairly similar here. Two decimal places, dollar signs on, but watch the dollar sign on the display. And notice how all of the data shifted to the right. So Currency format, the one that is not this dollar sign here, but the one you can get to by way of Format Cells, doesn't have the dollar sign floating off to the left-hand side, it's right next to the data, whereas this button right here, and I'll click it now, Accounting format, has the dollar sign to the left. And the issue is not so much that you want to use both of these a lot, but probably settle in on one of them.
I think for many Excel users it's easiest to use the dollar sign here, the comma here, and not worry too much about some of the other variations that you might encounter, but there will be those times when you get data from other sources, and sometimes you'll see them in nearby columns where some seem to have that extra half space on the right-hand side and some don't. Sometimes the dollar signs are next to the numbers. Somehow reach a conclusion as to which one you think looks best and then fix on that one. So I think for most people, it's going to be easiest to highlight these for example here, if you want dollar signs everywhere choose this.
Readjust the columns as necessary. If you want Comma format without the dollar signs, choose that. I think that's for many people going to be the ideal solution. So Accounting format, Currency format, Number format, and even though it's not later found there's a separate format Comma style right here. Those are the ones most frequently used. So recognize the variations, and as you're working with data, do recognize those differences too, but for the most part, you've got the tools available here to display these numbers whatever way looks best for you.
- Adjusting fonts, borders, and cell colors
- Aligning text
- Controlling numeric formatting
- Building custom numeric and date formats
- Creating value-based conditional formatting
- Working with tables
- Using styles and themes
- Adjusting row, column, and sheet sizes
- Outlining data
- Adding and formatting images
- Using specialized power formatting techniques