Chapter 3. Moving Data Around a Worksheet
Simple spreadsheets are a good way to get a handle on Excel. But in the real world, you often need a spreadsheet that's more sophisticatedone that can grow and change as you start to track more information. For example, on the expenses worksheet you created in Chapter 1, perhaps you'd like to add information about which stores you've been shopping in. Or maybe you'd like to swap the order in which your
This chapter covers the basics of spreadsheet modification, including how to select
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.
Simplest of all is selecting a
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
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
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 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.
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
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 Sidebar 2.1and 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.
In some cases, you might want to select cells that are
(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
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
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
The mouse can be an intuitive way to navigate around a worksheet and select cells. It can also be a tremendous time-suck,
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:
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 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
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.
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
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.
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,
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.