How to Copy a Formula Down in Excel

Who I am
Matthew Wilhelm Kapell
@matthewwilhelmkapell
Author and references

Imagine manually filling in formulas in a thousand cells – that would be a nightmare. Fortunately, Excel lets you easily copy formulas to other cells. This basic task can save you a lot of time. However, how this is done is not always clear to new Excel users.

In this guide, we will explain how to copy a formula in Excel using three methods. We'll also explain how to switch between relative and absolute formula references and answer common questions related to the topic.



How to copy a formula in Excel by dragging

You can quickly apply an Excel formula to adjacent cells by dragging the corner of the cell containing the formula. Follow the instructions below:

  1. Click the cell containing the formula you want to copy.
  2. Hover your cursor over the lower right corner of the cell. Your cursor should turn into a plus sign.
  3. Select and hold the lower right corner of the cell, then drag it in the direction you want to select the cells to fill.
  4. Release the mouse. The formula will be applied automatically to the selected cells.

If your cursor does not change to a plus sign and you cannot drag the cell, this functionality may be hidden. Follow the steps below to enable it:

  1. Click "File" at the top of your screen.
  2. Select "Options" from the drop-down menu.
  3. Go to “Advanced”.
  4. Click "Enable fill handle and cell drag-and-drop option" under "Editing options".

How to copy a formula in Excel without dragging

Sometimes you may need to copy Excel formula to non-adjacent cells and the move method will not work. In this case, you can select the cells manually. Follow the steps below:



  1. Click the cell containing the formula.
  2. Simultaneously press the "Ctrl" and "C" keys to copy the formula.
  3. Hold down the "Ctrl" key and click the cells you want to apply the formula to.
  4. Click an arrow next to "Paste" at the top of the document and select "Formula." It will be applied automatically to the selected cells.

If you need to quickly copy a formula to an entire column, you can use the "Ctrl + D" shortcut. Follow the instructions below:

  1. Select the cell with the formula.
  2. Click on the index of the desired column (the letter at the top).
  3. Simultaneously press the "Ctrl" and "D" keys to apply the formula to the column.

Shortcuts also allow you to copy a formula into an entire row. Here's how:

  1. Select a cell with the formula.
  2. Choose a line number on the left and highlight the whole line.
  3. Simultaneously press the "Ctrl" and "R" keys.

How to copy a formula in Excel without modifying the formula

By default, Excel formulas contain relative cell references. The values ​​of the formula change according to their relative position.

Let's take a simple formula =SUM(B1,C1) for example. If you copy it, the following cells will contain formulas =SUM(B2,C2), =SUM(B3,C3), and =SUM(B4,C4).

However, sometimes you may need to correct some values ​​in the formula.


Suppose you want to sum cell B1 with each value in column C. In this case, you need to add a dollar sign ($) in front of the column and row index. The formula should look like this: =SUM($B$1,C1), and subsequent cells will contain =SUM($B$1,C2), =SUM($B$1,C3), and =SUM($B$1, C4) formulas. You can fix as many references as you want using this method.


Additional FAQs

Can I correct only the column reference or only the row reference in a formula?

The dollar sign ($) allows you to fix the values ​​chosen in a formula. For example, if you want the value in cell B3 to remain unchanged when copying the formula to other cells, add dollar signs in front of the column and row reference (3 $B$).

However, sometimes you may need to correct a single reference. In this case, you only need to add a dollar sign in front of the column or row reference. Like that:


• $B3 – the row reference may change when the formula is copied to other cells, but the column reference may not.

• B$3 – the column reference can change, but the row reference is fixed.

How to copy the cell value instead of its formula?

By default, Excel copies the cell formula when using the "Ctrl+C" and "Ctrl+V" keyboard shortcuts. If you need to copy the cell value instead, follow the steps below:

1. Select the cell containing the value you want to replicate.

2. Use “Ctrl + C” keyboard shortcut to copy cell data.

3. Select the cells you want to paste the cell value into.

4. Click the arrow next to the "Paste" button at the top of your screen.

5. Select “Values”.

Improve your workflow

Now that you know how to copy formulas in Excel, your workflow should become much more efficient. Keep in mind that minor details in the instructions may differ depending on your version of Excel. Keep the software up to date to make sure you don't miss any new features and always find the right guides.

Do you know any useful tips for using Excel? Please share them in the comments section below.

Audio Video How to Copy a Formula Down in Excel
add a comment of How to Copy a Formula Down in Excel
Comment sent successfully! We will review it in the next few hours.