4. Essential Shortcuts for Excel Navigation
A few keyboard shortcuts can help you navigate in double-quick time. Here I show you how to use these shortcuts on a sample sales datasheet.
Useful keyboard shortcuts: Worksheet navigation
CTRL + Pg Dn: Move to worksheet on the right
CTRL + Pg Up: Move to worksheet on the left
SHIFT + F11: Create new worksheet
ALT + E, L: Delete worksheet
ALT + O, H, R: Change name of worksheet
Useful keyboard shortcuts: Cell navigation
→: Move to next cell
CTRL + →: Move to edge of data region
HOME: Return to first cell in the selected row
CTRL + HOME: Return to cell A1
Pg Dn: Move down one screen
Pg Up: Move up one screen
ALT + Pg Dn: Move across one screen to the right
ALT + Pg Up: Move across one screen to the left
F5: Show Go-To Dialog box
Most beginners navigate Excel using the mouse. And after all, it's very simple to go to a cell by simply clicking the mouse button.
However, this is a particularly slow way of navigating Excel documents, especially those with large worksheets. A much quicker way to move around Excel is using keyboard shortcuts, and that's what we'll be focusing on in this lesson.
To help us in this task, I have included this sample spreadsheet that can be downloaded underneath this video and then opened in Excel.
data is arranged in one or more worksheets, which together, make up a workbook.
To move to the second sheet of a workbook, I can simply scroll down to the bottom of the page and click the tab. Or a much quicker way is to use a keyboard shortcut such as Ctrl + Pgdown which moves me to the tab on the right-hand side of the currently selected sheet. So to move to Sheet 3, I press Ctrl + Pgdown.
To move back to Sheet 2, I press Ctrl + Pgup, and I press it one more time to get to Sheet 1. To create a new worksheet, I can use another shortcut command and that's Shift + F11. And that immediately creates a new worksheet, Sheet 4, to the left of Sheet 1 which I had previously selected. To delete a worksheet, hold Alt + E and then press L to delete Sheet 4 and bring me back to Sheet 1. If we have many sheets in our workbook, it can be helpful to have more descriptive names than Sheet 1, Sheet 2, and Sheet 3. And so to change the name of a worksheet, we'll use the shortcut Alt + O + H + R and then I'm going to change the name to “Q1_sales data”.
Press Return and now the name of our sheet has changed. All of these keyboard shortcuts might be a little overwhelming at the start, but don't worry about remembering them for now because I’ll include them in the show notes below this video. Let's now move on to navigating within a spreadsheet.
If you're navigating a spreadsheet using a keyboard, the easiest way to do it is simply to use the keyboard arrows. However, this is a pretty slow and tedious way of navigating a spreadsheet. To move a little faster, hold Ctrl and press the keyboard arrow. So when I hold Ctrl and press the right arrow, it moves me to the edge of the data region in the direction that I press. So for example, if I want to move back to the cell that says “Order number”, hold Ctrl and press left arrow.
If I'd like to move down to the number 12, hold Ctrl and press down arrow.
And if I press it again, it moves me to the edge of the next data region. And if I press it one more time, it brings me down to the bottom of the second data region. If I'd like to move back up to the top, I just press Ctrl + up arrow, and then repeat it two more times. Combining Ctrl and the keyboard arrows is a great way of navigating around spreadsheets, particularly financial models, which tend to have many data regions within a single sheet. The next set of navigation shortcuts we'll cover rely on the Home button. If I have a spreadsheet that has many columns, I'll often spend a lot of time scrolling back and forth, left to right. This is where the Home button can become particularly useful. Let's say I'm in AA3 and I'd like to get back to the left-hand side of the page.
Simply press the Home button and it stays in the same row, but brings me back to the first column. What's more, if I press Ctrl and the Home button, it always returns me to cell A1. So if you find yourself far away from the top left-hand corner on a spreadsheet, just press Ctrl and Home and it will bring you back to cell A1 immediately, a very useful command.
Another useful navigation shortcut is the Pgdown key. I currently have rows 1 to 23 showing on my screen.
If I want to view rows 23 to 45, I simply press the Pgdown key. If I'd like to move back up again, I simply press Pgup. We can also move across horizontally one screen by holding Alt and pressing Pgdown, and this shows us columns S to AK. If I'd like to move back to columns A to S, just hold Alt and press Pgup. The last navigation shortcut I'd like to show you is the Go To dialog box, which can be accessed by pressing F5 at the top of your keyboard.
If you'd like to navigate to a cell, let's say for example D300, click OK and this automatically brings us to that cell on the selected worksheet. The Go To dialog box is very powerful because you can access any cell in any worksheet immediately. So for example, if I'd like to go to cell D300 on Sheet 3, I'll press F5, I'll type in Sheet 3 followed by an exclamation mark, then D300 and click OK. And this brings us to cell D300 on Sheet 3 immediately. The Go To dialog box is a great shortcut command, particularly if you've got large data sets spread over a number of data sheets. Let's now move back to our initial sheet by pressing Ctrl + Pgup twice and then Ctrl + Home to move back to the top of the page. Although there are many more navigation commands available in Excel, I find the few I've shown you in this lesson will cover almost all of your needs. To get some practice using these shortcuts, be sure to download the Excel file underneath this video and try navigating around the data set without using the mouse.