Useful Excel Efficiency Tips and Shortcuts

Quick reference

• 00:04 I thought it'd be really useful to start out our journey through this course
• 00:09 just by adding in a quick lesson on some really useful Excel shortcuts and
• 00:15 efficiency tips.
• 00:16 Because just knowing a few of these is going to make your life so
• 00:20 much easier if you are a heavy Excel user.
• 00:23 So if you take a look at the worksheet on the screen,
• 00:26 I kind of have this very colorful little table just here.
• 00:29 Now, just make a note, this isn't actually an Excel table,
• 00:33 I've just added some values and applied some formatting.
• 00:36 But this is going to be useful to demonstrate some of the keyboard
• 00:40 shortcuts that I personally use all of the time.
• 00:42 I'm going to start out by taking a look at some navigation shortcuts to help us move
• 00:46 around our spreadsheet a little bit more efficiently.
• 00:49 So if I click in the first cell here, B7, what do I need to press if I want
• 00:54 to jump all the way down to the bottom of a long data set?
• 00:58 Well, we can press Ctrl+down arrow, it's going to jump our cursor down to
• 01:03 the last row in our dataset, really useful if you have thousands and
• 01:07 thousands of rows, Ctrl+up arrow will jump us to the top.
• 01:10 Similarly, Ctrl+right arrow is going to jump us to the rightmost cell,
• 01:15 Ctrl+left arrow is going to jump us back.
• 01:18 Now what about if we want to actually select all of the data In the column that
• 01:22 we're currently clicked in?
• 01:24 Well we can use pretty much exactly the same shortcut but we need to add in
• 01:29 the Shift button, so Ctrl+Shift+down arrow is going to select all of that column.
• 01:34 If I now want to highlight the next two columns as well,
• 01:38 I just need to hold down Shift and use my right arrow key to do that.
• 01:43 If I want to make noncontiguous selections, and
• 01:45 what we mean by that are selections that aren't necessarily next to each other,
• 01:50 I can keep these selected, hold down Ctrl, and then I can select other columns.
• 01:55 And we can do exactly the same thing when it comes to rows.
• 01:58 I can press Ctrl+Shift+right arrow to select the entire row,
• 02:02 Shift+down arrow to select as many rows as I want.
• 02:05 And if I want to select noncontiguous rows, I can simply hold down Ctrl and
• 02:10 go away and start making additional selections.
• 02:13 And of course, one of the most useful shortcuts is Select All.
• 02:18 So if you're clicked in a dataset and you just want to select absolutely everything,
• 02:23 Ctrl+A is going to do that for you.
• 02:24 Now what about when it comes to selecting full columns?
• 02:28 Well we know that if we hover our mouse over the column letter, so for
• 02:32 example column M, I can click once to select the entire column.
• 02:36 I can also click in the column, press Ctrl+Space, and
• 02:40 that's also going to select the entire column.
• 02:43 Now what about if I decide that I now want to add some additional columns
• 02:48 into my dataset?
• 02:49 Well there's a couple of ways that we can do this, and
• 02:51 it really depends on how many columns you want to insert.
• 02:54 If I just want to insert one blank column, I can simply select the column and
• 02:58 press Ctrl+Shift+plus and
• 03:00 that's going to give me a new blank column wherever I've clicked.
• 03:03 Note that on some keyboards it's Ctrl+plus without the Shift.
• 03:08 But what about if I want to insert multiple,
• 03:10 do I have to keep doing that every single time, well the answer is no.
• 03:15 If I want to insert three blank columns, I can simply select three columns,
• 03:20 Ctrl+Shift+plus or Ctrl+plus to insert those.
• 03:24 Ctrl+Z will undo your last action.
• 03:28 And, of course, this works exactly the same for rows.
• 03:31 I could select four or five rows, Ctrl+Shift+plus, and
• 03:35 it's going to insert the same number of blank rows, Ctrl+Z to undo.
• 03:40 Now when it comes to the hundreds and
• 03:42 hundreds of other shortcuts that are available in Excel,
• 03:46 you'll probably find yourself using around 10 to 15 on a daily basis.
• 03:50 Now we don't have time to go through all of them in this particular lesson, but
• 03:54 just a little tip for you.
• 03:56 If you want to find out what the shortcut is for a particular command on the ribbon,
• 04:01 many of them have them listed out in the screen tip.
• 04:05 So if I was to hover my mouse over Cut, you can see it has Ctrl+X in brackets.
• 04:11 The same thing here with Copy, we have Ctrl+C in brackets.
• 04:15 And if you're somebody who really loves their keyboard shortcuts,
• 04:18 we could use Alt key shortcuts in order to move around our spreadsheet,
• 04:22 execute commands without ever having to touch the mouse.
• 04:26 So maybe I want to AutoFit all of the column widths so
• 04:29 that they're exactly the width of the value within the cell.
• 04:33 What I could do here is press Ctrl+A to select all of my data, press the Alt key
• 04:38 which is going to pull up all of these keyboard shortcuts on the ribbons.
• 04:42 So I can then go to the Home ribbon by pressing H.
• 04:46 I know that the AutoFit command is underneath Format in that Cells group so
• 04:51 I can then press O, and then I can choose AutoFit Column Width by pressing I.
• 04:57 And that's going to AutoFit all of those cells and
• 05:00 I haven't had to touch the mouse at all.
• 05:02 So you can use those little key tips to move around and
• 05:05 execute commands really effectively.
• 05:07 Another thing I would highly recommend you do is that you customize your
• 05:11 Quick Access Toolbar.
• 05:12 Now if you're wondering what that is,
• 05:14 it's this little toolbar that runs underneath your ribbons.
• 05:18 Now yours might look a lot different to mine,
• 05:20 I've already got some customizations applied.
• 05:23 And you might not even see it underneath your ribbons,
• 05:27 you might have it showing above the ribbon so it might be somewhere up here.
• 05:32 Now what this is is basically just an area, a little toolbar where we can add
• 05:36 any command that we use frequently so that it's easy to access, and
• 05:40 you can pretty much add any command that you can see on the ribbon.
• 05:44 So if I go across to the Data tab for example,
• 05:47 maybe I find myself sorting data a lot.
• 05:50 What I could do is right-click on the Sort command and say Add to
• 05:55 Quick Access Toolbar, and you can see now I have the Sort icon just here.
• 06:00 So one of my recommendations is to go through and
• 06:02 right-click > Add to Quick Access Toolbar all of the commands that you think you're
• 06:07 going to use frequently so you're not having to hunt around the ribbons.
• 06:11 And remember, if you click the little drop-down at the end
• 06:14 you can choose to show this above or below that ribbon.
• 06:17 And if you want to see a full list of all of the keyboard shortcuts
• 06:21 that are available in Excel,
• 06:22 you can always press the F1 key to jump into the help files.
• 06:26 And simply type in keyboard shortcuts Excel, select the correct link, and
• 06:30 then if we scroll down we're going to get different categories.
• 06:34 So we can see our frequently used shortcuts, our ribbon keyboard shortcuts,
• 06:38 so on and so forth, shortcuts for navigating in cells.
• 06:42 And if you click on these links,
• 06:44 you're going to get a big long list of all of the different shortcuts that you can
• 06:48 use to execute commands more efficiently when you're working in Excel.

Lesson notes are only available for subscribers.