Join Dennis Taylor for an in-depth discussion in this video Learning the important formatting keystroke shortcuts, part of Excel 2010: Advanced Formatting Techniques.
Excel has quite a few different keystroke shortcuts that are going to help you speed along the process of formatting numbers. In column B for example, we have Sales Times, times of day. 24-hour type times here. Nothing really wrong with that. But if you would like to see AM/PM, there is a keystroke shortcut, and only one, for arranging the display of time. It's Ctrl+Shift+@ sign. Perhaps you might think I'll meet you at 3 o' clock. Maybe that's how you'll remember the at symbol.
Ctrl+Shift+@, and that's on the same key as the number 2 is. Over in columns M, N, and O, you'll notice that some of the shortcuts we'll be using here are displayed here. Ctrl+Shift+@. Some books by the way will display that as a shortcut. They might use the term Ctrl+@. Of course, you'd have to use the Shift key to get to it. So however you think of it, it is there on this key that has these two symbols on it and the change in column B as we now see is AM/PM. For some people, they are more comfortable in understanding this display than the 24-hour type.
Nothing truly wrong with column C. But the one keystroke shortcut associated with dates is Ctrl+Shift+#. Now perhaps that pound sign might remind you of a calendar grid. That never occurred to me once until I saw that in a book. But Ctrl+Shift+# does display the month as three letters and for some people that eliminates that momentary doubt when they're looking at a dates to which month it is. Now column D is just pure numbers and nothing wrong with the display there either.
Although it's sort of a convention that when numbers reach four digits, it would be appropriate possibly to have commas here. Many of you know that the Comma button in the Number group on the Home tab certainly gets the job done pretty fast. Now it does introduce decimals. Nothing wrong with that. I'm going to press Ctrl+Z. By the way before I do, notice that these numbers here do have a half space behind them. That indicates that if the values were negative, they would be in parentheses. For the moment here, I'm going to press Ctrl+Z to undo.
The keystroke shortcut that's closely associated with this, but slightly different in the way that it handles negatives, and in the display here, is Ctrl+Shift+Exclamation Point. It is referred to as number format. So the difference here between this and watch the display difference. Now when I press the Comma button, watch that half space on the right. You see what's happened. I'm going to press Ctrl+Z and put a minus in front of this, and again, reemphasizing what we're doing here.
Ctrl+Shift+! will keep the display the same. There we go. Watch the Comma button. Watch the difference. So here and there, even though that's not synonymous, that Ctrl+Shift+! isn't synonymous with Comma, they're very close. You might consider that as part of your repertoire of shortcuts here. Columns E and F. Many people will use the Dollar sign button. I certainly do a lot. It does apply Accounting format and that's fine. If the columns are wider, the dollar signs will stay on the left-hand side of the cell.
A closely associated keystroke shortcut, but again slightly different, is Ctrl+Shift+$. That's on the number 4 key. Watch the display difference here. The big obvious difference is that dollar signs are next to the numbers. Many people don't care where the dollar sign is, and so if it doesn't bother them whether the dollar sign is on the left side or next to the numbers, they'll use the keystroke shortcut or this. But again, note the difference. The keystroke shortcuts puts the dollar sign next to the numbers. The symbol here, the Accounting format, puts the dollar sign out here.
Once again with negatives, I'll put one negative here just for the moment. This is display. We get on the dollar sign. What's Ctrl+Shift+$ going to do for us? We see the difference. So you have to decide whether these shortcuts, which are not exactly identical to the buttons here, are going to work for you. There is no question they are fast. Let me change this back again, by pressing Ctrl+Z, back to here. A quick formula here for calculating percent of change. Equal. This is one of many different ways to write the formula.
The later amount divided by the earlier amount minus 1. The answer here we would want to see is a percent, and surely there is a Percent button right there, and there is also a keystroke shortcut that is identical to it. That's Ctrl+Shift+Percent sign, and we see the display there. It's sort of a rule of thumb that if percentages are below ten, you want to see at least one decimal. So we could increase the decimal there. Re-copy this and then copy the formulas downward by double-clicking.
Now here again emphasizing the idea that the keystroke shortcut on the symbol here are identical. Whether we click this or press Ctrl+Shift+%, we get exactly the same display. In both cases, we would need if we wished to increase the decimal this way. Now columns J and K have scientific information in them. Not everybody deals with that kind of information. But if you do, you're familiar with scientific type notation. Let's highlight both of these. Ctrl+Shift and the Caret symbol above the number 6 key displays these using scientific notation.
The numbers in column J were well below zero and this number here is readable as 6.32 times 10 to the -2. The numbers in column K as we saw them were well above zero. This is 1.65 times 10 to the sixth. Pressing Ctrl+Z again just to go back, just to remind ourselves the difference there. Ctrl+Shift+^. Now the default format in any worksheet as we begin working with the worksheet is General, and many times we don't need to go back to a General format.
But if that need were to arise, for example in column D, if you want to display these in General format-- and usually you don't want to go back to them. It's Ctrl+Shift+Tilde. That's the key just to the left of the number 1 key. So all the shortcuts that we have used here in this movie are found next to each other on the keyboard, to the left of the number 1 key and then reading down as we actually read across the keyboard, one through six. Here are all the shortcuts that we just used. They are adjacent to one another.
Here and there, I think many people are going to use three or four of these on a frequent basis. I know I do. They're fast, they're easy. Again, there are some slight differences here and there with the buttons that we find on the Home tab. But they're a lot faster many, many times, when you're dealing with data to simply press one of these keystroke shortcuts.
- 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.