Join Dennis Taylor for an in-depth discussion in this video Keyboard shortcuts for numeric formats, part of Excel Tips Weekly.
- [Instructor] Excel has seven different numeric keystroke formatting shortcuts. They're listed on this worksheet over in columns N through T. They all use the control and shift keys in combination with these seven keys that we see on most keyboards across the top, just below function keys on most keyboards. And by the way, sometimes when you're referring to these, in a book or you see them at an online site, they're likely to be listed for example, shortcut for currency, they're listed as either control+shift+4 or control+shift+$.
Makes no difference how you say it, we're talking about the same key. For example 4 and $ are on the same key. Now, let's show how these are used in different ways. And by the way, two of these, the currency and also the entering number one, the exclamation point, do have a similar correspondence with features found on the home tab, we'll point out those differences as we get into them. Now the data in column is in what we call general format. General format is the native format of all Excel cells, and so many times you're not trying to apply that format, you're moving away from it.
This format by the way does allow decimals. Now under items sold, more likely than not, these are going to be whole numbers, but depending on the kind of item, possibly could have a half of one, so maybe we've sold 699 and a half of these, we could put in a .5. So these are all in general format, it displays decimals as typed, otherwise doesn't display them, doesn't put in commas or any other symbols. And rarely do we move toward that. But if we did want to use that, we would press control+shift and whatever we want to call that, tilda.
So if I click here, I'll press control+shift+~, no real change going on there since those are already in general format. And by the way, although not a keystroke shortcut, keep in mind that on the home tab, in the number group, click in the drop arrow here. We see general at the top of the list there. You see some other ones here too. All right, gross tonnage here, maybe this is going to be easier if we had commas in place. What could we do here is a keystroke shortcut, control+shift+1.
Remember, we could be saying control+shift+!, same thing. I'll click that here, have to make to the column wider. Double click that column B, and now we see commas and decimal places. Now, making that column a bit wider. Remember I did press control+shift+1 there. This is very similar to a feature found on that home tab in the number group comma. But watch closely at those numbers as I click comma. It too shows comma and zeros, but it moves the data slightly to the left.
And that's because these kinds of entries do take into account negative. Now if we're talking about tonnage here, so unlikely to be negative, but just for the sake of argument here, make this be negative. That's how negatives are displayed using the comma button. But if we were in a hurry and we wanted to format this data, first of all how do we take it back to how it had looked? General format, that's control+shift+~. Here's how it looks in general format. And you would have typed it as -2104. But to quickly change this to so called number format, that keystroke shortcut, control+shift+1, that's how the data will look.
Remember that's almost the same but not quite the same as that comma button, we see it up here once again, comma style, and you see how that looks a little bit different. Control+shift+1, this way. All right the entries in column D, time of sale. These are times of day in 24 hour style, that's widely used throughout the world and certainly in the United States as well too, although maybe not the dominant one. If it's important to clarify these by putting in AM PM, let's use the keystroke shortcut. The only one for time that we have is control+shift+2, sometimes referred to as [email protected]
And that's how the data will look, once again readjusting the column width, I'll double click the boundary between D and E. And there we see it. So that displays the data in AM or PM. Back on the home tab in the number group if we click the drop arrow here and choose time, we see the display this way, that shows hours minutes and seconds, so I think we probly wouldn't want that. So here's another display which if we saw it, we would say perhaps in this case, we really don't need to know the seconds there. Just gimme the quick time.
[email protected], remember the same as control+shift+2. That's the way we would see that data. Date of sale, nothing wrong with the display here. This is analogous to the feature once again on the home tab called short date. We're seeing the same thing there. If I had done the whole column, no change. Short date, looks the same. But if you'd like a different display, there is one keystroke shortcut. This is for date, control+shift+3 or control+shift+#, or some people call it pound sign. Anyway, control+shift+3, column E here.
We see that display. Now that's distinctly different from the other one. This is different from short date. But they're both accurate, it's just a question of preference there. So the keystroke shortcut and the only one we have for any kind of date shortcut is control+shift+3. Displays the date with a three letter abbreviation for month. All right the date in column G. Now we could be using the dollar sign button right here, this is a counting number format, I'll use it right here. And that's what the data looks like, again, re-adjusting the column width.
But what if I were to use the keystroke shortcut here? It's control+shift+4, control+shift+$, there is a dollar sign on that symbol. Watch the data, it will be a little bit different. Control+shift+$, there we see it. So that's called currency format. But it's different from that symbol we see in the number group. This right here means a counting number format, now it too is a dollar sign. Once again watch the data in column G. Apply it that way, or control+shift+$. You see the difference? But you'll decide whether you want to use that keystroke shortcut or the dollar sign symbol.
Now if we want to do a calculation here, we probly, if we're calculating the change, we'd want these two to probably be formatted the same way. So either we use dollar sign, see what's happening there. Or control+shift+$. But if we want to make a calculation here, the simple formula is the later entry divided by the early entry minus one, this is one of number of different ways to write this formula. Our answer here, really what we want here. What do we do in terms of percent of change here? Use the percent symbol right here, or, and these are identical, control+shift+%.
There we see the entry. Double click, copy this down the column. All these are percentage entries, we see the percent of change here that's take place comparing those two numbers with that simple formula. If we use the symbol up here that says percent, same thing. Control+shift+%, control+shift+5, say it either way. Quick way to format these. Now, less likely to be used but occasionally used, we've got scientific notation. These numbers were entered as general format. If we use scientific notation here, it's control+shift+6.
Just as easily we could say control+shift+^. And we see how that's being displayed in that so-called scientific or sometimes called engineering format. These are numbers well below zero, we see the entries there. That's 6.32 times 10 to the minus two. And these entries are well above zero, same keystroke shortcut, control+shift+6. And those are positives of course, that's 1.65 times 10 to the sixth. So these different keystroke shortcuts at different times are really handy.
Again, notice the difference though, the one here is very similar to the comma button that we see in that number group on the home tab, and the choice over here, very similar to the dollar sign here and yet not the same. But handy, these are handy keystroke shortcuts at different times. Be aware of them. And they're all conveniently located next to one another across the top row of keyboards just before the function keys.
Author
Updated
3/2/2021Released
1/16/2015Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Intermediate
Duration
Views
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.
Related Courses
-
Excel: PivotTables for Beginners
with Dave Ludwig23m 52s Beginner -
Presentation Tips Weekly
with Jole Simmons3h 32m Intermediate -
Excel: Lookup Functions in Depth
with Dennis Taylor1h 8m Intermediate
-
Excel Tips - New This Week
-
Previous Episodes
-
Quick formatting tips8m 9s
-
Create an organization chart8m 56s
-
Auditing9m 1s
-
Adding comments and shapes7m 35s
-
Creating an Excel template7m 57s
-
Adjust banded-row formatting14m 35s
-
Dealing with circular errors8m 20s
-
Sorting tips and shortcuts8m 40s
-
Freeze Panes and Split5m 30s
-
Calculate % of change5m 41s
-
How to adjust names5m 45s
-
Sorting by moving columns4m 16s
-
Using the Solver Add-in4m 29s
-
Create picture links5m 37s
-
Display large values5m 16s
-
Create a powerful macro4m 40s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Keyboard shortcuts for numeric formats