Get help

Help center

Guides and answers

Contact support

Write to our team

Sign in to

Workflows

Build processes with your team

Automations

Connect files and automate tasks

One system, four parts

AI agents

Virtual Employees that work 24/7

Automations

Connect files and automate tasks

Workflows

Build processes with your team

Data Space

Trusted data sharing and control

Solutions by department

Finance

Reports, budgets, and reconciliations

Inventory and manufacturing

Stock, orders, and production in one view

HR

Timesheets, attendance, and reviews

Project management

Status and workload across projects

Sales and CRM

Leads and deals kept up to date

Education

Student records and school data

Finance

Turn manual finance work into reports that update themselves.

What Finance teams automate

  • Track payables and receivables
  • Build cash flow statements
  • Compare budget against actuals

Inventory and manufacturing

See stock levels and automate ordering.

What inventory and manufacturing teams automate

  • Track inventory across multiple sites
  • Connect sales, purchasing, and warehouse data
  • Link a barcode scanner to your sheet

HR

Run recruitment, attendance, and OKR tracking from your sheets.

What HR teams automate

  • Track recruitment and onboarding
  • Record team OKRs and results
  • Track check-ins, check-outs, and leave

Project management

Assign tasks and track hours without manual updates.

What project teams automate

  • Assign tasks and track status changes
  • Estimate effort and log hours worked
  • Track milestones across projects

Sales and CRM

Track leads and sales performance with automated reports.

What sales teams automate

  • Track and convert leads
  • Set sales goals and track team metrics
  • Monitor sales KPIs on dashboards

Education

Automate attendance, grades, and school purchasing.

What schools automate

  • Track attendance and notify parents
  • Record and compare class grades
  • Track inventory lent to students and staff

Find a use case

By department

How each team uses Sheetgo

By industry

How each industry uses Sheetgo

By company size

Small, medium-sized, or large

Learn

Blog

Guides and product news

Customer stories

How teams like yours use Sheetgo

Help center

Guides and answers

Why Sheetgo

Compare

How Sheetgo compares with other tools

Security

How we protect your data

Trust center

Our compliance details

Sign in to

Workflows

Build processes with your team

Automations

Connect files and automate tasks

One system, four parts

AI agents

Virtual Employees that work 24/7

Automations

Connect files and automate tasks

Workflows

Build processes with your team

Data Space

Trusted data sharing and control

Solutions by department

Finance

Reports, budgets, and reconciliations

Inventory and manufacturing

Stock, orders, and production in one view

HR

Timesheets, attendance, and reviews

Project management

Status and workload across projects

Sales and CRM

Leads and deals kept up to date

Education

Student records and school data

Find a use case

By department

How each team uses Sheetgo

By industry

How each industry uses Sheetgo

By company size

Small, medium-sized, or large

Get started

Templates

Ready-made workflows to start from

Learn

Blog

Guides and product news

Customer stories

How teams like yours use Sheetgo

Help center

Guides and answers

Why Sheetgo

Compare

How Sheetgo compares with other tools

Security

How we protect your data

Trust center

Our compliance details

Help center

Guides and answers

Contact support

Write to our team

Workflows

Build processes with your team

Automations

Connect files and automate tasks

How to copy a formula in Excel? 9 easy methods

When working in a spreadsheet, there are many reasons why you may need to copy a formula to single or multiple cells. These may be adjacent – column or row – or located in different parts of the spreadsheet. In any case, Microsoft Excel offers multiple options for copying and pasting formulas and other parts or features of the cell’s contents.

In this guide, you will learn how to copy and paste a formula to a single cell, an entire column or row, or non-adjacent cells. First, you will review absolute vs. relative referencing before learning nine different methods for copying formulas in Excel: using simple paste, using special paste (formulas), pasting to multiple non-adjacent cells, converting a range to an Excel table to copy the formula automatically, using the array formula shortcut, using the fill tool, double-clicking or dragging the fill handle, and using a keyboard shortcut.

Absolute vs. relative referencing in formulas

The type of referencing you use determines whether the references change when you copy a formula. In some cases, you want the references to be relative so that they will adapt to refer to whichever row or column to which you are copying. However, sometimes you need to keep the column or row fixed with absolute referencing, and sometimes you need the formula to remain anchored to the original cells.

Absolute referencing

To ensure that you refer to a specific cell, use absolute referencing for the column and row. Regardless of where you copy the formula, it will always refer to those specific cells. The dollar signs work the same way when you set an absolute cell reference in Google Sheets.

How to copy formula in Excel — =$A$1*$B$1 in cell D2 under the absolute referencing label

Mixed referencing

To ensure that the column is fixed but the formula adjusts to the row it’s copied to, use absolute referencing for the column and relative for the row.

How to copy formula in Excel — =$A1*$B1 in cell E2 under the fixed column label

To fix the row and not the column, use absolute referencing for the row only.

How to copy formula in Excel — =A$1*B$1 in cell F2 under the fixed row label

Relative referencing

Finally, use relative referencing for both to allow the column and row to change relative to the cell’s location.

How to copy formula in Excel — =A1*B1 in cell G2 under the relative label

How to copy and paste a formula in Excel?

There are various ways to copy and paste a formula in Microsoft Excel. You can copy it using any of the available methods, including right-clicking to see the menu or using the shortcuts: Ctrl + c (Windows) or Cmd + c (Mac).

Select the cells where you want to paste.

Copy and paste a formula in Excel — copied row D2:G2 with cells D3:G5 selected below it

Use the shortcut – Ctrl + v or Cmd + v – or right-click and select paste. This will reproduce the cell exactly, including the formula and the format. However, the type of referencing will depend on the original cell. Using relative referencing in the original formula will adapt the references to the new location.

Copy and paste a formula in Excel — formulas pasted into rows 3 to 5 with the references adjusted

Special paste options

If you want to keep the formula only, use ‘Paste Special’ and choose ‘Formulas’.

Copy and paste a formula in Excel — right-click Paste Special submenu with Formulas highlighted

However, as you can see in the screenshot below, Microsoft Excel features a variety of special options for pasting.

Copy and paste a formula in Excel — Paste Special submenu with a callout pointing to Paste Special

Click ‘Paste Special…’ to see even more options.

Copy and paste a formula in Excel — Paste Special dialog with Paste and Operation options

How to copy a formula to non-adjacent cells in Excel?

Follow the steps below to copy formulas to non-adjacent cells.

1. Copy the cell with the formula. Remember to use absolute referencing if you want the formula to remain the same.

Copy a formula to non-adjacent cells in Excel — SUM formula in B2 with Copy selected in the right-click menu

2. Select the first cell and hold down Ctrl or Cmd while selecting the rest.

Copy a formula to non-adjacent cells in Excel — cells D2, F2, H2 and J2 selected

3. Press Ctrl + v or Cmd + v to copy the contents to the selected cells.

Copy a formula to non-adjacent cells in Excel — the SUM result pasted into D2, F2, H2 and J2

How to copy a formula down a column in Excel?

There are multiple ways to copy a formula to a column or row in Microsoft Excel. Below, you will learn how to use six different methods.

Create Excel table to add formula to entire column automatically

The simplest way to ensure that your formula is automatically adjusted and copied down to the last row is to turn your range of data into an Excel table.

1. Select any cell in the range and press Ctrl + t (Windows) or Cmd + t (Mac).

Copy a formula down a column in Excel — Create Table dialog for range A1:B20 with headers ticked

2. Type your formula into the first cell of the column where you want the results. In this case, I want to multiply the value in the amount column by 0.1 (10%).

Copy a formula down a column in Excel — amount times 0,1 formula typed in the first interest cell

3. As soon as you press ‘Enter’, the formula is copied down to the last row.

Copy a formula down a column in Excel — interest column filled to the last table row

Use array formula shortcut

If you don’t want to convert your data to an Excel table, you can use the array shortcut to apply the formula to the entire column. Like in the previous example, I want to multiply each value in the first column by 0.1 (10%).

1. Select the cells to which you want to copy the formula and click on the formula bar.

Copy a formula down a column in Excel — empty interest cells B2:B20 selected

2. Type the formula and press Ctrl + Shift + Enter. In Excel for Microsoft 365, you can also type the formula in the first cell and press Enter, and the results spill down the column on their own.

Copy a formula down a column in Excel — =A2:A20*0,1 typed in the formula bar

3. The formula is applied, and the results appear in the selected cells.

Copy a formula down a column in Excel — array formula in braces with results in every selected cell

Use fill tool

The fill tool allows you to copy the cell and fill up, down, left, or right. Select the cells – including the one with the formula – and go to ‘Editing’ in the ‘Home’ tab.

Copy a formula down a column in Excel — total cells selected and the Fill button in the Editing group

Click to see the ‘Fill’ drop-down menu. In this case, I will select ‘Down’.

Copy a formula down a column in Excel — Fill drop-down open with Down highlighted

As you can see, the formula has been copied down, and the references adjusted.

Copy a formula down a column in Excel — total column filled with =A2+B2 copied down

Double-click fill handle

Select the cell with the formula and double-click the fill handle in the bottom-right corner of the cell to copy it down.

Copy a formula down a column in Excel — fill handle of cell C2 marked with a red box

The formula is automatically copied down to the last row.

Copy a formula down a column in Excel — total column filled to row 20 after the double-click

Drag fill handle

Alternatively, you can drag the fill handle to copy the formula.

1. Select the cell with the formula and grab the fill handle.

Copy a formula down a column in Excel — red arrow dragging the fill handle from C2 down to row 20

2. Drag it over the cells to which you want to copy in any direction you want.

Copy a formula down a column in Excel — totals filled down with the Auto Fill Options button

Use shortcut

Finally, you can use a keyboard shortcut to copy the formula down to the selected cells instantly.

1. Select the cell with the formula and the relevant cells in the column.

Copy a formula down a column in Excel — cells C2:C20 selected with =A2+B2 in C2

2. Press Ctrl + d (Windows) or Cmd + d (Mac). To copy across a row instead of down a column, select the row and use Ctrl + r or Cmd + r.

Copy a formula down a column in Excel — total column filled after the Ctrl + d shortcut

Conclusion

There are multiple ways to copy formulas in Microsoft Excel, depending on your preference and how the data is structured. Suppose the cells referenced in your formula are spread out over your spreadsheet or contained within multiple ranges with different structures. In that case, it’s especially important to use the appropriate referencing for each cell: absolute, relative, or mixed.

The simplest way to ensure that your formulas are accurately copied to a whole column in a table is to turn the range into an Excel table. If you don’t want to use an Excel table, you know there are multiple methods you can use to copy a formula in any direction you want. These methods include using the array formula shortcut, using the fill tool, dragging or double-clicking the cell’s fill handle, and using a keyboard shortcut to copy the formula down or across the selected cells.

To learn how to lock cells, transfer data, and other Excel topics, check out the guides on:

Working in Google Sheets instead? See how to fill down in Google Sheets. To move formula results into another workbook without copying them by hand, you can transfer data from one sheet to another in Excel with Sheetgo, an AI-powered data automation platform. For more formulas worth copying, start with these top 10 Excel functions.

You may also like…

IRR formula Excel — Excel logo and fx symbol above =IRR(values; [guess]) on a light orange background

How to calculate IRR in Excel? (IRR function and formula)

Use the IRR formula in Excel to find an investment’s internal rate of return. Compare two projects with IRR and NPV, and learn to calculate IRR by hand.

Excel INDIRECT function — Excel logo and fx icon above =INDIRECT on a light gray background

Excel INDIRECT function: formula and examples

Use the INDIRECT function Excel provides to turn text into a cell reference. See the syntax and examples with SUM, AVERAGE, ROW, and other sheets.

Excel GROWTH function — Excel logo and fx icon with =GROWTH on a yellow background

Excel GROWTH function: how to use and examples

The Excel GROWTH function returns the predicted exponential growth for your data set. Here’s how to use the GROWTH function step by step, with examples.