There are two keys on our keyboard that aid in navigation and selection: CTRL and Shift.
- The CTRL key tells Excel to move the cursor (the green box) to the end of the currently selected data set.
- The Shift key tells excel to highlight cells.
When used with the arrow keys, we can perform very rapid movements and highlights.
Using the CTRL Key
If you have a large data set, using CTRL-↓ will place your cursor on the last row. Using CTRL-→ will place your cursor in the last (right-most) column.
The opposite is true. CTRL-↑ moves you to the top row and CTRL-← moves you to the first (left-most) column.
Excel stops the cursor when it encounters the first blank cell or sheet edge, whichever comes first.
Working with Multiple Tables
If you have several tables separated by blank rows/columns, repeated pressing of the CTRL-arrows would “walk” you through the tables.
- CTRL-↓ to move to the last row in the first table.
- CTRL-↓ to move to the first row of the second table.
- CTRL-↓ to move to the last row in the second table.
- CTRL-↓ to move to the first row of the third table.
- etc, etc, etc…
This assumes you don’t have any blank cells in your table(s).
Using the SHIFT Key
The Shift key is how we tell Excel to highlight.
If we hold the Shift key and repeatedly press one of the arrow keys, we will select cells as we move.
Using CTRL and Shift at the Same Time
By pressing CTRL-Shift and then using the arrow keys we are telling Excel to “move to the end and highlight along the way”.
It’s a fast and easy way to select a column or row of data.
You could even select an entire table using these keys.
- Select the upper-left cell of the table
- Press CTRL–Shift-↓
- Keep pressing CTRL–Shift
- Press →
This is good practice to get your finger acrobatics skills perfected.
NOTE: A simpler way to select a table is to click in the table and press CTRL-A.
Moving to the Last Used Cell
Additional navigation shortcut keys are the END and HOME keys.
- CTRL-End will place the cursor in the last used cell.
- CTRL-Home will place the cursor in cell A1.
If we start our table in cell A1, we could place our cursor in cell A1 and press CTRL-Shift-End to select the table.
NOTE: There is a slight catch to using the CTRL-End keyboard shortcut. This will be discussed at the end of the post.
Encountering Data Speed Bumps
Take a look at the data set below.
Our goal is to highlight the Region data in column B.
The problem is there are empty cells scattered throughout the column. If we try to use our CTRL–Shift-↓ trick, we are prematurely halted during our selection process.
Granted, we could keep repeatedly hitting CTRL–Shift-↓, but with dozens or hundreds of empty cells, we could be pressing keys for hours. It would be faster to highlight the cells with a mouse.
The Trick to Selecting with Blanks
The trick to selecting a set of cells that contain blanks is to over-select.
Over-selecting is where you select more cells that you need, then reduce the selection to what you originally wanted.
There isn’t a keyboard shortcut for this over-selection act, so we’ll use the Name Box.
The Name Box has many useful features:
- Tell us the cell address for the currently selected cell
- Navigate to a cell by typing the cell address in the Name Box and pressing Enter
- Select a range of cells by typing the range in the Name Box and pressing Enter (e. A1:F100)
- Apply a name to a cell (like naming cell A1 “Tax Rate”) for use in formulas
We will type an address to select a range of cells.
- Click in the Name Box; the current cell address will turn blue.
- Erase the current address and type the address range you want to highlight (e. B1:B10000). Select an ending cell address that you know will place you well beyond your last row of data. Any value will do as long as it is past your data.
- Press Enter.
- With the range selected, press CTRL-Shift-↑.
That’s it! You have over-selected with the Name Box then reduced the selection to the last row of your data using the CTRL–Shift-↑ keyboard shortcut.
Another Way to Over-Select Cells
If you don’t want to use the Name Box for range selection, you can also use the “Go To” dialog box.
Press CTRL-G to launch the “Go To” dialog box.
In the Reference field, you can type the same cell range reference you previously typed in the Name Box.
The Problem with CTRL-End
I mentioned earlier that the CTRL-End keyboard shortcut has a small catch. The catch is that CTRL-End places the cursor in the last used cell. That sounds like what we want, but Excel isn’t thinking “last used” the way you are.
We think of “last used” as the “last cell holding data”. Excel thinks of “last used” as the “last cell that ever had data, even if that data has been deleted.”
This can cause navigation issues.
Imagine a data set where you have placed data far to the right or below your table.
If you press CTRL-End, the cursor is placed in the last used cell, not the bottom-right corner of your data.
If we delete the renegade data outside of the table, we will think we have solved our problem; we haven’t.
The cursor is still placed in the cell that held the renegade data because Excel remembers that we have visited this place before.
This can be very frustrating for users who never saw the original renegade data and wonder why the CTRL-End shortcut is throwing their cursor into no-man’s land.
Resetting the End of Data Range
Using the above data set as an example, to get Excel to ignore the blank rows and columns where data used to exist, perform the following steps:
- Select the range of columns where data used to exist (e. E1:Z1) and delete the selected cell columns.
- Select the range of rows where data used to exist (e. A11:A100) and delete the selected cell rows.
At this point, you think you’ve won, but a quick retry of the CTRL-End trick shows you have yet to succeed. Here are the steps that users don’t think to perform.
- Save the workbook.
- Close and reopen the workbook.
If we press CTRL-End the cursor is now resting in the last occupied cell (lower-right corner).
It’s the saving/closing/re-opening that resets Excel’s historical perspective.
Addendum
Due to several suggestions made after the publication of the above video/blog, I want to show you additional ways to achieve the same result when working with data that may have different structures or layouts then was demonstrated.
Extra Method #1
Here are the parameters:
- The data begins in row 1
- The data is not in a proper Data Table format, rather a plain table
- Select the first cell (row 1) of the column you are attempting to highlight
- Press CTRL-Space to select the entire column, all one million-plus rows
- Press CTRL-Shift-↑
Extra Method #2
Using the same parameters as Method #1:
- The data begins in row 1
- The data is not in a proper Data Table format, rather a plain table
- Select the first cell (row 1) of the column you are attempting to highlight
- Press CTRL-End to select to the last used cell in the sheet
- Repeatedly press Shift-← to move left to the originally selected column
Extra Method #3
Here are the parameters:
- The data does not begin in row 1
- The data is not in a proper Data Table format, rather a plain table
- Select the first cell in the data of the column you are attempting to highlight (typically the header)
- Press CTRL-Space to select the entire column, all one million-plus rows
- Press CTRL-Shift-↑
- Press CTRL-. (period) to move the selected cell to the last highlighted cell
- Press CTRL-Shift-↓
Extra Method #4
Here are the parameters:
- The data is in a proper Data Table format
- Select the first cell in the data of the column you are attempting to highlight (typically the header)
- Press CTRL-Space
Because the data is in a proper Data Table format, the highlight is restricted to the table’s column boundaries.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
