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 cell's contents, formatting the cells, or pasting new values into the selected cells. Figure 3-1. Top: The thick black border tells you that you've selected the cells A1, B1, and C1.
Bottom: Notice that Excel doesn't highlight the first cell selected. Why? To remind you that it's the active cell: if 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, on the Formatting toolbar, click the bold "B.")
Note: If you select some cells and then press an arrow key or click into another cell before you perform any action, Excel clears your selection.
Here are a few useful shortcuts for making continuous range selections (some of these shortcuts 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. If you want to select an entire column, click the header at the top of the column. For example, if you want to select the second column, click the gray "B" box above the column. Excel selects all the cells in this column, right down to row 65536. 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 are selected, right through to column IV. 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").
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 worksheet, click the empty gray square at the top-left corner of the worksheet. 
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.
Warning: When selecting multiple rows or columns, make sure that you click inside the column header's left and right edges, not on either edge. If you click the edge of the column header, you'll end up resizing the column instead of making a selection.
3.1.2. Making Non-Contiguous Selections In some cases, you may want to select cells that are non-contiguous (also known as nonadjacent or not-next-to-each-other), which means they don't form a neat rectangle. For example, you may want to select columns A and C, but not column B. Or, you may 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 covered 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-3, isn't highlighted because it becomes the active cell).
Note: Excel displays continuous and non-contiguous selections a little differently. In a continuous selection, the first selected cell is always the active cell. With a non-contiguous selection, the last selected cell becomes the active cell.
Figure 3-3. This figure shows a non-contiguous selection that includes four cells (A1, B2, C3, and B4). The last selected cell (B4) isn't highlighted because it's the active cell. (If this were a continuous selection, the first cellnot the last cellwould be 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. Hold down Shift. Now, double-click whichever edge of the active cell that corresponds to the direction you want to AutoSelect. For example, if you want to select the cells below the active cell, 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 lets you use the keyboard to select cells in a worksheet. Here are the steps to select a continuous range of cells: 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 rows), using the arrow keys. As you move, the selection will grow. 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 letters EXT in the Status bar, and Excel selects cells as you move, 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 letters EXT 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. If 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 one giant 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'll see the word ADD 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, just 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 will release 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, if you hold down Shift and then press Ctrl+  , youll automatically jump to the last occupied cell with all the cells in between selected. For more information about the shortcut keys, refer to Table 1-1 on Section 1.3.
GEM IN THE ROUGH
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 it says Sum= (shown here). That's the sum of the cells you've selected. You can also instantly perform several other calculations; first select your cells, then 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).
- Count Nums. The number of selected cells that contain numbers or dates.
- Min. The selected number or date with the smallest value (for dates, this means the earliest date).
- Max. 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, don't bother: adding dates together generates meaningless results.
Not surprisingly, 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 on Section 4.1.1.7and 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.  |
|
