Chapter 3. Moving Data Around a Worksheet

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 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: 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.")

There are also 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.

• 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.

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 just outside the top left corner of the worksheet.

• 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, 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").

• 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.

3.1.2. Making Non-Contiguous Selections

In some cases, you might 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-3, isn't highlighted because it 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. This behavior is a little bit different from a continuous selection, in which the first selected cell is always the active cell. With a non-contiguous selection, the last selected cell becomes the active cell.

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:

1. Move to the first cell that you want to select.

   Before continuing, decide which direction you want to extend the selection.

2. 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.)

3. 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. Here are the steps to select a continuous range of cells:

1. 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.

2. 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 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:

1. 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.

2. 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.

3. 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 a non-contiguous selection.

4. 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.

5. 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 Table 1-1.

   ---

   POWER USERS' CLINIC Selecting Cells with the Go To Feature

   In Chapter 1 (on Sidebar 1.3) 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 Edit Go To from the menu, or by clicking Ctrl+G. Type in the address of the bottom-right cell in the selection you want to highlight. Now, here's 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, you'll select a block of six cells: A1, A2, A3, B1, B2, and B3.