Spreadsheets

Unit 2: Creating More Effective Spreadsheets

Lesson 6: Working With Formulas and Functions

How do you copy labels, values, and formulas?

You'll often find that multiple cells need to contain the same, or nearly the same, formulas.

Rather than retyping each formula, you can easily copy the contents of one cell into other cells. Variants on the basic cut-and-paste technique are powerful tools for rapidly building your spreadsheet and minimizing typographical errors.

To do a simple copy and paste, follow these steps:

1. Select a cell to copy.
2. Choose the Copy command from the Edit menu.
3. Select the cell to copy the information to (call this the paste-to location).
4. Choose the Paste command from the Edit menu.

When you do this, the following happens:

• Literal values (such as "Cost and 3.14159) are copied exactly as they appear.
• Absolute cell references (such as $G$8) are copied exactly as they appear.
• Relative cell references (such as G8) are adjusted to bear the same relationship to the paste-to location as they did to the copy-from location.
• Relative portions of cell references (such as the 8 in $G8 or the G in G$8) are adjusted as well, leaving the absolute portions intact.

That's how basic Copy/Paste works in the spreadsheet.

It's crucial to understand the difference between relative and absolute cell references. Although they behave identically within a formula, they copy differently. Most cell references in your formulas will be relative ones, so that you can replicate one formula to operate over different blocks of raw data simply by pasting the formula to other locations.

For more information, study the following concepts:

• How are relative references different from absolute references?
• When do you use absolute references?
• How are Fill Down and Fill Right different from Copy/Paste?

Here are some variants on copy-and-paste that you may find useful:

• One-to-many copy. You can select a single cell and copy its value into a range of cells by selecting a cell range as the paste-to location. The result will be the same as if you had separately pasted the value into each cell.

• Many-to-many copy. You can select an entire range of cells and copy them to another location by choosing as the paste-to location the cell that you would like to hold the value of the upper-left cell of the selected range.

  In other words, Excel makes it easy to copy many contiguous cells at once without having to mark off the exact extent of the paste-to area. Be careful! If any cells within the paste-to area already contain values, those values will be replaced by the pasted ones.

• Value-only copy. If a cell contains a formula, and you want to copy only its resulting value (the value that appears in the grid for that cell), use Paste Special... rather than Paste. Choose the Values only selection in the Paste Special... dialog box. If, for example, the copy-from cell contains the formula =EXACT("this","that"), its value will be FALSE. Pasting the value only will put the Boolean value FALSE in the paste-to location.

  Hint: If the value doesn't look exactly like the cell you copied from, it is probably because the paste-to cell has a different format. Unit 2, Lesson 4 explains cell formatting.