Locked lesson.
About this lesson
Exercise files
Download this lesson’s related exercise files.
01-03-Useful Excel Efficiency Tips and Shortcuts-Start.xlsx10.4 KB 01-03-Useful Excel Efficiency Tips and Shortcuts-Complete.xlsx
10.3 KB 1.03 useful-excel-efficiency-tips-and-shortcuts - Exercise.docx
43.6 KB 1.03 useful-excel-efficiency-tips-and-shortcuts - Exercise solution.docx
81.8 KB Exercise - Useful Excel Efficiency Tips.xlsx
10.7 KB
Quick reference
Useful Excel Efficiency Tips and Shortcuts
Learn some of the most useful shortcuts to work efficiently in Excel.
When to use
We use shortcuts and other efficiency tips when we want to maximize our efficiency and productivity.
Instructions
Excel Shortcuts are awesome!
Simply knowing a few useful keyboard shortcuts, can help us navigate, make selections and execute commands much more efficiently.
Navigation and Selection Shortcuts
CTRL+down arrow | Jump to the bottom of a dataset |
CTRL+up arrow | Jump to the top of a dataset |
CTRL+right arrow | Jump to the right-most cell in the dataset |
CTRL+left arrow | Jump to the left-most cell in the dataset |
CTRL+SHIFT+down arrow | Select all data in a column |
CTRL+SHIFT+right arrow | Select all data in a row |
SHIFT+right arrow | Select additional columns or cells |
CTRL+A | Select all data |
CTRL+Space | Select a column of data |
Insert Columns and Rows
CTRL+SHIFT+(+) | Insert a new column or row (column or row must be selected first). To insert multiple columns or rows, select the number of columns or rows to insert first. |
Keyboard Shortcut Screen Tips
There are hundreds of shortcut keys in Excel. We can see some of them by hovering our mouse over the command on the ribbon. The shortcut will show in brackets.
ALT Key Shortcuts
We can work entirely using keyboard shortcuts using ALT key shortcuts.
- Press the ALT key.
Letters will appear underneath each ribbon. To navigate to the Home tab, we would press H.
The Home tab will show a different set of shortcuts.
- Press O to open the Format menu.
- Press I to AutoFit Column Width.
So simply remembering the keyboard shortcut ALT+HOI will execute Autofit faster than clicking through the menus.
The Quick Access Toolbar
The Quick Access Toolbar or QAT is a small toolbar that sits above or below the ribbons. We can add any command in Excel to the QAT so we don't have to hunt around for it.
- Right-click on any command on any ribbon.
- Click Add to Quick Access Toolbar.
Full List of Keyboard Shortcuts in Excel
We can see a full list of all shortcuts in Excel by pressing F1 to step into the Help Files. Simply search for 'Excel Keyboard Shortcuts'.
Hints & tips
- Remember, to Undo the last actions, press CTRL+Z.
- 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.