If you’d like to use the same formula across multiple cells, copy the formula instead of typing it individually in each cell. You can use both dynamic cell reference and absolute cell reference while doing this, and we’ll show you how.
To copy an Excel formula, you can use either the traditional copy and paste method or drag and drop. Both work the same way and produce the same results.
RELATED: Relative and Absolute Cell Reference, and Formatting
Using Absolute Cell Reference While Copying Formulas
By default, when you copy a formula, Excel uses dynamic cell reference, meaning the cell references will change depending on where you paste the formula. If you’d like to use absolute cell reference, meaning the cell references stay the same no matter where you put the formula, then in your original formula, add the
$ (dollar) sign before the column letter and the row number.
For example, if your formula looks like this:
And you don’t want Excel to dynamically change D2, then tweak the formula as follows:
You can use this cell reference in either of the following formula copy methods.
One way to copy an Excel formula is to use your computer’s traditional copy and paste method, which involves pressing Ctrl+C for copying and Ctrl+V for pasting. (On Mac, you’d press Command+C for copying and Command+V for pasting.)
To use this method, first, open your spreadsheet with Microsoft Excel.
In the spreadsheet, click the cell that contains the formula you want to copy. Then press Ctrl+C (Windows) or Command+C (Mac).
You’ve successfully copied the selected formula.
Now, click the cell where you want to use this formula in your spreadsheet. Then press Ctrl+V (Windows) or Command+V (Mac).
Excel has pasted your formula in your selected cell, and you’re all set.
Method 2: Use the Fill Handle to Copy an Excel Formula
To copy formulas in sequential order, using the Fill Handle is much more efficient than the above method.
To use it, first, open your spreadsheet with Microsoft Excel.
In your spreadsheet, click the cell that contains the formula to copy.
From the selected cell’s bottom-right corner, drag downwards. Drag it across all the cells where you want your formula copied.
When you leave dragging, Excel fills the selected cells (hence the name—Fill Handle) with your formula.
And you’re all set.
Did you know you can also copy conditional formatting in your Excel spreadsheets? Check out our guide to learn how.
RELATED: How to Copy Conditional Formatting in Microsoft Excel