copying and pasting formulas and functions copying and pasting formulas and functions, slide...

Copying and Pasting Formulas and Functions

Copying and Pasting Formulas and Functions, Slide 1 Copyright © 2004, Jim Schwab, University of Texas at Austin

Often we want to perform the same or a similar calculation on a row or column of cells, using the same formula or function. We can copy and paste formulas and functions in the same way that we copy and paste cell contents. However, when we copy and paste formulas and functions, the addresses of the cells used the formula or function needed to change to take into account the fact that the calculation is being performed in a different row or column. For example, the formula =A1+A2 in cell A3 would sum the contents of the first two rows in column A. If want to sum the first two rows in column B, we can copy the formula in cell A3 and paste it in cell B3. Excel will change the formula in B3 to =B1+B2 to reflect the change in column.

Since formulas are hard to type, this feature is a great time-saver, provided that we wanted the cell addresses to be adjusted. Sometimes we don't want Excel to automatically adjust the cell references, for example, when we are computing percentages and we want to divide each entry in a column of numbers by the same total that sums the column. Excel makes a distinction between relative cell addresses, which it can change, and absolute cell addresses, which it will not change. To make a cell address absolute, we put a $ in front of the row or column identifier that we do not want Excel to adjust.

Copying a formula and adjusting cell references - 1

The formula in cell A3 computes the sum of the cells A1 and A2.

If we copy this formula and paste it into cell B3, it will adjust the cell addresses to reflect the change in column, and will correctly sum the values in B1 and B2.

The formula in cell A3 computes the sum of the cells A1 and A2.

If we copy this formula and paste it into cell B3, it will adjust the cell addresses to reflect the change in column, and will correctly sum the values in B1 and B2.


Copying a formula and adjusting cell references - 2

To copy the formula to the clipboard, select cell A3 and click on the Copy tool button.

To copy the formula to the clipboard, select cell A3 and click on the Copy tool button.


Copying and Pasting Formulas and Functions - 3

First, select cell B3 as the destination for the Paste command.

First, select cell B3 as the destination for the Paste command. Second,

click on the Paste tool button.

Second, click on the Paste tool button.

The formula pasted in cell B3 substituted a reference to cell B1 instead of A1, and B2 instead of A2. This correctly sums the cells.

The formula pasted in cell B3 substituted a reference to cell B1 instead of A1, and B2 instead of A2. This correctly sums the cells.

When copying and pasting formulas, Excel automatically adjusts the cell references. If a formula is copied from column A and pasted in column B, all references to cells in A will be replaced by B.



Copying a function and adjusting cell references - 1

Had we used the SUM function to add cell A1 and A2 instead of the formula, the end result would be the same. We will drag copy the contents of cell A3 to B3.

Had we used the SUM function to add cell A1 and A2 instead of the formula, the end result would be the same. We will drag copy the contents of cell A3 to B3.

With cell A3 selected, click on the Copy tool button.

With cell A3 selected, click on the Copy tool button.


Copying a function and adjusting cell references - 2

First, select cell B3 as the destination for the Paste command.

First, select cell B3 as the destination for the Paste command. Second,

click on the Paste tool button.

Second, click on the Paste tool button.

The formula pasted in cell B3 substituted a reference to cell B1 instead of A1, and B2 instead of A2, and correctly sums the cells.

The formula pasted in cell B3 substituted a reference to cell B1 instead of A1, and B2 instead of A2, and correctly sums the cells.




Copying a formula without adjusting cell references- 1

In this example, we will divide each of the two cells by their sum total to demonstrate a situation when we don't want Excel to automatically adjust cell references for us.

In this example, we will divide each of the two cells by their sum total to demonstrate a situation when we don't want Excel to automatically adjust cell references for us.

In cell F1, enter the formula =E1/E3 to compute the proportion for 22 of 63.

In cell F1, enter the formula =E1/E3 to compute the proportion for 22 of 63.



We will copy cell F1 to F2 by filling down.

We will copy cell F1 to F2 by filling down.

Second, select the Fill > Down command from the Edit menu.

Second, select the Fill > Down command from the Edit menu.

First, select cells F1 and F2.

First, select cells F1 and F2.



Instead of computing the proportion, Excel returns a #DIV/0! error which means it tried to divide by zero which is not an allowable mathematical operation.

If we look at the formula in cell F2, we see that when Excel adjusted the row addresses, it substituted E4 for E3. Since E4 is empty, Excel substituted a zero for it and tried to divide, producing the error.

In this case, we want Excel to keep the E3 in the denominator instead of changing it.

Instead of computing the proportion, Excel returns a #DIV/0! error which means it tried to divide by zero which is not an allowable mathematical operation.

If we look at the formula in cell F2, we see that when Excel adjusted the row addresses, it substituted E4 for E3. Since E4 is empty, Excel substituted a zero for it and tried to divide, producing the error.

In this case, we want Excel to keep the E3 in the denominator instead of changing it.



Excel makes a distinction between relative cell addresses, which it can change, and absolute cell addresses, which it will not change. To make a cell address absolute, we put a $ in front of the row or column identifier that we do not want Excel to adjust. For example, if the address E$1 is entered in a formula or function, Excel will not change the 1 when the cell is copied and pasted. If a cell address is written as $E1, Excel will change the row number, but not the column number when copying and pasting. If the cell reference is written as $E$1, neither the row nor the column will be changed when the cell is copied and pasted.

Excel makes a distinction between relative cell addresses, which it can change, and absolute cell addresses, which it will not change. To make a cell address absolute, we put a $ in front of the row or column identifier that we do not want Excel to adjust. For example, if the address E$1 is entered in a formula or function, Excel will not change the 1 when the cell is copied and pasted. If a cell address is written as $E1, Excel will change the row number, but not the column number when copying and pasting. If the cell reference is written as $E$1, neither the row nor the column will be changed when the cell is copied and pasted.



For this example, the correct entry for cell F1 should be =E1/E$3, so that the reference to row 3 does not change.

When we copy F1 to F2, we will no longer get the division by zero error.

For this example, the correct entry for cell F1 should be =E1/E$3, so that the reference to row 3 does not change.

When we copy F1 to F2, we will no longer get the division by zero error.


copying and pasting formulas and functions copying and pasting formulas and functions, slide...

Documents

cell b3

cell references

cell a1

cell b1

cell contents

contents of cell a3

absolute cell addresses

relative cell addresses