Excel Tutorial: How to Copy Formulas in Excel

Copying formulas into Excel

After creating a formula in Excel, you can use the Copy and Paste commands to duplicate or transfer the formula into other areas of your worksheet. When you copy formulas in Excel that contain cell references, the references adjust to their new location, unless you specify otherwise.

Using absolute and relative cell references in Excel

When you use cell references in your formulas, Excel uses the data stored in that location in its calculations. The benefit is that when you change the original data, the formula is updated as well.

If you copy a formula from one location to another, Excel adjusts the cell reference based on its new location. For instance, the formula =B2*B3 entered in cell B5 would be adjusted to =C2*C3 when copied to cell C5. However, you can modify the cell reference so it remains fixed to its original location.

An absolute cell reference in a formula remains fixed even when the formulas are copied or moved. A relative cell reference adjusts to its new location.

The dollar sign ($) is used to indicate that the cell reference should remain absolute. Use the dollar sign to indicate the portion of the address (the row reference, the column reference, or both) that should remain fixed. The following table shows some examples.

Absolute and relative cell references in Excel

 Cell Reference

What it Means

$A6

Column A remains fixed, row 6 adjusts

A$6

Row 6 remains fixed, column A adjusts

$A$6

Cell A6 remains fixed

A6

The reference to column A and row 6 adjusts to its new location

 

Making an absolute cell reference in Excel

The following example shows how to make a cell reference absolute.

  1. Type = in cell D5.
  2. Point to cell B5 and then press the F4 function key on your keyboard. Excel inserts a dollar sign ($) between the row and column reference.
  3. Type + and point to cell C5.
  4. Press Enter on the keyboard. The formula in cell D5 now reads =$B$5+C5.
 

When you use a range name in a formula, Excel automatically makes the range absolute. In other words, when you copy a formula that makes use of a range name, the range reference will not adjust to its new location.

Copying formulas with AutoFill in Excel

The AutoFill command lets you quickly copy a formula to a range of adjacent cells. Perform the following set of steps to practice using the AutoFill command.

  1. Click in cell D5.
  2. From the Formulas tab, choose AutoSum. Excel adds up the cells immediately adjacent to the current cell, which in this case is range B5:C5. Press Enter.
  3. Click in cell D5 and drag the AutoFill handle down to cell D9 and release. Excel copies the formula from cell D5 and adjusts the cell references accordingly.

The AutoFill feature lets you quickly copy a formula to adjacent cells.

Copying Excel formulas with Copy and Paste

When you use the Copy command, Excel stores a copy of the selection on the clipboard, allowing you to paste the data multiple times. Perform the following set of steps to practice copying and pasting data.

  1. Click in cell B10.
  2. From the Home tab, choose Copy from the Clipboard group.
  3. Select range C10:D10.
  4. Choose Paste from the Clipboard group. Excel pastes the formula from cell B10 and adjusts the cell references accordingly.
 

You can also press the Enter key on your keyboard to paste the data in a worksheet cell. However, Excel will remove the stored item from the clipboard.

Pasting Excel formula results

With the Paste Values command, you can copy a formula and then paste the results of the formula in another cell. Perform the following set of steps to practice copying and pasting formula results.

  1. Select cell H8 and choose Copy from the Home tab.
  2. Click in cell I8, choose Paste from the Clipboard group, and then select Paste Special. The Paste Special dialog box appears.
  3. Select Values from the Paste Special dialog box and click OK. Excel pastes the result of the PMT formula, (2,090.74) in cell I8.

Paste the results of formulas in a separate cell.

 

Moving worksheet formulas in Excel

Unlike copying a formula, Excel does not adjust cell references when a formula is moved. There are two ways to move a formula in Excel: with the Cut and Paste command, or by dragging and dropping the formula to its new location.

To drag and drop a formula:

  1. Select range D4:D10.
  2. Position the mouse pointer on the edge of the selected range; when the mouse pointer changes to a four-headed arrow, click and drag the selection to cell E4.
  3. Release to drop the range in its new location.

Quickly move a range of cells to a new location with click,
drag and drop.

To move an Excel formula with Cut and Paste:

  1. Select range E4:E10.
  2. From the Home tab, choose Cut. A flashing dashed border appears around the selected range.
  3. Click in cell D4 and choose Paste from the Home tab. Excel pastes the selected range.

Take a class from the authors of this tutorial

See available Excel classes and learn from the expert instructors at American Graphics Institute. In-person classes at our locations, online courses, or private training is available.

See Courses

Tutorials Category: Excel Tutorials

These tutorials are created by and the team of expert instructors at American Graphics Institute.