How to copy a formula in an Excel sheet

Have you ever spent too much time retyping the same formula in Excel? There’s a faster way! Instead of manually inputting the same formula into multiple cells, you can copy and paste it across the sheet. In this article, we will explore two ways to do so.

Why copy a formula in an Excel sheet?

Copying formulas in an Excel sheet is crucial for efficiency and accuracy. Instead of retyping the same formula for each cell, copying allows you to apply a single formula to multiple rows or columns. This minimizes errors and maintains uniformity across the dataset.

It is particularly useful when working with large data sets, financial reports, inventory management, and any scenario that requires repeated calculations.

Additionally, copying formulas allows for dynamic updates, meaning that if you change a referenced cell, all dependent formulas will automatically update.

Methods to copy a formula in an Excel sheet

Let’s see with some practical examples how to copy a formula in an Excel sheet. To do so, we will use ONLYOFFICE Spreadsheet Editor.

Method 1: Using the Fill Handle

The Fill Handle is a quick and easy way to copy formulas across adjacent cells.

1. Select the cell containing the formula you want to copy.

2. Hover over the bottom-right corner of the selected cell until you see a small cross (the Fill Handle).

3. Click and drag the Fill Handle across the range of cells where you want to copy the formula.

4. Release the mouse button, and the formula will be copied to the selected cells with adjusted references.

Method 2: Using copy and paste

If you need to copy a formula to non-adjacent cells or different sheets, the copy-paste method is effective.

1. Select the cell containing the formula.

2. Press Ctrl + C (or Cmd + C on macOS) to copy it.

3. Click on the destination cell where you want to paste the formula.

4. Press Ctrl + V (or Cmd + V on macOS) to paste it.

Copying as an absolute reference

By default, spreadsheet editors adjust cell references when copying formulas. If you want to keep a fixed reference, you need to use absolute references.

1. Edit the formula in the original cell and add $ before column and row references (e.g., $A$1).

2. Copy the formula using any method above.

3. When pasted, the reference will remain fixed.

Copying formulas without changing cell references

Sometimes, you may want to copy a formula exactly as it is, without adjusting cell references.

1. Select the cell with the formula and press Ctrl + C (or Cmd + C).

2. Click on the small Ctrl icon that appears on the right, and choose “Paste only formula” to paste the exact formula without modifying references.

To learn more about pasting options, please read this guide.

Get ONLYOFFICE Spreadsheet Editor

Ready to boost your spreadsheet efficiency? Open ONLYOFFICE Spreadsheet Editor and try these techniques now!

If you don’t have a ONLYOFFICE DocSpace account yet, you can create one for free right now and test the capabilities of its Spreadsheet and all the other editors. Try it online or with our desktop applications.

START ONLINE   GET DESKTOP APP