Copy Formulas in Google Sheets: Step-by-Step Guide
Learn how to copy a formula in Google Sheets with clear steps, practical examples, and tips. This How To Sheets guide covers relative and absolute references, cross-sheet copying, and converting results to values.
This guide shows how to copy a formula across cells, columns, and sheets in Google Sheets. You'll learn the fill handle method, drag-fill, copy-paste, and paste special options, plus how to manage relative and absolute references. This foundation speeds up calculations and reduces errors, with practical examples and tips. This quick answer outlines core methods you’ll use daily in 2026. This quick answer outlines core methods you’ll use in daily work.
What copying a formula really does in Sheets
When you copy a formula, Google Sheets recalculates the referenced cells relative to the new location. This is the core idea behind formula replication: most references adjust automatically unless you explicitly lock them with absolute references. If you're asking how to copy formula in google sheets, you're not alone—this is a fundamental skill that boosts speed and reduces errors in everyday data work. According to How To Sheets, understanding how replication works is the first step toward building reliable spreadsheets. For example, a simple formula in C1 such as =A1+B1 will become =A2+B2 when you drag the fill handle down to C2. That automatic adjustment is the power of relative references in action. As you copy further, Sheets applies the same logic to each new row or column, preserving the formula structure while updating the cell addresses. Recognize the difference between copying a single cell vs. copying a block; the same principle applies, but the propagation pattern changes depending on the direction you choose and whether you include mixed references. This awareness helps you design more scalable templates and reduces the need for manual edits later on.
Basic methods to copy formulas
There are several reliable methods to copy formulas in Google Sheets, each suited to different scenarios. The most common is using the fill handle: click the bottom-right corner of the formula cell and drag across the target range. This quickly extends the formula while updating relative references along the way. If you need to fill down a long column, you can double-click the fill handle, and Sheets will auto-fill as far as there is data in the adjacent column. Keyboard shortcuts speed up the process: select the source cell, press Ctrl+C (Cmd+C on macOS), move to the destination range, and press Ctrl+V (Cmd+V). Another approach is to copy the formula to a non-adjacent area by selecting the range first, then using Paste (Ctrl+V) to place the formula where needed. You can also use Ctrl+D to fill down when the destination is directly below the source. Each method preserves the core logic of the formula, with relative addresses updating automatically for adjacent cells. Remember to verify a few cells after copying to confirm that references updated correctly and that no unintended absolute references were introduced.
Relative vs absolute references when copying
The behavior of copied formulas hinges on whether references are relative or absolute. A relative reference like A1 changes when you copy the formula to another cell, producing A2, B3, etc., depending on the movement. An absolute reference uses a dollar sign to lock either the row, the column, or both (for example, $A$1 locks both, A$1 locks the row, and $A1 locks the column). When you copy a formula that contains a mix of relative and absolute references, Google Sheets updates only the parts that are not locked. This means you can copy a formula across columns to apply the same calculation to different data columns, while keeping a specific constant reference intact. A common pattern is to fix a lookup table with absolute references and let the data references move. If your goal is to copy a formula across a large range while maintaining certain constants, plan the references accordingly before you start copying. The ability to control how references shift is what makes copying formulas powerful, not error-prone, once you understand the rules.
Copy across sheets and multiple workbooks
Copying a formula across different sheets or workbooks is a frequent task for consolidating data. Start by selecting the cell with the formula and copying it (Ctrl+C). Navigate to the target sheet and paste (Ctrl+V). If the formula references other sheets, you may want to adjust the references after pasting, or use indirect references to keep cross-sheet links intact. When moving formulas between workbooks, you can paste into the target workbook; Google Sheets will adapt internal references, but external references to other workbooks may require you to re-establish sharing or re-check links. If you need the same calculation to appear in many sheets, consider using named ranges that maintain stability even when sheets are reordered. This practice reduces maintenance and makes your template scalable. In all cases, verify that pasted formulas still point to the intended data, especially in complex templates that rely on multiple data sources or dynamic ranges.
Paste special options: values, formats, and more
Sometimes you want the result of a copied formula rather than the formula itself. In Google Sheets, you can paste as values to freeze results, preserving the calculated results while removing the underlying formula. Use Edit > Paste special > Paste values only (or the keyboard shortcut Ctrl+Shift+V on many systems) after copying. You can also paste only formats, or both values and formats, depending on your needs. If you’re copying into a template with conditional formatting, consider pasting values first, then adjusting formats to avoid unintended style changes. For advanced workflows, you can use Apps Script or add-ons to automate the paste-special process, but most tasks are easily accomplished with built-in features. Understanding these options helps you maintain clean data while reusing robust formulas across your workbook.
Practical examples, templates, and a quick checklist
To make the concept concrete, let’s look at practical examples and a simple template you can reuse. Example 1: Sum across a column. Suppose you have numbers in B2:B100 and you want to add them with a constant tax rate in C2. In C2 you might have =B21.07. Copy this formula down to C3:C100. The relative reference to B2 will become B3, B4, etc., while the constant 1.07 remains fixed if you used absolute references for it, such as =B2$A$1 where A1 holds the tax rate. Example 2: VLOOKUP across a data table. If you place =VLOOKUP(D2, Data!$A$2:$C$100, 3, FALSE) in E2, copying down will adjust only the row of D2 while the table reference remains fixed thanks to absolute addressing. Example 3: A template for monthly totals. Build a single formula in G2 to compute subtotal from your daily data, then copy across columns for each month. A simple checklist for successful copying includes: verify relative/absolute references before large copies, test a few cells after copying, and ensure you’re not overwriting anything important. How To Sheets emphasizes practicing with a small sample before applying to real datasets. Use this approach to create robust templates that scale as your data grows.
Troubleshooting common issues
Often, issues arise when a copied formula behaves unexpectedly. Common culprits include unintended absolute references that prevent updates, copying across non-adjacent ranges that change the wiring of references, or protected ranges that block edits. If you see #REF! after copying, check the source references and any updated sheet references. When copying across sheets, ensure the target sheet has identical data layout or adjust references accordingly. If a formula uses a function that requires a dynamic reference (like INDIRECT), copying won't preserve it without modification. Finally, always test a handful of cells to confirm that the results match your expectations. The How To Sheets team recommends practicing on a dummy dataset to minimize impact on actual work and to build muscle memory for reliable formula replication.
Tools & Materials
- Google Sheets document (with editing access)(Open the file in a web browser or mobile app)
- Computer or device with internet(Stable connection recommended)
- Mouse or trackpad(For precise clicks and drag actions)
- The formula you plan to copy(Confirm logic and references before copying)
- Keyboard shortcuts (Ctrl+C/V, Ctrl+D, Ctrl+S)(Speeds up copying and filling tasks)
- Paste Special capability (values, formats)(Use Edit > Paste special as needed)
- Practice dataset (optional)(Helpful for safe experimentation)
Steps
Estimated time: 20-30 minutes
- 1
Open the sheet and locate the formula
Navigate to the cell that contains the formula you want to copy. Verify the formula is correct and note any absolute references that should stay fixed when copied.
Tip: If you plan to copy across many columns, consider planning which references must stay fixed before you start. - 2
Select and copy the source cell
Select the formula cell and copy it using Ctrl+C (Cmd+C on macOS). Have the destination area ready before you paste.
Tip: Copy to a nearby area first to confirm behavior before large-scale replication. - 3
Use the fill handle to extend the formula
Drag the small square at the bottom-right corner of the cell across the target range to fill the formula. Watch references adjust as you go.
Tip: Hold Shift to extend a straight line while dragging. - 4
Double-click to auto-fill down a column
If your adjacent column has data, double-click the fill handle to auto-fill down to the last row with data.
Tip: Ensure there is data on the adjacent column; otherwise Sheets may stop early. - 5
Copy to a non-adjacent range
Select the first destination cell, then paste (Ctrl+V) to place the formula where needed, even if the area is not contiguous.
Tip: You can also use Copy > Paste Special if you need values instead of formulas. - 6
Copy across sheets within the same workbook
Switch to the target sheet and paste the formula. Review references; absolute references will stay fixed if intended.
Tip: Named ranges can help keep references stable when reorganizing sheets. - 7
Copy into a different workbook
Copy the formula and paste into the other workbook. Re-check any external references and re-share if needed.
Tip: When dealing with external data, consider using named ranges or INDIRECT with care. - 8
Convert to values if needed
If you want the results only, paste as values to remove the underlying formula while keeping results.
Tip: Paste values last to avoid losing recent edits.
FAQ
How do I copy a formula to adjacent cells quickly?
Use the fill handle by clicking the bottom-right corner of the formula cell and dragging across the desired range. Double-clicking the handle also auto-fills down a column when data exists in the adjacent column.
Use the fill handle to quickly fill adjacent cells, or double-click for automatic filling if there’s data beside it.
Can I copy formulas across different sheets in the same workbook?
Yes. Copy the formula, switch to the target sheet, and paste. Review references to ensure relative parts adjust correctly while fixed references stay the same if intended.
You can copy across sheets; just paste and verify that references update as planned.
What’s the difference between relative and absolute references when copying?
Relative references change as the formula is copied, while absolute references remain fixed. Mix them thoughtfully to control which parts shift and which stay constant.
Relative references move with the copy; absolute references stay fixed. Plan your references before copying.
How do I copy a formula as a value instead of the formula itself?
After copying, use Paste special > Paste values only to keep the computed results but remove the formulas.
Paste as values to keep results without the formulas.
Why might a copied formula show #REF! after pasting?
This usually indicates broken references or moved data. Check the source references and adjust any sheet or range references to restore a valid link.
If you see #REF!, check the references and fix the ranges accordingly.
Watch Video
The Essentials
- Copy formulas with the fill handle for speed
- Understand relative vs absolute references
- Verify references after large copies
- Use paste special to control results
- Test on a small sample before applying to full data
