When it comes to data organization, computation, and daily task automation, Microsoft Excel is a quite handy tool. You’re absolutely not alone if you’ve ever wondered how to add formula in Excel. Once you have included that first formula, though, learning how to copy a formula in Excel—especially in light of big data—becomes the next natural step.
This guide will walk you exactly—clearly and simply—along the path.
Why Copying Formulas Saves Time
Assume that you have used a formula to determine the total sales for a row. Imagine now having to repeat the process for hundreds or even thousands of additional rows. Typing the same formula repeatedly would be a huge waste of time.
Fortunately, Excel makes it easy to copy and paste formulas, and it even automatically modifies the cell references for every new row or column.
ALSO READ: Top 25 Excel Formulas for Beginners—Learn to Work Smarter, Not Harder!
Step-by-Step: How Do You Copy a Formula in Excel?
1. First, write your formula.
First, start by entering the formula in the first cell. For example:
=SUM(A3:A6)
This formula simply adds up the numbers from cells A3 to A6. Press Enter, and MS Excel will provide you the result.
2. Use the Fill Handle Tool
- Select the cell that contained your formula by clicking on it.
- The Fill Handle is a tiny square that appears in the cell’s lower-right corner.
- Move the cursor over the square until it becomes a plus (+) symbol.
- To apply the same formula to other cells, drag it down or across.
Excel will automatically update the cell references based on where the formula is copied.
3. Copy and Paste Formulas the Traditional Way
If you don’t want to use the Fill Handle, you can use the traditional copy-paste method:
- Click the cell with the formula.
- Press Ctrl + C to copy.
- Highlight the cells where you want the formula.
- Press Ctrl + V to paste.
This method works well, especially when you want to paste the formula into non-adjacent cells.
RELATED: How to Create a qbj File From Excel
How to Copy and Paste Formulas in Excel Without Changing Cell References
Excel uses relative referencing by default, which causes the formula to change when it is copied. However, there are instances when you want the formula to remain identical wherever you paste it.
You must add dollar signs ($) to absolute cell references in order to accomplish this. For example:
=SUM($A$2:$A$5)
This tells Excel not to change those cell references, even when the formula is copied to a new location.
Quick Tips for Copying Formulas
- Use Ctrl + D to quickly copy a formula down.
- Use Ctrl + R to copy a formula across a row.
- Use Paste Special > Formulas to paste only the formula, without any formatting changes.
Final Thoughts
Anyone who works with spreadsheets will find that knowing how to copy a formula in Excel changes everything. This ability will save you time and minimize human error, regardless of whether you’re just learning how to add formulas to Excel or are investigating how to copy and paste formulas in Excel.
In a few easy steps, you can apply your formulas to entire sheets, increasing the workflow’s intelligence, speed, and accuracy.
