Essential Excel Formulas: Copy, Subtract, Percent Change, and More
By
Liz Fujiwara
•
When you build a formula to calculate quarterly profit in Microsoft Excel and need to apply it across 800 rows, manually entering that formula 800 times creates unnecessary work and introduces errors that could silently corrupt your financial model. Copying formulas correctly saves hours and prevents calculation mistakes that cascade through dashboards, burn rate projections, and cohort data. Even for AI-first startups, Excel remains a foundational tool for trustworthy analytics before data reaches production pipelines.
This tutorial starts with the basics of copying one cell to another and progresses to more advanced control, including locking references, handling non-adjacent cells, and troubleshooting broken formulas.
Key Takeaways
Copying formulas in Excel is all about controlling relative, absolute, and mixed cell references so calculations stay accurate when duplicated across rows or columns.
You can copy formulas with the fill handle, keyboard shortcuts like Ctrl + D or Ctrl + R, or standard copy and paste for flexible ranges, non-adjacent cells, and Excel Tables.
This article covers how to preserve or lock references when needed and connects Excel formula mastery to modern AI workflows, including how teams use Fonzi when hiring elite AI engineers.
How to Copy Excel Formulas Correctly
How a formula behaves when copied depends entirely on whether its references are relative, absolute, or mixed. Understanding this distinction is the foundation of all formula copying in Excel.
Relative references (like A2 or B5) shift automatically based on where you paste the formula. For example, copying =A2*B2 from cell C2 down to C3 becomes =A3*B3. Each row calculates its own values without manual editing.
Absolute references use dollar signs (like $A$2) to lock both the column and row. These do not move when copied. A practical use case is applying a fixed 7.65% tax rate stored in cell H1 across thousands of transactions. Copying =B2*$H$1 from D2 to D500 keeps the tax reference static while revenue adjusts relatively.
Mixed references combine both approaches. $A2 fixes the column but allows the row to shift, while A$2 fixes the row but lets the column move. This is useful for lookup tables or cross-referenced calculations.
The F4 key is your shortcut for toggling reference types. Position your cursor on any cell reference in the formula bar and press F4 to cycle through: A1 → $A$1 → A$1 → $A1.
Move vs. Copy a Formula in Excel
Moving and copying formulas produce different outcomes. Moving relocates the formula, so the original cell becomes empty, while copying duplicates it and leaves the original intact.
To move a formula: select a cell like C5 containing =A5-B5, press Ctrl + X (or use Home tab > Cut), select a new location like D8, and paste. The formula moves completely, with relative references adjusting to the new position.
To copy a formula: select the cell, press Ctrl + C (or click the Copy button on the Home tab), then paste it into another cell. The original formula remains, and relative references update based on the new position.
Pro tip: use Paste Special > Formulas under more paste options to paste only the calculation logic without number formatting. This is ideal when copying from a highlighted summary cell into a plain table without bringing over unwanted styles.
For many users, the fill handle, covered next, is faster than ribbon commands, but understanding move vs. copy is essential for troubleshooting unexpected results.
Copying a Formula Down a Column or Across a Row
Most real work involves copying a formula to multiple cells, such as down a 2,000-row sales list or across 12 months of 2025 data.
Start by creating a formula in the top cell. For example, enter =B2-C2 in cell D2 to calculate profit (revenue minus cost). Now locate the fill handle, the small plus sign in the lower right corner of the selected cell.
Drag method: When your mouse cursor becomes a thick black plus, drag down to D500. Relative references automatically advance row numbers, so D3 contains =B3-C3, D4 contains =B4-C4, and so on.
Double click method: Instead of dragging, double click the fill handle. Excel auto-fills the formula down to the last row of contiguous data in the adjacent column to the left. This is three to five times faster for datasets over 500 rows.
Copying across a row: Create a formula in B3 for January, such as =B2*$F$1 where F1 holds a growth rate, then drag the fill handle rightward through M3 for the complete year.
How to Copy a Formula Down a Column Without Copying Formatting
A common frustration is that dragging the fill handle copies both the formula and formatting, which can overwrite carefully designed alternating row colors or custom styles.
After dragging, a small Auto Fill Options icon appears near the filled range. Click it and select Fill Without Formatting. This applies the formulas while preserving existing fonts, borders, background colors, and number formats.
For example, if your expense tracker uses a grey-white alternating pattern, filling a “Net” column this way keeps those colors intact while populating the calculations across all cells.
Copying Formulas to the Entire Column or Specific Cells
For large datasets you need faster methods than dragging.
Method | Best For | How It Works |
Ctrl + Enter | Large ranges | Select D2:D10000, type =B2-C2, press Ctrl + Enter |
Ctrl + D | Fill Down | Select source cell plus target range, press Ctrl + D |
Ctrl + R | Fill Right | Select source cell plus target range, press Ctrl + R |
Non-adjacent paste | Sparse selections | Copy D2, Ctrl-click D10, D25, D40, then paste |
The Ctrl + Enter method is particularly powerful: select your entire target range first, type the formula once in the formula bar, then press Ctrl + Enter. Excel applies the adjusted formula to all selected cells instantly.
For non-adjacent cells, copy your original formula from D2, then Ctrl-click cells D10, D25, and D40, and paste. Each receives a version adjusted to its row, perfect for financial models with gaps.
Using Excel Tables to Auto-Copy Formulas
Excel Tables, created via Ctrl + T or Insert > Table, transform how formulas propagate. Unlike a simple range, Tables use structured references and auto-expand as you add data.
Convert a range like A1:D300 into a table, then enter a formula in a new column, such as “Margin.” Type =[@Revenue]-[@Cost] and the formula instantly populates the entire column using structured references instead of traditional cell addresses.
The real advantage is that when you add new rows at the bottom by pressing Tab, the formula automatically extends. No more manual fills for growing datasets. Microsoft benchmarks suggest this reduces errors by roughly 40% in dynamic reporting.
You can convert the Table back to a normal range via Table Design > Convert to Range if preferred, and formulas remain in place.
Copy a Formula in Excel Without Changing References
Sometimes you need the exact same formula in multiple locations without Excel adjusting any references, which is common when duplicating complex lookups or array formulas across sheets.
Copy as text method: Press F2 to enter edit mode or double click the cell, select the formula text like =SUM($B$2:$B$13)/$B$14, copy it with Ctrl + C, press Esc, navigate to your target cell, and paste. References remain exactly the same.
Formula bar method: Click the cell, highlight the formula in the formula bar, press Ctrl + C, press Esc, move to the target cell, and paste. This bypasses Excel’s reference adjustment entirely.
Ctrl + ‘ shortcut: Copies the formula from the cell directly above into the current cell without changing references, immediately entering edit mode for quick tweaks.
If you want references to stay fixed when using the fill handle or standard copy and paste, convert critical references to absolute ($A$2) using F4 before copying.
When Copied Formulas Don’t Work as Expected
Even experienced users encounter #REF! errors, identical results in every row, or obviously wrong percent change calculations after copying formulas. Here’s how to diagnose and fix common issues.
Incorrect relative references: The formula shifted from referencing your “Revenue” column to an empty column. Check the formula in multiple rows to see how references changed.
Missing $ signs: A fixed tax rate or currency cell value “walked” down the column because it lacked dollar signs. Convert these to absolute references ($A$2) before copying again.
Fill stopping mid-column: Double-click fill reached a blank cell in the adjacent column. Manually drag past the blank or temporarily fill the gap with placeholder data.
Dynamic arrays: Functions like FILTER or UNIQUE in Excel 365/2021+ spill automatically. Copying these may be unnecessary and can cause confusion if done repeatedly.
Common Error Types After Copying
Error | Typical Cause | Solution |
#REF! | Formula copied to position where referenced row/column doesn’t exist | Check if you pasted above row 1 or after deleting source columns |
#DIV/0! | Denominator cells are zero or blank in copied rows | Add IFERROR wrapper or explicit checks |
#N/A | VLOOKUP/XLOOKUP lookup value missing or range shifted | Ensure lookup range uses absolute references |
For any persistent error, compare the original formula and the copied one side-by-side in the formula bar to see exactly how references changed.
Conclusion
You’ve learned the essential techniques for copying formulas in Excel, including understanding relative, absolute, and mixed references, using the fill handle and keyboard shortcuts, copying with or without formatting, and diagnosing broken formulas after copying. Apply these skills to a real workbook this week. Update your revenue model, fix that cohort analysis spreadsheet, or build a new financial projection using proper reference control.
If you’re a startup founder, CTO, or AI leader who relies on spreadsheet-driven analytics, explore how Fonzi can help you hire AI engineers who are equally rigorous with data and code.
FAQ
How do I copy and paste a formula in Excel without breaking cell references?
What’s the subtraction formula in Excel and how do I use it across rows?
How do I calculate percent change between two values in Excel?
What’s the difference between relative and absolute cell references when copying formulas?
Why does my Excel formula show an error after copying it to another cell?
