Selecting cells
One of the commonest actions in Excel is to select a group of cells arranged in a rectangle on a worksheet.
By using the mouse |
By using the mouse |
By using the keyboard |
Everyone knows this way |
Not so many people know this way |
Not so many people know this way |
Click on a cell in one corner of the rectangle |
Click on a cell in one corner of the rectangle |
Use the arrow keys to navigate to a cell in one corner of the rectangle |
Hold down the left mouse button |
Hold down the Shift key |
Hold down the Shift key |
Click on the cell at the diagonally opposite corner of the rectangle |
Click on the cell at the diagonally opposite corner of the rectangle |
Use the arrow keys to navigate to the cell at the diagonally opposite corner of the rectangle using the arrow keys |
Let go of the mouse button. |
Let go of the Shift key |
Let go of the Shift key |
Deselecting cells
Once you have made a selection of some cells on a worksheet, how can you get rid of the selection? You can deselect the cells in two ways
| • | with the mouse: click on any single cell |
| • | with the keyboard: press any of the arrow keys |
The rule is simple: when you move about the spreadsheet, either by clicking on a cell or using the arrow keys on the keyboard,
| • | while holding down the mouse button or the Shift key: Excel selects any cells that you pass over |
| • | without holding down the mouse button or the Shift key: Excel deselects any previously selected cells. |
MORE ELABORATE SELECTING
As we've just learned, when you move about the spreadsheet without holding down the mouse button or the Shift key, Excel deselects any previously selected cells.
But there are ways to stop that happening.
| • | With the mouse: Select some cells. Hold the Ctrl key down. Select some other cells. |
| • | With the keyboard or the mouse: Select some cells. Press Shift+F8. Select some other cells. |
In both cases, the second group of cells is not selected instead of the first. Instead, the second is selected as well as the first.
This process can be repeated as necessary to select up multi-area ranges of arbitrary shape.
WHY THIS IS USEFUL
In cell A1 on a blank workbook, enter a formula =C3+C4+D6+E6+F8+F9
Now, while that cell is selected, press Ctrl+[. (Hold the Ctrl key down, and press the left square bracket.) Excel will change the selection from A1 to those cells mentioned in the formula. This is because pressing Ctrl+[ is one way to find the precedents of a cell.
The cells mentioned in the formula don't lie in a simple rectangle, but that presents no obstacle to Excel in selecting them. Excel simply selects one of the multi-area ranges that we have just learned about.
There are several ways to find the precedents of a cell. Ctrl+[ is just one of them. Another is to use Go To Special.
| • | Select cell A1, the one in which you have put the formula. |
| • | Press F5, the short cut for Go To |
| • | In the first dialog that appears, press the Special button in the bottom left corner. |
| • | In the second dialog that appears, select Precedents |
| • | Click OK or press Enter. |
The cells mentioned in the formula will be selected, as before.
Finding a cell's precedents is just one of the options offered by the Go To Special dialog. They are basic tools in spotting blemishes in spreadsheets. With just a couple of exceptions, all of them will select a multi-area range when appropriate. (The exceptions find Comments and Objects, which float over the grid of cells, so multi-area ranges are not relevant.) In this way Excel's ability to select ranges of arbitrary shape underpins the facilities it provides for checking spreadsheets.
The active cell
Clicking on a cell, or navigating to it using the arrow keys, instructs Excel to make that cell the Active Cell. It is the contents of the Active Cell that are displayed in the Formula Bar above the worksheet, and it is the cell address of the Active Cell that is displayed in what Microsoft calls the Name Box, to the left of the Formula Bar.
If you select many cells on a worksheet, the Active Cell will be among them. It is differently highlighted from the other cells.
Moving the active CELL
Once Excel's Go To Special command has selected cells of a particular kind, the commonest next step is to examine the contents of those cells. However Excel only displays in the Formula Bar the contents of one cell, the Active Cell. And as we have already learned, when you move about the spreadsheet without holding down the mouse button or the Shift key, Excel deselects any previously selected cells.
So any attempt to navigate around the worksheet, to inspect cells within the selection other than the currently Active Cell, risks losing that selection.
The trick for resolving this conundrum is to press the Tab key. The Tab key will visit each cell within a selection in turn, and it will do it while keeping the relevant cells selected. Shift+Tab does the same thing, but travels in the opposite direction.
In fact there are seven key combinations that move the active cell within a range.
| • | Tab, as we have just seen, moves from left to right within the selection |
| • | Shift+Tab, moves from right to left within the selection |
| • | Enter, moves downwards within the selection |
| • | Shift+Enter, moves upwards within the selection |
| • | Ctrl+. (full stop, or period), moves clockwise among the corners of the area containing the Active Cell |
| • | Ctrl+Alt+Right moves to the top left corner of the next area of a multi-area selection |
| • | Ctrl+Alt+Left moves to the top left corner of the next area of a multi-area selection. |
Why this is useful
Excel's Go To | Special command can be instructed to identify interesting cells, and these can then be visited in turn by pressing the Tab key, and their contents examined. This is a basic technique in examining a spreadsheet.
OAK's Search Selection commands mimic this behaviour.