Join Dennis Taylor for an in-depth discussion in this video Keystroke shortcuts from A to Z: Using the Ctrl key, part of Excel Tips Weekly.
- [Voiceover] Excel has well over 200 keystroke shortcuts, and there's a whole series of them starting with control A all the way down to control Z. Now some of these are widely used, some are not, but it's worth a look at all of them. Two of them are not used at all, so there are 24 keystroke shortcuts that we're about to talk about. Control J and control M have no keystroke meaning. Usually when you see these listed at websites or in books, you see something like control plus A, meaning hold down the control key as you click this letter. Control A has multiple meanings.
If you click in an empty cell with no data around it, control A selects the entire worksheet. That's gonna be handy at times. If you click where there is data, and there might be other data on the worksheet but separated from it, control A means highlight all the contiguous data. So I've clicked on cell B5, I'll press control A, it highlights just this data. If I want the entire worksheet highlighted, I'll press control A again. So if you wanted to highlight the entire worksheet and the active cell were here, press control A twice.
And you don't have to release control while you're doing that, in other words, press control A A, on example. Now in other situations, if you happen to be working with a table, this is data that's been converted to a table. When you press control A with a cell in the table, it highlights all the table except for the title row on top. Press control A again, it highlights the title row, and press control A a third time, it highlights the entire worksheet. So in varying degrees, control A highlights a lot of data.
It's certainly different in different examples here. Remember, once again, clicking in an empty cell somewhere where there's no data around, control A means select the entire worksheet, highlight the entire worksheet. When you're within data, press control A twice to get to that same feature, highlight the entire data in the worksheet, or simply highlight the data in what's called the current region, the cells that are adjacent and different in the table. As I use these I'll also apply a background color, we'll make them yellow, to remind us. Bold, along with italic and underline are right up here and all three of them have keystroke shortcuts, and they're exactly what you would think they would be.
Control B for bold, there it is. Or we take it off, it's a toggle, all three of these buttons. Control B, control I, control U, let's highlight that that we've done it, and here's italic down here, control I, click here, control I, makes it italic or turns it off in different ways. Make that yellow. And I'll also underline down here, control U, there it is right there, control U applies underlining. There it is. And that too is a toggle, it's on off as needed.
We'll make that yellow as well too since we've talked about it. Control C for copy, widely used, one of the most convenient and frequently used keystroke shortcuts. I want to make a copy of this data, I'll highlight it, press control C. If I wanna paste it somewhere, maybe I wanna paste it down here, it's companion, control V means paste. Highlight that since I just used it. We'll use it again too, because a lot of you know that not only do you frequently copy paste, you can also do a cut and paste.
So let's make copy yellow as well. Sometimes we want to not just make a copy, we actually want to move it somewhere. So I've got the data here, really don't want it there. I'll press control X for cut, we see it right up above here. Control X, nothing happens to the data just yet, but we're gonna scroll to the right maybe and put it out here somewhere, over there. Control V to paste. So, those shortcuts are widely known, widely used. Let's highlight control V here as well for paste, control X for cut, there we go.
Make that the yellow to remind ourselves what we've done here. Now control D, copy data or formula downward through selected cells, has a companion, control R, copy data or formula rightward. And the ironic and strange thing about this is that these two buttons are interchangeable, meaning we can use either one in this situation. Now, copying data downward is gonna be much faster if you double-click the lower right-hand corner. In a situation where, for example here, maybe we want a formula there, it actually calculates equal, and maybe we'll do rounding while we're at it.
Let's round this calculation. We're gonna take the compensation amount here and multiply it by this percent, use the function key F4 to make it absolute, and add on to it, or add to it, the compensation here. Comma zero, let's only show pennies, we'll do that. Now if we wanted to copy that downward we could certainly double-click the right-hand corner, we could also highlight the data and press control D, meaning down. It copies the formula downward. And oddly enough, as I press control Z to undo, I can press control R, even though it means copy the data rightward.
So that works as well too, that's strange. Another situation off to the right here, we don't have our double-click shortcut but if this formula is subtracting those two cells, and we wanna copy to the right, we can certainly drag the lower right-hand corner or we can highlight the data this way and press control R, and as I undo it with control Z, and strangely enough, even though D means down, I can press control D. So that's a little bit strange. It would be logical to press control R there. Nevertheless, those two features we've seen here, control D, now there's another aspect to control D we also wanna show you.
Let me first highlight these to indicate that we've shown them. And control Z for undo. I'll probably use that again too, but let's highlight it, we've used it a couple times. Another use for control D is for duplicating an object, and so if for example, you go to the Insert tab, you might pick one of the shapes here, maybe I'm gonna put some text in this rounded rectangle, but I'll drag it first, I might want a few of these, so I'll just press control D a few times. Move these around, I'll eventually put text in those. So that's a use of control D. And you can also use these, by the way, on charts.
So maybe I've got some data over here, I want a chart based on this data right here. So I'll highlight it, quick chart, creation tip, alt F1, there's a chart. I might wanna show that chart in a different way, maybe as a line chart also, so I'll duplicate it with control D, that duplicates the chart. And maybe after duplicating it, what I'll do is I'll go to the Design tab here and off to the right, change the chart type to be maybe a line chart. But I was able to quickly duplicate the chart just by pressing control D.
So you can easily create not only duplicates for charts, but as we saw over here, any object. I don't really need these so I'll get rid of 'em. Control E is for flash fill. That's a feature not everybody's familiar with. Flash fill allows us to quickly fill in data. Now, I've got these employee names over here, and there's nothing wrong with those, but maybe I want to set these up, say for mailing labels. I don't want to retype all these, but I am going to type right now, Rachel Porter. And I'll press not enter but control enter.
I want the active cell to stay in place, but I'm gonna press control E, and it fills in the other data. That's flash fill. It's a feature that's on the data tab, flash fill, right there, and you see the keystroke shortcut as referred to, control E, automatically fills in values. Enter an example, that's what we did, and it filled it in beautifully. So that's handy too, that's control E for flash fill. A relatively new keystroke shortcut, that was a feature introduced in Excel 2013. So back on the home tab, we'll make that yellow as well too. Control F, find, we're looking for data over here, maybe we're looking for, we don't know if there's a Bob in there, is there a Robert in there, let's just click here, control F, can we find Bob in there? Robert? We could find all of 'em, just find the next one maybe.
There it is, or the next one, and so on. Control F for find. And while we're here, if we wanted to do a replace instead of that, let's move back, there's control H. Control F for find, make that be yellow, control H for replace. So I've got a list over here, I wanna replace all the Edwards with Ed. I don't know how many there are, click here, control H, replace Edward with Ed. I could do it for all of them, replace all, there were three of them.
We don't know where they are, we could find those if we wanted to. But control H gets us to this dialog box fast. Control H for replace. Control G is the same as the function key F5, it means go-to. I use it occasionally when I want to go to a special dialog box. Control G, there we are, special, and that dialog box has lots of little treasures in it, for example, you might wanna highlight all the blanks within a region, something like that. Or you might wanna pick on precedents or dependents, lots of interesting choices in here.
We can get here quickly with control G and then clicking special. You can also do the same thing with the F5 key, takes us to the same place. Control G. Another use of this, by the way, could be for an address in a large worksheet, maybe I need to go down to cell A500, I was working with data near there yesterday, I could certainly scroll, it wouldn't take that long, but strictly keyboard here, I could press control G, A500, or whatever the address is, enter. There we are. Control home will take me back up top quickly.
So that's control G. Control K, insert a hyperlink, I don't really need to do that right now, but if I wanted to hyperlink here to a different location, a different file, a website possibly, control K. This dialog box pops up. I could, for example, go to a place in this document, an existing file or webpage maybe, I could type in, for example, the hyperlink. We could also put in a screen tip and do other things. I don't need to go there necessarily. But we could possibly do this if we wanted to.
We could also do it to a place in this worksheet, too. So you can do it under recent files, places in this document right here, I've got another range name here, I've got another sheet, possibly, sort of thing, maybe I'll just use this here, so that's a hyperlink. I could go there too if I were on the internet. Control K, substitute for insert hyperlink. Make that yellow. Control L is the same as control T, it let's us quickly convert data to a table. Got data over here that's already a table, I might want to turn this data here into a table, let me first move this down here for the moment, and let's say I don't need this data anymore.
I wanna convert this data to a table, I can press control L, that starts me down the path of creating a table. Or, identically, I can press control T, starts me down the same path. So those two keystroke shortcuts are interchangeable. Control L and control T. Make them both be yellow. Control N, if I want to create a brand-new workbook, control N, we'll see a new workbook popping up on the screen, there it is, gonna be called book one or book two, book three, something like that.
I wanna go back to the other workbook, control tab. So control N creates a new blank workbook. Control O to open a file, control O. I'm not really going to open a file, but I've got some other files out there I could be opening right now. Escape, right back here. Control O to open a file. Control P for printing, I don't really want to print right now, but if I did, control P certainly starts me down that path. And that's easy to get to. And also, by the way, is print preview too. And you can also get to print preview by way of control F2, and escape.
Control P actually takes us to print and print preview, or you could call it print or print preview, but same location. Control Q, activate the quick analysis toolbar. You don't really need this very often, because if you do highlight data, for example, like this, you're likely to see in the lower right-hand corner, the button. But you could of course, highlight the data and press control Q, and there it is, it pops up the choices. So I guess you could say it's faster, maybe, than clicking this with the mouse, but if you had just used the mouse to highlight these, it's pretty easy to get here, different ways there.
So highlighting the data, press control Q. Now, what if you don't highlight the data? What if you just click here and press control Q? That highlights the data for you and takes you here, so there is that advantage, of course, to activate these features where you might want to create simply, some conditional formatting with data bars or possibly create charts, so we've got conditional formatting choices here first, charts this way, and so on, and some other features. The quick analysis toolbar activated with control Q.
Control S, save the current file. I don't really need to do that right now, but I certainly could press control S. In your quick access toolbar, if you have an icon for save, and you slide over it, you'll see that it says control S. So although I'm not really demonstrating this, keep in mind that certainly is a fast way to save a file. And of course if your file has been saved previously, and you press control S, what you're really doing is updating the file. I don't want to close this file but I might want to close the other one. Control tab, maybe it's got some data in it, I don't really need that, whatever.
I'm gonna close this file, control W. I don't need to save it, don't save. Control W to save a file. Now, we used undo earlier, but control Y and control Z are often spoken of together. I might have a chart over here, and maybe I wanna chart with this data right here. I highlight the data, I can create a chart quickly. With alt F1 I've created a chart. It's a column chart, doesn't look too bad, I might change it to say, a line chart.
I like that. On second thought, maybe I like the other chart better. So I'll press control Z to undo, nah, come to think of it, I like the other chart. I'm pressing control Y to undo the undo. so control Z to undo, control Y to redo, or undo the undo as we sometimes say. It can get a little confusing if you try and explain it to others. Control Y has another use, too, but a limited use. I might have done some formatting here, maybe I'll make those cells yellow, and I'd like to repeat that over here.
I can click here and press control Y, I could highlight some of these and press control Y. And so on, there's that idea. If I wanted to apply multiple features, what if I said, for example, I want to change the format of one of these and then use it elsewhere? I could click this button here, to the right of font, it's a dialog box launcher, or I could press control 1, that activates format cells, and maybe do a few things here. I'll go to the font tab, I'll make it bold and italic, I'll apply a color background, maybe a different one here, maybe a green color here, and while I'm at it I could do other things, I could fill this in with other features, in other words, I'm doing multiple kinds of formatting changes here.
Maybe I'll fill this with a pattern as well, and color's okay, how about a pattern style here, maybe some dots there. So I've got a few things going on here, some font changes, I'll add some borders while I'm at it, how about a border like that, and so on. So I've done five or six things here, I click okay, it looks like that, I have to admit that's not very good, but what happens if I highlight these cells and press control Y? In other words, I redid all the features that were done within that dialog box. So control Y is sometimes used to repeat, but at other times it's the companion to control Z.
So after undoing something, you might want to reconsider it. So I'm gonna undo my last action, control Z, and then reconsider it by pressing control Y. So, different uses for these keystroke shortcuts, it's not to suggest they are the most important ones, or even the ones that you use all the time, but certainly a lot of popular ones are in the list. And there's no special merit to say that just because they're associated with a letter that they're special. So we've seen all the keystroke shortcuts, from control A to control Z, remember two of them have no meaning, control J and control M.
Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Intermediate
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.