Join Dennis Taylor for an in-depth discussion in this video Essential numeric formatting keystroke shortcuts, part of Excel 2013: Advanced Formatting Techniques.
- View Offline
- Excel has a number of keystroke shortcuts related to numeric formatting. And depicted on the right in columns M through S we see that they all use the top row of keys, keys 1 through 6. And also to the left of that, the key that has the tilde and the accent grave on it. And we'll be using control shift with all of these. And these are alternate ways to do some quick formatting instead of possibly using the $ here for Accounting Number format and the comma here. But they don't necessarily match up with those.
So as we scroll to the left, let's take a look at some of the data. In column A, we may want to display this simply as numerical information with commas, control shift 1. Now, we can say control shift 1 or control shift !. Both those symbols are on the same key. There we are, we've got that display. We may or may not wanna see the decimals. I think in most of these cases in the examples coming up, we don't. And so we can decrease the decimals twice from the Number group on the Home tab in the ribbon.
If you'd like to put in dollar signs here, you can use Currency format. It's control shift $. That's on the number 4 key. There, too, we may or may not wanna display the decimals. The amount changed, we possibly could have done this one as well, too, but if we wanna leave the numbers just like this, fine, we can leave that. We could press control shift 1 to get the difference that way. Control shift $ to get the difference this way. Different ways to display the data quickly. When it comes to data entries, these entries here in column G, are commonly used in the United States, but in other countries we tend to see the reverse.
This is June 8th in the United States. In other countries it's likely to be August 6th. But there's one keystroke shortcut associated with dates. It's control shift #. There we are. And it simply displays the month as a three-character entry. And that eliminates that potential, possible doubt you have sometimes as to which month it is. So we get the three letter abbreviation for the month. Nothing wrong with the time entries in column H, but if you'd like to see the AM-PM type entry instead, simply press control shift @.
That's on the number 2 key. Maybe you'll remember that by thinking something like I will meet you at 3 o'clock. Control shift @ for that kind of display. Now, less commonly used but nevertheless needed in some circles, is Scientific data. In column J, we see very, very small numbers, well below zero. Let's display these using Scientific format. That's control shift ^, the number 6 key. If you're not familiar with this, you probably don't really need to use it, but that first entry means 6.32 times 10 to the minus 2.
And in column K, we've got numbers well above zero. Let's display these in Scientific format, control shift ^, and we see those in that first entry means 1.65 times 10 to the sixth. Now, there are two formats we have not seen. General format is the native format, the default format in Excel. Occasionally, but not very often, we might want to switch numbers back to that format. If we simply want column A to be a string of numerical characters without symbols, we can press control shift tilde.
That's the key just to the left of the number 1 key. And that displays the numbers without commas, so-called General format. If at any time if you're not sure what the format of a cell is, you can right-click it and go right into Format Cells and on the Number tab there you'll see the display of the current format. In this case, it's General. Now, in column E we're about to put in percents, and the way we calculate this, based on the available data, is the entry in C2, so =C2 divided by the entry in B2 minus 1.
And we probably would want that to be percent. Simply press control shift %. Now that is identical to the % button that we have here. Double-click and we see our entries. It might be, in a case like this, advantageous to show one decimal, so let's use the Increase Decimal Button, found in the Number group on the Home tab, to get that display that way. So you can see, these shortcuts, they're not exactly, in all cases, like the percent case is, they're not, in all cases, identical, but they certainly are similar.
If you watched a previous movie in this course, you did recognize or remember that Currency format, although very similar to Accounting format, is not the same. And we'll have to adjust the columns to display that. One difference is that Accounting format does make room for negatives, and so the parentheses needs to make room for those. Make these wider, also. The Accounting number keeps the $ on the left side of the cell, regardless of how wide it is. And Currency format, which I'm about to use, will put the dollar sign next to the numbers.
Control shift $. There we go. So, again, these are depicted to the right in this chart, and use them as alternates for those buttons that we see on the Number group. These are faster, of course, keystroke shortcuts always are, but they give us some nice, quick ways to format numeric data and they're all located next to each other in that same portion of the keyboard.
- Adjusting fonts, cell borders, and fills
- Aligning text
- Indenting data
- Setting special formats for dates, times, phone numbers, zip codes, and more
- Creating formats based on formulas with conditional formatting
- Applying styles and themes
- Adjusting row heights and column widths
- Adding background images and pictures
- Outlining data
- Printing double-spaced data