22 Excel shortcuts to supercharge your efficiency

Garreth Hanley:
This is Excel Tips, a podcast for accounting and finance professionals brought to you by CPA Australia.

Neale Blackwood CPA:
Welcome to the Excel Tips Podcast. My name is Neale Blackwood. In this episode, we are going to look at some handy Excel shortcuts. Some of the biggest productivity gains are with those tasks that you perform frequently, so the small savings add up during the day. Now, although these tips are for Excel, many of them will work in Outlook, Word, and PowerPoint, so don't be afraid to give them a go in those other apps. Now, there is a companion video for this podcast and it goes through all of the shortcuts and you can see how to apply them and how they work. So feel free to check out INTHEBLACK website and check out the companion video.

I thought I'd start with selecting files and folders. So that's something you do sort of all-day every day, and there are a number of shortcuts that are built into Excel. You can use those to make the process of selecting files and folders a lot quicker. So the first one is pinning files to the top of the list. So when you go into the file ribbon tab, it opens up on the recent list. So these are all the files that you've had open recently. Now you can point to one of those and you'll see a little pin icon on the right-hand side. Now you can click that pin icon and what that does is it pins the file to the top of the open list because there's an open option in file and that list has all of the pinned files at the top and then all of the recently open ones listed below.

And so what you can do is you can have all of your commonly used files pinned to the top of the list and then all of the recent ones underneath that. Now obviously you don't have to leave them pinned. You could pin some of the files there just for a project because you're going to it regularly and then at the end of the project you can unpin it. So if you click the pin icon again, that will unpin it from the list. Now the same functionality also works for folders. So there is a folders option in Open. And again, you can have your commonly used folders pinned to the top of the list. Folders and files are what we work in, and so being able to access them quickly can save you a lot of time. In terms of folders, there is another option that you can use.

When you go to browse, so there's a browse option which then lets you get to your folder structures and all of your drives, et cetera, there is something called quick access. So this opens on the left-hand side of the screen and pretty much any folder you can add to that quick access list as well. Now this can be used across Word, PowerPoint, outlook, things like that as well. So it's actually built in I think to the file structure. So when you are opening files normally using Explorer or something like that, the quick access is always listed on that left-hand side. So it's a similar thing. You right click on a folder and there's an option to pin to quick access. So it's towards the top. So right clicking is another shortcut that we'll mention later.

When you right click things, you get a what's called a context-sensitive menu up. Towards the top of that, when you right click a folder, one of the options is pin to quick access. So the quick access is the listing on the left-hand side of the screen. So that's files and folders, so some easy and quick ways to access the ones that you use frequently. Now there is another quick access feature that's worthwhile using in Excel and it's called the Quick Access Toolbar. So when you install Excel, the Quick Access Toolbar is situated above the ribbon, so it's in that green area where their file name is.

Now I don't personally like it there, so if you right click that, so it's going to be in the top left corner of the screen, it'll probably just have a couple of icons if you are not using it there. You can right click it and there's an option to show it below the ribbon. Now that's where I tend to use it so any of the icons from any of the ribbon tabs, you can add to the Quick Access Toolbar, which means you can access them a lot quicker and it's easy to do. All you do is you right click any of the icons. The very first option is add to Quick Access Toolbar. Now the beauty of that is you can put your own toolbar together and you can group things together, you can move them around. When you point to an icon on the Quick Access Toolbar, it still tells you what it is.

So in case you forget what the icon means, if you just point to it, you get a what's called a tool tip and it'll explain what the icon does. So if you right click the Quick Access Toolbar, there is another option called customise Quick Access Toolbar. And in that, you can actually access all of Excel's options pretty much. There's a dropdown and you can select all commands. That list includes everything you can do in Excel using icons. And so you can add that, you can search for things and then add it to your Quick Access Toolbar. On the right of that screen, your Quick Access Toolbar icons are listed and you can click on one. On the far right-hand side there's some arrows so you can move them around. There's also something called a separator, which is listed at the top of the list on the left-hand side. Now what that does when you put it onto the right-hand side of the screen, it gives you a horizontal line, but when you actually see it on the Quick Access Toolbar, it'll be a vertical line.

So what you can do is you can use these separators to sort of separate the sections on your Quick Access Toolbar. So for example, you might have all of your print options together. And then next to that, all of your formatting options. And next to that, all of your data options. Those sort of things. So you can actually build in some structure to your Quick Access Toolbar and keep all of your icons together for specific tasks. When you do make a change, I always remember to click the okay button, otherwise it gets lost. Something else you can add to the Quick Access Toolbar are macros. So if you use macros, you can create a button on the Quick Access Toolbar to run those macros. So that's another functionality that can save you a lot of time.

Okay, let's talk about keyboards. Laptop keyboards are famous for being sort of cut down in terms of they don't have all of the keys. Sometimes you can't access your function keys because you've got to press another Fn key to access function keys, things like that. One of the first things that they take off a laptop keyboard tends to be the numeric keypad on the right. I like having that keypad there. Now if you don't have it on your laptop, so I'd re recommend buying a cheap full-size keyboard that you can just plug in via USB. Or if you want a wireless one, you can do that as well. That gives you your numeric keypad on the right-hand side. The keys on there can really speed up your work. The numeric keypad is based on the calculator layout.

So one is at the bottom, not the top. It's also got a large plus sign key and a large Enter key. Now they can speed up your work. Not many people know you can start formulas in Excel with the plus sign. Now that's a lot easier when you're using a full size keyboard because there's this nice large plus sign on the far right-hand side. When you press Enter, Excel will insert the equal sign for you. All formulas in Excel must start with the equal sign, but you can type in the plus sign and Excel will insert the equal sign for you once you press Enter. As well as the plus sign, the minor sign, the divide sign and the little asterisks or the multiplication sign are there as well. So all of the standard operators for your mathematical calculations are on that numeric keypad. So I highly recommend using a full size keyboard, especially if you're on a laptop, it can really speed up your work. Speaking of keyboards, let's talk about keyboards shortcuts.

Now Excel's got lots of them, so I'm going to cover quite a lot of keyboard shortcuts. Everyone uses Excel slightly differently, so just pick up the ones that you think you can make use of. Start using them. The quicker you use them, the quicker they become second nature and you don't even need to think about them. So keyboard shortcuts can save you a lot of time because they can... In a lot of cases, using the keyboard can be quicker than using the mouse. So let's start. We'll start with the F4 function key. If you are on a laptop, as I mentioned earlier, you might need to press another key to access your function keys. The F4 function key allows you to add the dollar signs to cell references and range references when you are creating a formula or editing a formula. So all you do is you click next to the reference, you press the F4 key and Excel will automatically insert two dollar signs. So a dollar sign on the column number and a dollar sign on the row number. Now this is called a fixed or absolute reference.

That reference won't change as you copy the formula around. If you press the F4 key again, it'll take the dollar sign off the column reference and leave it on the row reference. If you press the F4 key again, it will then move the dollar sign in front of the column. And if you press the F4 key again, it'll remove the dollar signs and go back to what's called a relative reference so there's no dollar signs in there. Now one way to remember the F4 key is that the number 4 key has the dollar sign above it.So the F4 key can speed up putting the dollar signs in there. You get used to how many presses are required because that sequence that I explained is repeated. So you can get used to pressing it twice, three times, however many times you need it to get the dollar signs that you require so that you can copy the formula down and across. The F4 function key also has a second use. It repeats things. So if you format a cell for example and you want to apply that format to another cell, you can just select the other cell and press F4. So the F4 repeats the last action that you did. It doesn't do everything but it does most things. So give it a go.

So F4 will repeat what you did last. It typically works pretty well with formats and things like that. Okay, the F9 function key. Now typically that's used to do a calculation. So if someone's turned calculation off in a file, you can press the F9 function key to force a calculation. But the thing that I tend to use it for is when I'm editing or looking at a formula and trying to figure something out about a formula, what you can do is you can highlight part of the formula and press F9. And if that part of the formula can be calculated, what Excel does is it returns the result in the formula bar so you can actually see what the result is. So this is a good way when you are trying to find errors and pick things up like that, that you can use the F9 key within the formula bar to track errors and see the results. Now when you do use the F9 key, always press the escape key because otherwise the numbers that it tells you stay there. And so it sort of stops the calculation almost if you like because the number that has been displayed will stay in the formula, which is not what you want. So always press the escape key to get out of the formula there and that puts the formula back to the way it was.

The F12 function key is one that I use a lot. It is Save as. Now I don't particularly like the Save as in the file ribbon, so I just press F12 and that opens the Save as dialogue up directly. And so I find that much easier to use than the Save as that's in the file ribbon. And again, that will work in Word and PowerPoint and Outlook. The reason I use F12 quite a bit is that I do use a lot of versions when I'm building models. I tend to create quite a few versions so that I can go back to them if necessary. So F12 is a really quick way to save it with a different version number or save it to a different folder whichever way you need to do it. Okay, now Ctrl key shortcuts. There are lots of Ctrl key shortcuts, you're probably using a few of them. Now. Ctrl+C to copy Ctrl+V to paste, they're the standard ones.

I'll take you through a few other ones that you might not be aware of. If you've got a large range selected. One of the useful Ctrl key shortcuts is holding the Ctrl key and pressing the full stop. Now what that does, that will take you around the corners of the selected range. Now as I mentioned, if you have a large range selected, this can give you a feel for how big it is and where it is and what's around it. So Ctrl, hold the Ctrl key and press the full stop. You need to have a range selected and then it basically just takes you round each corner. So each press of Ctrl and the full stop will take you to another corner of that selected range. Okay, Ctrl and the left square bracket. What that one does is that will follow a link. So if you've got a link to another cell or another sheet and you press that Ctrl and the left square bracket, which is on the right-hand side of the letter P, then that will take you to the first cell link in the formula. Now to go back, you can actually press F5, the F5 function key, and then Enter.

So you don't hold them down, you just press them in sequence. So you follow the link, so Ctrl and then the left square bracket and then you can press F5 and then just press Enter and that will take you back to the formula. It can be a really useful technique if someone's given you a new file and you are just sort of figuring out where things are coming from, then Ctrl and the left square bracket is a useful way to follow links. And then pressing F5 and then pressing Enter is a useful way to go back so you can sort of track down where things are coming from.

Now, copying Excel users Ctrl+C and Ctrl+V which works in all of windows, but there is another way to copy if you want to copy from the cell above. Now this copy is a little bit different. What you can do is it's the apostrophe, so hold the Ctrl key down and press the apostrophe. So that's on the left of the Enter key. Now, the apostrophe will copy from above and it copies it exactly. Now in a case of text, that's sort of normal, but in a formula it copies the formula exactly so it doesn't change a reference. So you can copy a formula from the cell above and the formula will be exactly the same.

So even if there was no dollar signs in that formula, the formula that's copied down will be exactly the same as the formula from above. So it gets around the problem of relative references. So a relative reference like A1, when you copy it around the sheet will change based on where you copy it from and to. But with this technique which is Ctrl and the apostrophe, all it does is it just copies the whole formula to the cell below from the cell above.

Jackie Blondell:
If you're enjoying this podcast, you should check out our in-depth business and finance show, INTHEBLACK. Search for INTHEBLACK on your favourite podcast app today.

Neale Blackwood CPA:
Another one that is a useful one is Ctrl and the semicolon, that will enter today's date. It's a data entry so it won't change. If you want the time, just go Ctrl+Shift, which is the colon, and that will enter the time. And again, that doesn't change. If you do want a formula that returns today's date, use =today(), and that will insert today's date. And that's a formula. So when you open the file tomorrow, that'll have that date in there, tomorrow's date. Now Ctrl keys are also sometimes useful in combination. So here's one that I use quite a bit. Ctrl+S, I use, to save the file and there's also a Ctrl+W to close the file. So what you can do is just hold the Ctrl key down, hit the S, and then hit the W. And what that does is that saves the file and closes the file. S and W are right above each other, so they're close and they're also close to the Ctrl key. Ctrl+S, keep holding the Ctrl and then hit W and that will save and close the file. And it doesn't close Excel.

So Excel stays open. There is a table, if you jump on the INTHEBLACK website. There's also a table of some other Ctrl key shortcuts that I've listed there. So you can check that out. There's quite a few Ctrl key shortcuts there. Couple of other ones, Ctrl+A will select the whole range that you are in. Ctrl+D is duplicate, and that will copy down. You can also use Ctrl+D to copy things like charts and images quite quickly. Ctrl+R will copy to the right. Ctrl save... I meant Ctrl+S saves. Ctrl+T I use a lot to create formatted tables. So I'm a big fan of formatted tables and Ctrl+T is the shortcut. There's also Ctrl+Y and Ctrl+Z. So Ctrl+Y is redo and Ctrl+Z is undo. Okay, let's have a think about the Alt key. So the Alt key is on either side of the space bar. It's sort of a throwback to the early days of computing. Before we had a mouse, yes, there were computers that didn't have a mouse, you used the Alt key a lot to access all of the menus.

Pretty much everything was driven by menus, and so you could use the Alt key to access the menu options. So there's still a throwback in Excel with the use of the Alt key. But before we look at that, I'll quickly go through some of the Alt key shortcuts where you hold the Alt key down and press another key. So to insert the Sum function, hold the Alt key down and press the equal sign. So it's just like the auto sum on the ribbon. Visible cells. Now if you've got a range and you've got hidden rows and hidden columns and you only want to copy the visible cells, what you can do is select the range, hold the Alt key down and press the semicolon. And then once you've done that, then you can go Ctrl+C to copy it. So that only copies the visible cells.

When you press Ctrl, you'll see the dotted line actually goes around all of the separate sections and missing out the hidden rows and columns. So that's Alt and the semicolon, and that selects the visible cells. And then you can go Ctrl+C to copy those. There's a brand new shortcut, so if you've got the subscription version of Excel, you can hold the Alt key down and press F12, and that opens up the Power Query editor. So we finally got a shortcut to open up Power Query. So Alt and the F12 key. If you're into macros, ALT+F11 will open up the VBA window. Now, grouping. I'm a big fan of grouping for rows and columns. Grouping is actually a term that's used in a lot of features in Excel. In this case, what I want to do is group rows and columns. And when you do that, you have the ability to hide those rows and columns really easily and quickly with little icons that pop up. And the beauty of it is, those icons also sort of are a visual clue that the rows are either hidden or you could hide them. So I like grouping rows and columns so that you can hide the rows and columns quite quickly. The shortcut to do it to actually apply the grouping is you select the range first and then you hold the shift and the Alt keys down and press the right arrow key. So Shift+Alt right arrow, that applies grouping.

If you want to take it off, just Shift+Alt and the left arrow will take the grouping off for you. Now, what I wanted to look at now was what's called the Alt key sequences. These are key sequences that you press one after the other. You don't hold them down. This is the throwback to the early days of computing. If you press the Alt key, you'll see a whole lot of letters and numbers pop up on the ribbon. Now what that means is you can access those options by pressing whatever the numbers and the keys are. For example, when you press the Alt key, the first nine items on your Quick Access Toolbar have the numbers 1 to 9, which means you can access them really quickly by pressing Alt and one of those numbers. Now the other keys, they'll be on across the top of the ribbon for the tabs. So if you go Alt+H, that will then open up the Home ribbon and then you can see all of the icons will have a number or a letter to access them.

So some of the useful ones. Now again, these keys are all pressed in sequence. You don't hold them down. Some of the ones I use a lot, Alt+WFF. I like the ones that are doubles like an F and an F. So in this case, what that does that applies freeze panes. Freeze panes is how you can stop the rows at the top and the columns on the left from changing as you move around your spreadsheet. So freeze panes is the best way to do that. There is another option that does that, but it has a few issues. So I always use freeze panes to lock in the rows at the top and the columns on the left. By the way, that's nothing to do with the print titles, which is a totally separate issue. So this is only for navigating around the spreadsheet. And also, that one's a toggle.

So that will turn off freeze pans as well as turning it on. So once you apply it, you can then take it off by pressing the same sequence Alt+WFF. Another one I use a lot is the shortcut to remove filters from a filtered list. It's Alt+AC. Again, just pressed in sequence. AC are quite close, so it's quite quick. Another one is pasting values. Alt+HVV will paste values. To open up the queries and connections, which is something else that's associated with Power Query, again, I'm a big fan of Power Query, is Alt+AO will open up the queries and connections. It's called a task pane and it'll appear on the right-hand side of the screen. And another one, this one here is a little bit different in that it's actually a throwback to Excel 2003. Microsoft tends to be backward compatible and there was a shortcut to open up the data validation dialogue. That keyboard shortcut is Alt+DL. So that will open up the data validation dialogue for you.

Okay, so that's enough of the keys. Let's have a look at the mouse. So there's a lot of shortcuts you can do with the mouse, mainly double clicking and right clicking, that sort of thing. Let's have a look at some of those. Double clicking. Things you can double click. You can double click a sheet name and then you can rename it. There's an arrow between the column letters and the row numbers, a double-headed arrow. You can double click that to automatically fit the row or column to whatever's in that particular cell range, whatever you're working with. You can also select multiple columns. Double click one of those double-headed arrows, it's called AutoFit, that will AutoFit everything in those columns.

There's a Format Painter. The Format Painter icon is a little paintbrush. What it allows you to do is to select a range that has a format. Typically, people just single click the Format Painter, and then that allows you to apply that format to another range just by dragging. But you can double click the Format Painter, and that allows you to basically apply the format multiple times. Once you're finished, press the Escape key. When you're using that technique, your cursor will have the paintbrush icon on there, which is a little bit of a warning that you are actually painting a format.

So Escape is the quick way to get out of that one. Escape pretty much gets you out of everything. So the ESC key top left-hand corner of the keyboard is the get out of jail free key. It gets you pretty much out of everything. Okay, so when you point to a cell or select a range, if you point to the border of that cell or range, you get a little arrow icon. Now you can double click that. If you are within, say like a table, you can double click that little icon to take you to the bottom of the list or the top of the list. It depends which border you double click. So it basically goes in the direction of the border that you double click.

So if you click the bottom border, it'll take you down. If you double click the top border, it'll take you up. So you can navigate around within a table very quickly just by double clicking that icon, which is an arrow icon, which you get when you point to the border of a cell. One of the big time savers in double clicking is double clicking the fill handle. Now the fill handle is typically used to copy down and across. It can also copy up and to the left, but basically you usually use it to copy across to the right and down. There is a double click shortcut to copy down.

Again, the icon is a little plus sign. It's on the bottom right-hand corner of a selected cell or range. And if you double click it, it will copy down. So in a big table, it will basically copy all the way down the table. That's a really quick saving with the fill handle. Okay, right clicking. So I've sort of mentioned a couple of right clicks already, but you can right click pretty much anything in Excel. What happens is you get what's called a context-sensitive menu popup. So that menu will be based on whatever you've right clicked. It can actually be an easy way to figure out what something does in Excel if you are learning, because all of the items listed there give you an indication of what you can do to that item. You can right click a column letter and then you get a whole lot of options that are related to columns. Typically, at the top of the menu are the standard type copy options. And then as you go down, it gets more sort of context-sensitive to whatever you've clicked on. So inserting, deleting, for example, those sort of things. So a couple of things that are useful to right click on are the row numbers, the column letters.

I've also mentioned that you can click on the ribbon at the top of the screen to add icons to your Quick Access Toolbar. You can right click a sheet. There's a lot of options there, like hide. You can change the colour. If you right click on the little arrows that are to the left of the very first sheet tab, what you'll get is a list of all of the sheets. Now, it only pops up if there's more than one sheet there. So if you've only got a single sheet in your file, you won't see the list. So you can right click and see the list. And then by clicking on the name, you can actually navigate to that sheet.

Okay, so I've covered a whole lot of shortcuts in this podcast. Hopefully, some of those you can identify as useful. And as I mentioned, the quicker you start using them, the quicker they become second nature and you don't even need to think about them. When you first start using them, you've got to try to remember them, but after a while, they become second nature. So I hope you found that useful, and thanks for listening.

Garreth Hanley:
Thanks for listening to Excel Tips, a CPA Australia Podcast. If you've enjoyed this episode, help others discover the show by leaving us a review or sharing this episode with colleagues, clients, or anyone else looking for the best Excel tips and tricks. To find out more about our other podcasts and CPA Australia, check out the show notes for this episode. We hope you can join us next time for another episode of Excel Tips.