In this tutorial, you will learn a few different ways of copying formulas in Excel – how to copy formula down a column, to all of the selected cells, copy a formula exactly without changing cell references or formatting, and more.
Copying formulas in Excel is one of the easiest tasks that is usually done in a mouse click. I say “usually” because there can be very specific cases that require special tricks, like copying a range of formulas without changing cell references or entering the same formula in multiple non-adjacent cells.
Luckily, Microsoft Excel offers many ways to do the same task, and it is true for copying formulas. In this tutorial, we are going to discuss different ways to copy formulas in Excel so that you could choose the one best suited for your task.
Copying formulas in Excel is an essential skill for anyone working with spreadsheets. Whether you need to apply the same calculation across multiple rows or columns, or simply want to duplicate a formula without having to rewrite it, knowing how to copy formulas can save you a lot of time.
In this comprehensive guide we’ll walk through the various methods for copying Excel formulas quickly and efficiently.
Why Copy Formulas in Excel?
Here are some common reasons you may need to copy formulas in Excel
-
Perform calculations on multiple rows/columns – Rather than manually typing the same formula over and over, copying speeds up applying calculations to large datasets.
-
Maintain consistency – Copying formulas ensures all calculations are uniform. This eliminates errors from manually entering formulas.
-
Save time – Rewriting the same formula is tedious and time consuming. Copying them takes just a few clicks.
-
Update multiple formulas simultaneously – Making changes to a formula is easy when you can copy it to multiple cells.
-
Preserve structure of complex formulas – Lengthy formulas with many nested functions can be tricky to rebuild from scratch. Copying preserves the formula’s structure.
Copying Formulas Down a Column
The most common copy operation is to apply a formula down an entire column. Here’s how to do it:
-
Enter your formula in the first cell (e.g. B2).
-
Click on the cell with the formula.
-
Hover your cursor over the lower-right corner of the cell until it turns into a plus (+) icon.
-
Click and drag the plus icon down the column to select the cells to copy the formula to.
-
Release the mouse. The formula will populate all selected cells.
The great thing about copying formulas this way is it automatically updates relative cell references. For example, a formula =A1+B1 in cell C1 will change to =A2+B2 in cell C2. This allows the formula to adapt to each row’s data.
Tip: Double-clicking the plus icon will copy the formula down to the bottom of the contiguous data range.
Copying Formulas to Multiple Cells
You can also copy a formula to multiple disconnected cells at once:
-
Select the cell with the original formula.
-
Press Ctrl + C to copy.
-
Select the cells you want to paste the formula into. To select non-adjacent cells, hold Ctrl while clicking additional cells.
-
Press Ctrl + V to paste into the selected cells.
The formula will populate each cell and adjust relative references accordingly.
Copying Formulas Without Adjusting References
When you want to copy a formula without shifting its cell references, you need to use absolute references. Prefix a cell address with a $ sign to make it absolute.
For example, =$A$1 would refer to cell A1 in all copies. The reference won’t change no matter where you copy the formula.
You can also mix absolute and relative references. For example:
-
=$A1anchors the column but allows the row to change. -
=A$1anchors the row but allows the column to change.
Copying Formulas to New Worksheets or Workbooks
If you want to copy a formula to a different worksheet or workbook, the steps are the same as copying within a worksheet:
-
Select the original cell and press Ctrl + C.
-
Switch to the destination worksheet/workbook.
-
Select where you want the formula to go and press Ctrl + V.
You’ll need to adjust cell references to ensure they point to the correct sheet or file. For example, reference the sheet name like 'Sheet2'!A1 or include the workbook name like [Book1.xlsx]Sheet1'!A1.
Ways to Copy Formulas Faster
Besides the manual drag and copy methods above, there are a few shortcuts that can help copy formulas faster in Excel:
-
Copy and paste – Once you get the formula right in one cell, copy it with Ctrl + C and paste to other cells with Ctrl + V.
-
Fill handle – Click and drag the fill handle (lower-right corner) to copy formulas down or across.
-
Ctrl + D – Copies the formula from the cell above into the current cell. Useful for copying down a column.
-
Ctrl + R – Copies the formula from the cell to the left into the current cell. Useful for copying across a row.
-
Flash fill – Automatically copies a formula and increments cell references when you start typing a new formula into subsequent cells.
-
Array formulas – Use array formulas for more complex formula duplicates that need to span multiple cells.
-
Excel Tables – Turning your data into an Excel Table will automatically fill calculated columns with the correct formulas.
Copying Formulas vs Copying Values
One final tip – don’t confuse copying formulas with copying the results!
If you simply want to duplicate a formula’s final value, select the cells and press Ctrl + C, then right click and select Paste Values only. This pastes the results, not the underlying formula.
Conversely, copying only the formulas is done by right-clicking and choosing Paste Formulas after copying.
Common Formula Copying Issues
Copying formulas in Excel doesn’t always go smoothly. Here are some troubleshooting tips for common formula copying problems:
-
Broken references – If cell references don’t update correctly, convert them to absolute or relative references. Also check formulas for proper sheet names.
-
Circular references – Copying a formula that refers to a cell which in turn refers back to the original formula’s cell creates a circular reference error.
-
Overwrite formatting – By default copying values also copies formatting. To prevent this, select Keep Source Formatting when pasting.
-
Large formulas – Lengthening a formula by copy-pasting can exceed Excel’s character limits. Try shortening it or breaking it into smaller sub-formulas.
-
Formula duplicates – If you copy a formula to a cell that already has data, it won’t override the existing value. You’ll end up with two formulas.
-
Merged cells – Copying to merged cells can exhibit strange behavior. Avoid merging cells used in formulas.
-
Volatile functions – Some Excel functions like RAND() or NOW() recalculate with every change. Copying these down columns may slow your spreadsheet.
As you can see, copying and duplicating formulas is straightforward in Excel. The key is understanding how to use absolute and relative references properly to control formula behavior when copying.
Mastering various copy techniques like fill handle dragging, keyboard shortcuts, and flash fill will help you become proficient at copying formulas quickly. Avoiding common copy pitfalls like circular references and merged cells will ensure you don’t run into errors.
Equipped with this knowledge, you should have no trouble effectively copying formulas in Excel to streamline your spreadsheet work. The time savings add up fast, especially when working with large datasets!
Copy formula to the entire column
As youve just seen, the fill handle makes copying formulas in Excel really easy. But what if you need to copy a formula down a ten-hundred-line sheet? Dragging the formula over hundreds of rows does not look like a good idea. Luckily, Microsoft Excel provides a couple of quick solutions for this case as well.
Enter a formula into multiple cells with a single key stroke (Ctrl + Enter)
In situations when you need to input the same formula in more than one cell on a worksheet, adjacent or non-adjacent ones, this method can be a time-saver.
- Select all the cells where you want to enter the formula. To select non-contiguous cells, press and hold the Ctrl key.
- Press F2 to enter the edit mode.
- Input your formula in one cell, and press Ctrl + Enter instead of Enter. Thats it! The formula will get copied to all of the selected cells, and Excel will adjust relative cell references accordingly.
Tip. You can use this method to enter any data, not just formulas, in multiple cells at a time. A few other techniques are described in the following tutorial: How to enter the same data into all selected cells at a time.
