Join Dennis Taylor for an in-depth discussion in this video Working with the numeric formatting options, part of Excel 2010: Advanced Formatting Techniques.
Controlling the display of numeric information is one of the most important things that many Excel users need. This worksheet, like many an Excel worksheet, is loaded with numbers. Look at the information in columns E and F. We've got salaries here and nothing wrong with those entries really. But it's pretty much of a convention that as soon as we have four digits in numbers, we'd like to see commas or someway to read them more easily. We have to look the idea that selecting the entire column often makes sense. This will not use up any additional memory. It doesn't make the file any larger.
Why not select both of these columns, drag across here? The fastest way to change the display of these numbers in a sensible way might be on the Home tab, in the Number group, choose Dollar sign. By the way, you may have noticed as I slid over that dollar sign, the pop-up Accounting Number Format. In older versions of Excel, before 2007, frequently you would look at this dollar sign and would say Currency. Accounting Number Format, not in everybody's vocabulary.
Now, would that be a good choice here? And I am editorializing a bit here. For a lot of us, this is too many dollar signs. We see too many in every single case here. Let's choose comma instead. Now this is not the opposite. The name doesn't quite ring true. We're not sure what it means. We haven't used it. What does comma do? Essentially the same thing, but without the dollar signs. Unlike other kinds of formatting buttons, for example Bold, Italic, Underlining, the dollar sign is not truly an on- off or a toggle button. Click dollar sign. We see them.
Click dollar again, nothing happens. So even though these two are not the opposite, for many people it becomes the two most important formatting buttons when it comes to numerical information. In either case, and depending upon the circumstances, it's going to make sense not to show the decimals perhaps. Decrease the decimals. You likely to want to see either zero or two decimals, and of course these buttons here. If this were scientific data, you could easily imagine situations where we might want to show more decimals.
We can easily make these adjustments here. As always, formatting simply changes appearances. We are not changing values here. What if the needs are greater than this? What if some of the data we have doesn't quite fit or perhaps we're surprised with some of the choices here? In columns J, K, and L, we've got numerical information, financial information as well perhaps. Let's drag across columns J, K, and L. Dollar sign might be a choice, might not be. But what I wanted to bring out here is the idea that negatives of course will appear from time to time in data.
Is that the way you would like to see them displayed, the way we're seeing them in column L? That is the standard accounting notation style. If we choose comma, watch the difference. We'll still see the parenthesis. Not everybody understands or uses parentheses this way. So what we need to be exploring here is the idea that sometimes the buttons that we see in this Number group on the Home tab are not sufficient for all of our needs. You'll notice in the lower right-hand corner an arrow.
You'll also see this by the way on the Alignment tab, on the Font tab. When other tabs in the ribbon are active from time to time, you won't see any on the Formulas tab for example. You will see them on the Page Layout tab. But going back to the Home tab, this Dialog Box Launcher, as it's called, activates a dialog box, the Format Cells dialog box. Now this is one way to activate. We'll show you a few more. This is very familiar to long-time users of Excel, and many, many times it takes us behind the scenes.
It gives us more choices. For those choices perhaps that are not so obvious. Now it could be that the choice you're looking for is something you really are going to need often. So let's not minimize the use of this. As we look at this, what might we choose here? Here is another choice called Currency. Now notice when I click on Currency, you'll see the different choices here. If you prefer the negatives to have a leading minus, you might choose this option here. I'll click OK. We're not dealing with the decimals just yet, but just see what happen there.
It actually shifted the data to the right. I'm going to press Ctrl+Z to undo, so you can see that again. And I'll press Ctrl+Y to redo. This is the difference. Accounting Notation puts in parentheses and it needs that extra half space to the right. Notice in row five here, this data, this 34,469, has a half space out to the right. So the decimals line up properly. So it's a decision you have to make as to which kind of format works best for you. Again, clicking column L just for the moment, choosing comma.
That's the choice we see. The negative is in parentheses. If that's not what you want, one choice could be click that Dialog Box Launcher and then choose Currency and then choose a different negative. Now you could choose red of course or red in parentheses. Just for contrast, I'm choosing the leading minuses here and we see how that plays out. Now ideally, you should be consistent here. So if we're doing this, perhaps we would again go to that same location. Instead of clicking the Dialog Box Launcher, there are two shortcuts that you should be aware of.
Ctrl+1 activates the Format Cells dialog box that we just saw. You see that here. By the way, the tab that will be active is the one that you use when you most recently used this particular dialog box. If you were just for making font changes, had we come here, we'd see some font choices instead. But for the moment, we're interested in numerical choices here. Notice also there is another choice called Number, and this doesn't give us any options to put in currency symbols. Currency of course, does allow the dollar here, and other kinds of symbols that might be appropriate to your needs as well.
So in a certain sense you can say, if you're dealing with numerical information, you might want to explore Number, Currency, and Accounting. As we'll see a bit later too, there are some keystrokes shortcuts associated with these that will make them a bit faster. So you have to make a decision here and there about what works best for you. I frequently steer people back to the idea that try and use dollar and comma and be happy with it. Then you won't have to go behind the scenes and explore some of the other options. But here is another aspect of this that you wouldn't see necessarily, and you wouldn't probably see it in a column like this.
But I'm going to on purpose take both of these columns and make then substantially wider. Sometimes in financial reports, you'll see large numbers and smaller numbers within the same column, and look at the dollar signs here. They are off on the left-hand side. Now that may not be what you want and so what you could choose here-- and you wouldn't do this instinctively. But once again, pressing either Ctrl+1 or the Dialog Box Launcher, activate Format Cells again, and this time possibly choose Currency.
Click OK. Watch the dollar signs. So sometimes that's meaningful too. So we see the variations with the various number of choices that we see here. Once again, recapping, for fast easy changes, dollar sign or comma as needed. You decide on the display of decimals. Decrease, increase as needed. If you have negatives, think about whether the comma approach works, because it does put in parentheses. If not, either right-click, choose Format Cells or press Ctrl+1, or use that Dialog Box Launcher, and change these to a display that suits your needs best.
We can quickly and easily format numbers of this nature by using the buttons in the Number group, on the Home tab, dollar sign, comma, and where necessary, Increase and Decrease Decimals.
- Saving time with keyboard shortcuts
- Adjusting fonts, cell borders, and fills
- Setting themes
- Formatting numeric data with cell formats
- Using conditional formatting
- Hiding repeating column information
- Printing double-spaced data
- Elbow formatting
Skill Level Intermediate
Q: Where can I learn more about Excel formulas?
A: Discover more on this topic by visiting Excel formulas on lynda.com.