3.1. Selecting Cells
First things first: before you can make any changes to an existing worksheet, you need to select the cells you want to modify. Happily, selecting cells in Exceltry saying that five times fastis easy. You can do it many different ways, and it's worth learning them all. Different selection techniques come in handy in different situations, and if you master all of them in conjunction with the formatting features described in Chapter 4, you'll be able to transform the look of any worksheet in seconds.
3.1.1. Making Continuous Range Selections
Simplest of all is selecting a continuous range of cells. A continuous range is a block of cells that has the shape of a rectangle (high school math reminder: a square is a kind of rectangle), as shown in Figure 3-1. The easiest way to select a continuous range is to click the top-left cell you want to select. Then drag to the right (to select more columns ) or down (to select more rows). As you go, Excel highlights the selected cells in blue. Once you've highlighted all the cells you want, release the mouse button. Now you can perform an action, like copying the cells' contents, formatting the cells, or pasting new values into the selected cells.
| || |
Figure 3-1. Top: The three selected cells (A1, B1, and C1) cover the column titles.
Bottom: This selection covers the nine cells that make up the rest of the worksheet. Notice that Excel doesn't highlight the first cell you select. In fact, Excel knows you've selected it (as you can see by the thick black border that surrounds it), but it has a white background to indicate that it's the active cell: When you start typing, Excel inserts your text in this cell.
In the simple expense worksheet from Chapter 1, for example, you could first select the cells in the top row and then apply bold formatting to make the column titles stand out. (Once you've selected the top three cells, press Ctrl+B, or chose Home Font Bold.)
Note: When you select some cells and then press an arrow key or click into another cell before you perform any action, Excel clears your selection.
You have a few useful shortcuts for making continuous range selections (some of these are illustrated in Figure 3-2):
Instead of clicking and dragging to select a range, you can use a two-step technique. First, click the top-left cell. Then hold down Shift and click the cell at the bottom-right corner of the area you want to select. Excel highlights all the cells in between automatically. This technique works even if both cells aren't visible at the same time; just scroll to the second cell using the scroll bars, and make sure you don't click any other cell on your way there.
If you want to select an entire column, click the header at the top of the column (as shown in Figure 3-2). For example, if you want to select the second column, then click the gray "B" box above the column. Excel selects all the cells in this column, right down to row 1,048,576.
| || |
Figure 3-2. Top: Click a column header to select that entire column.
Middle: Click a row number to select that entire row.
Bottom: To select every cell in the just outside the top-left corner of the worksheet (circled).
(In all these figures, the ribbon is collapsed to give more room, as described in Section 1.3.1.)
If you want to select an entire row, click the numbered row header on the left edge of the row. For example, you can select the second row by clicking the gray "2" box to the left of the row. All the columns in this row will be selected.
If you want to select multiple adjacent columns, click the leftmost column header, and then drag to the right until all the columns you want are selected. As you drag, a tooltip appears indicating how many columns you've selected. For example, if you've selected three columns, you'll see a tooltip with the text 3C (C stands for "column").
If you want to select multiple adjacent rows, click the topmost row header and then drag down until all the rows you want are selected. As you drag, a tooltip appears indicating how many rows you've selected. For example, if you've selected two rows, you'll see a tooltip with the text 2R (R stands for "row").
If you want to select all the cells in the entire worksheet, click the blank gray box that's just outside the top-left corner of the worksheet. This box is immediately to the left of the column headers and just above the row headers.
Tip: When you're selecting multiple rows or columns, make sure you click between the column header's left and right edges, not on either edge. When you click the edge of the column header, you end up resizing the column instead of making a selection.
| TIME-SAVING TIP |
A Truly Great Calculation Trick
Excel provides a seriously nifty calculation tool in the status bar. Just select two or more cells, and look down to the status bar where you'll see the number of cells you've selected (the count), along with their sum and their average (shown in Figure 3-3).
To choose what calculations appear in the status bar, right-click anywhere on the status bar, and then, in the menu that appears, choose one of the following options:
Average . The average of all the selected numbers or dates.
Count . The number of selected cells (including any cells with text in them).
Numerical Count . The number of selected cells that contain numbers or dates.
Minimum . The selected number or date with the smallest value (for dates this means the earliest date).
Maximum . The selected number or date with the largest value (for dates this means the latest date).
Sum . The sum of all selected numbers. Although you can use Sum with date values, because of the way Excel stores date values, adding dates together generates meaningless results.
Most of the status bar calculations don't work properly if you select both date and numeric information. For example, when you're attempting to add up a list of numbers and dates, Excel computes the value using both date valueswhich it stores internally as numbers, as explained in Section 2.2and the ordinary numbers; Excel then displays the final count using the formatting of the first selected cell. That adds up, alas, to a number that doesn't really mean anything.
| || |
Figure 3-3. The nicest detail about the status bar's quick calculations is that you can mix-and-match several at a time. Here, you see the count, average, and sum of the selected cells.
3.1.2. Making Non-Contiguous Selections
In some cases, you may want to select cells that are non-contiguous (also known as nonadjacent), which means they don't form a neat rectangle. For example, you might want to select columns A and C, but not column B. Or, you might want to select a handful of cells scattered throughout the worksheet.
The trick to non-contiguous cell selection is using the Ctrl key. All you need to do is select the cells you want while holding down Ctrl. You can select individual cells by Ctrl-clicking them, or you can select multiple blocks of cells on different parts of the sheet by clicking and dragging in several different places while holding down Ctrl. You can also combine the Ctrl key with any of the shortcuts discussed earlier to select entire columns or rows as a part of your selection. Excel highlights in blue the cells you select (except for the last cell selected, which, as shown in Figure 3-4, isn't highlighted because it becomes the active cell).
Note: Excel restricts what you can do with non-contiguous selections. For example, you can format the cells in a non-contiguous selection, but you can't cut or copy the selection.
3.1.3. Automatically Selecting Your Data
Excel provides a nifty shortcut that can help you select a series of cells without dragging or Shift-clicking anything. It's called AutoSelect, and its special power is to select all the data values in a given row or column until it encounters an empty cell.
To use AutoSelect, follow these steps:
Move to the first cell that you want to select .
Before continuing, decide which direction you want to extend the selection.
| || |
Figure 3-4. This figure shows a noncontiguous selection that includes four cells (A1, B2, C3, and B4). The last selected cell (B4) isn't highlighted because it's the active cell. This behavior is a little bit different from a continuous selection, in which the first selected cell is always the active cell. With a noncontiguous selection, the last selected cell becomes the active cell.
Hold down Shift. Now, double-click whichever edge of the active cell corresponds to the direction you want to AutoSelect .
For example, if you want to select the cells below the active cell, then double-click its bottom edge. (You'll know you're in the right place when the mouse pointer changes to a four-way arrow.)
Excel completes your selection automatically .
AutoSelection selects every cell in the direction you choose until it reaches the first blank cell. The blank cell (and any cells beyond it) won't be selected.
3.1.4. Making Selections with the Keyboard
The mouse can be an intuitive way to navigate around a worksheet and select cells. It can also be a tremendous time-suck, especially for nimble -fingered typists who've grown fond of the keyboard shortcuts that let them speed through actions in other programs.
Fortunately, Excel is ready to let you use the keyboard to select cells in a worksheet. Just follow these steps:
Start by moving to the first cell you want to select .
Whichever cell you begin on becomes the anchor point from which your selected area grows. Think of this cell as the corner of a rectangle you're about to draw.
Now, hold down Shift, and move to the right or left (to select columns) and down or up (to select more rows), using the arrow keys .
As you move, the selection grows. Instead of holding down Shift, you can also just press F8 once, which turns on extend mode. When extend mode is on, you'll see the text Extend Selection in the status bar. As you move, Excel selects cells just as though you were holding down Shift. You can turn off extend mode by pressing F8 once you've finished marking your range.
Making a non-contiguous selection is almost as easy. The trick is you need to switch between extend mode and another mode called add mode. Just follow these steps:
Move to the first cell you want to select .
You can add cells to a non-contiguous range one at a time, or by adding multiple continuous ranges. Either way, you start with the first cell you want to select.
Press F8 .
This key turns on extend mode. You'll see the text Extend Selection appear in the Status bar to let you know extend mode is turned on.
If you want to select more than one cell, use the arrow keys to extend your selection .
If you just want to select the currently active cell, do nothing; you're ready to go onto the next step. When you want to add a whole block of cells, you can mark out your selection now. Remember, at this point you're still selecting a continuous range. In the steps that follow you can add several distinct continuous ranges to make a non-contiguous selection.
Press Shift+F8 to add the highlighted cells to your non-contiguous range .
When you hit Shift+F8, you switch to add mode, and you see the text "Add to Selection" appear in the status bar.
You now have two choices: You can repeat steps 1 to 4 to add more cells to your selection; or, you can perform an action with the current selection, like applying new formatting .
You can repeat steps 1 to 4 as many times as you need to add more groups of cells to your non-contiguous range. These new cells (either individuals or groups) don't need to be near each other or in any way connected to the other cells you've selected. If you change your mind, and decide you don't want to do anything with your selection after all, press F8 twiceonce to move back into extend mode, and then again to return to normal mode. Now, the next time you press an arrow key, Excel releases the current selection.
Tip: You can also use the keyboard to activate AutoSelect. Just hold down the Shift key, and use one of the shortcut key combinations that automatically jumps over a range of cells. For example, when you hold down Shift and then press Ctrl+ , youll automatically jump to the last occupied cell in the current row with all the cells in between selected. For more information about the shortcut keys, refer to Table 1-1 in Section 1.3.
| POWER USERS' CLINIC |
Selecting Cells with the Go To Feature
In Chapter 1 (in Section 1.3.1) you learned how you could use the Go To feature to jump from one position in a cell to another. A little known Excel secret also allows you to use the Go To feature to select a range of cells.
It works like this: Start off at the top-left cell of the range you want to select. Then, open the Go To window by selecting Home Editing Find & Select Go To, or by pressing Ctrl+G. Type in the address of the bottom-right cell in the selection you want to highlight. Now, heres the secret: Hold down Shift when you click the OK button. This action tells Excel to select the range of cells as it moves to the new cell.
For example, if you start in cell A1, and use the Go To window to jump to B3, then you'll select a block of six cells: A1, A2, A3, B1, B2, and B3.