Copy cell content

copy cell content

In the preceding lesson, you learned about 3D references in worksheets, which enable you to work with data among multiple sheets in a workbook. Now you're no longer limited to two dimensions!

In this lesson, you learn about copying data and formulas in a workbook, and the consequences of doing so in various ways. This can be a challenging topic, not so much because of the actions you take in Excel, but because of the thinking involved in knowing what actions to take. Make sure you follow along with all the examples in this lesson, and don't hesitate to ask questions on the class's Message Board.

The traditional way of copying data is with the Copy and Paste commands. It's quick, especially when you use shortcut keys (Ctrl+C for Copy and Ctrl+V for Paste). You can use the Copy and Paste commands from the Edit menu, the Copy and Paste buttons on the toolbar, or the shortcut key methods.

Copying data this way always results in an exact copy of any data (rather than a filled series, as explained later); copying formulas this way changes any cell references in the formulas to be relative to the new location (more about this in a moment).

Another quick way of copying is with drag-and-drop. Drag-and-drop typically moves things, rather than copies them, but hold down the Ctrl key while you drag to make a copy.

use AutoFill

AutoFill provides a fast way of putting repetitive data into cells. This data can consist of the same value in every cell, or it can consist of a series.

A fill series increments in a predictable way, such as counting by 1s (1, 2, 3, 4), counting by 3s (1, 4, 7, 10), listing the days of the week (Monday, Tuesday, Wednesday), or some other value that has a regular interval.

The AutoFill handle is the black square in the bottom right corner of a selected cell or range. Dragging that black square fills adjacent cells with the AutoFill content.

Depending on what you're filling, you'll get different results if you select a single cell and then drag the AutoFill handle. That's because Excel tries to guess what you want:

• If the cell contains a word in a defined AutoFill series, such as Monday, dragging fills adjacent cells with that series.
• If the cell contains a numeric value, dragging fills adjacent cells with that same numeric value.
• If the cell contains text that is not part of a defined AutoFill series -- Steve, for example -- dragging fills adjacent cells with that word.
• If the cell contains a formula that refers to other cells by their addresses, dragging creates copies of it in adjacent cells with the cell references changed to reflect the new position. For example, if cell B3 contains the formula =B1+B2, and you AutoFill that formula into C3, the formula changes to =C1+C2.

Here are some examples to try:

1. Enter your name in a cell, and then drag the AutoFill handle.
2. Enter Monday in a cell, and then drag the AutoFill handle. Then try it with January instead.
3. Enter 1002 in a cell, and then drag the AutoFill handle.
4. Enter 1002 in one cell, and then enter in an adjacent cell. Select both cells, and then drag the AutoFill handle.
5. Enter A505 in one cell, and enter A510 in an adjacent cell. Select both cells, and then drag the AutoFill handle.
6. Enter 01/15/2003 in one cell, and in an adjacent cell. Select both cells, and then drag the AutoFill handle.
7. Start a new worksheet, and enter the data shown in Figure 3-1. Then in cell D2, enter a formula that sums B2:C2.
8. Select cell D2 and drag its AutoFill handle down to cells D3 through D5. Then check out the formulas that appear in those cells.

Figure 3-1: Use this data to practice copying formulas with AutoFill.