How to Copy Formulas in Excel

Copying formulas in Excel is a fundamental skill that saves hours of manual data entry. However, many users struggle with formulas changing unexpectedly when copied—or not changing when they should. This comprehensive guide teaches you the correct methods to copy formulas, master absolute and relative cell references, and avoid common errors that corrupt calculations.

2,900 searches/month

Essential Skill

Why Copying Formulas Correctly Matters

Imagine manually typing =A2*B2, then =A3*B3, then =A4*B4 for hundreds of rows. Copying formulas eliminates this tedious work—Excel automatically adjusts cell references as you copy down or across. However, this automatic adjustment is both powerful and dangerous. Without understanding relative vs. absolute references, you might copy a formula that should reference a fixed cell (like a tax rate), only to have that reference shift to the wrong cell, creating calculation errors throughout your spreadsheet.

Mastering formula copying is essential for building scalable spreadsheets, analyzing large datasets, and ensuring calculation accuracy across thousands of cells. It's the difference between spending 5 seconds and 5 hours on repetitive calculations.

Method 1: Copy and Paste (Classic Approach)

The most straightforward method using keyboard shortcuts or right-click menus

1Select the Cell with the Formula

Click on the cell containing the formula you want to copy. The cell will be highlighted with a border. You'll see the formula in the formula bar at the top of Excel.

2Copy the Cell

Choose one method:

• Keyboard: Press Ctrl+C (Windows) or ⌘+C (Mac)
• Right-click: Right-click the cell → Select "Copy"
• Ribbon: Home tab → Clipboard group → Click "Copy"

The cell border will change to a dashed moving border (marching ants), indicating it's copied.

3Select Destination Cells

Click on the cell where you want to paste, or select a range of cells to paste into multiple locations at once. To select a range: click first cell, hold Shift, click last cell.

4Paste the Formula

Choose one method:

• Keyboard: Press Ctrl+V (Windows) or ⌘+V (Mac)
• Right-click: Right-click destination cell → Select "Paste"
• Enter key: Simply press Enter (pastes and removes marching ants)

✓ The formula is now copied with cell references automatically adjusted!

Example:

Cell A1: 100 | Cell B1: =A1*2 (Result: 200)

Copy B1, paste to B2

Cell A2: 50 | Cell B2: =A2*2 (Result: 100) ← Formula adjusted automatically!

Method 2: Fill Handle (Drag to Copy)

The fastest method for copying formulas down columns or across rows

The fill handle is the small square at the bottom-right corner of a selected cell. This is the fastest way to copy formulas to adjacent cells.

Step 1

Select the cell with the formula. Look for the small green square in the bottom-right corner.

Step 2

Hover over the fill handle until your cursor changes to a black crosshair (+).

Step 3

Click and drag down (for columns) or right (for rows) to the last cell you want to fill.

Step 4

Release the mouse. Excel automatically copies the formula to all selected cells.

Pro Shortcut:

Double-click the fill handle to auto-fill down to the last row with adjacent data. Excel detects where your data ends and stops there automatically—perfect for long datasets!

Understanding Absolute vs. Relative References

The critical concept that determines how formulas behave when copied

This is where most Excel users struggle. When you copy a formula, Excel automatically adjusts cell references—but sometimes you need references to stay fixed. This is controlled by dollar signs ($).

Relative Reference

A1

Changes when copied. If you copy =A1 from B1 to B2, it becomes =A2.

B1: =A1 (refers to row 1)

Copy to B2: =A2 (now refers to row 2) ← Adjusted automatically

Copy to C1: =B1 (now refers to column B) ← Adjusted automatically

Absolute Reference

$A$1

Stays locked when copied. The dollar signs ($) lock both column and row.

B1: =$A$1 (locked to A1)

Copy to B2: =$A$1 (still refers to A1) ← Stayed locked!

Copy to C5: =$A$1 (still refers to A1) ← Stayed locked!

Mixed References

$A1

Column locked, row changes. Useful for lookup tables with fixed columns.

A$1

Row locked, column changes. Useful for headers or tax rates in a fixed row.

Magic Shortcut: F4 Key

When editing a formula, click on a cell reference and press F4 to cycle through reference types:

Press F4 once: A1 → $A$1 (absolute)

Press F4 twice: $A$1 → A$1 (mixed: row locked)

Press F4 three times: A$1 → $A1 (mixed: column locked)

Press F4 four times: $A1 → A1 (back to relative)

Real-World Example: Sales Tax Calculation

Cell D1 contains tax rate: 8.5%

You want to calculate tax for items in column B:

C2: =B2*$D$1

Copy this down. B2 changes to B3, B4, etc. (relative), but $D$1 stays locked to the tax rate (absolute).

Paste Special: Advanced Copy Options

Control exactly what gets pasted—formulas, values, formatting, or combinations

Sometimes you want to copy a formula without its formatting, or paste only the calculated values without the formula itself. Paste Special gives you precise control.

Values

Paste Values Only

Converts formulas to their calculated results. Use when sharing data with others who shouldn't see formulas.

Ctrl+C → Ctrl+Alt+V → V → Enter

Formulas

Paste Formulas Without Formatting

Copies formulas but keeps destination cell formatting intact. Prevents unwanted color/border changes.

Ctrl+C → Ctrl+Alt+V → F → Enter

Transpose

Flip Rows to Columns (or vice versa)

Pastes horizontal data vertically or vertical data horizontally while maintaining formula logic.

Ctrl+C → Ctrl+Alt+V → E → Enter

Common Errors When Copying Formulas

⚠️
#REF! Error: Occurs when you copy formulas referencing cells that don't exist in the new location. Example: copying =A1 to row 500 works, but copying =A1 upward from row 1 creates #REF! because there's no row 0.
⚠️
Circular Reference Warning: Copying a formula that inadvertently references its own cell creates an infinite loop. Excel will warn you with "There are one or more circular references."
⚠️
Forgetting to Lock References: Not using $ signs when needed causes fixed values (like tax rates or conversion factors) to shift, producing wrong calculations.
⚠️
Copying Merged Cells: Formulas in merged cells often don't copy correctly. Unmerge first, then copy formulas, then re-merge if necessary.
⚠️
Hidden Rows/Columns: When copying across hidden cells, formulas may skip those cells unexpectedly. Unhide all rows/columns before copying for predictable results.

Pro Tips for Copying Formulas Efficiently

💡
Copy to entire column instantly: Select the cell with formula, press Ctrl+C, then click column header (e.g., "B"), press Ctrl+V. Formula copies to all rows in that column.
💡
Use Ctrl+D to fill down: Select the formula cell and empty cells below it, press Ctrl+D to instantly fill down. Faster than dragging for large ranges.
💡
Use Ctrl+R to fill right: Same as Ctrl+D but copies formulas across columns to the right. Select horizontal range, press Ctrl+R.
💡
Show formulas with Ctrl+`: Press Ctrl+` (grave accent, usually above Tab key) to toggle formula view. Verify all formulas copied correctly before hiding them again.
💡
Name your cells: Instead of =$D$1, create a named range "TaxRate" and use =B2*TaxRate. Named ranges are automatically absolute and more readable.
💡
Audit formulas with trace precedents: Formulas tab → Trace Precedents shows arrows indicating which cells each formula references. Helps catch copy errors visually.

Quick Reference: Keyboard Shortcuts

Action	Windows	Mac
Copy	Ctrl+C	⌘+C
Paste	Ctrl+V	⌘+V
Paste Special	Ctrl+Alt+V	⌘+Ctrl+V
Fill Down	Ctrl+D	⌘+D
Fill Right	Ctrl+R	⌘+R
Toggle Reference Type ($)	F4	⌘+T or Fn+F4
Show/Hide Formulas	Ctrl+`	Ctrl+`

Need Help Creating Excel Formulas?

Our AI-powered formula generator creates custom Excel formulas based on your description—no manual syntax required

Generate Formula Free