How Do I Copy a Formula in Excel: Complete Guide to Copying Formulas Across Cells, Rows, and Columns
How do I copy a formula in Excel? Master fill handle, Ctrl+D, paste special, and absolute references with this complete step-by-step tutorial.

If you have ever found yourself wondering how do I copy a formula in Excel without breaking the references or retyping the same calculation across hundreds of rows, you are in the right place. Copying formulas is one of the most fundamental skills in Excel, and mastering it transforms a simple spreadsheet into a powerful, dynamic analytical tool. Whether you are building a financial model, tracking inventory, or running a payroll workbook, knowing how to copy formulas correctly will save you hours of manual work each week. Practice with the free Excel practice test on PracticeTestGeeks (500+ questions with answer explanations).
Excel provides several distinct ways to copy formulas across cells, including the fill handle, keyboard shortcuts like Ctrl+C and Ctrl+V, the Ctrl+D and Ctrl+R fill commands, and the Paste Special menu. Each method behaves slightly differently, especially when it comes to handling relative, absolute, and mixed cell references. Choosing the right approach is critical, because copying a formula incorrectly can silently break calculations and lead to reporting errors that go unnoticed for months.
The fill handle, that small green square at the bottom-right corner of a selected cell, is by far the most popular method. You simply click and drag it down, up, left, or right to copy the formula to adjacent cells. Excel automatically adjusts the cell references based on the direction you drag, which is called relative referencing. This single feature is what makes spreadsheets so much more efficient than calculators or paper ledgers.
However, the fill handle is not always the best choice. When you need to copy a formula to thousands of rows, dragging becomes impractical. That is when keyboard shortcuts and double-click tricks shine. Double-clicking the fill handle automatically copies the formula down a column as long as the adjacent column has continuous data. This trick alone can shave minutes off your daily workflow if you process large datasets.
Beyond simple copying, you also need to understand how Excel handles cell references when you copy a formula. A relative reference like A1 will change to A2 when copied down one row. An absolute reference like $A$1 stays locked no matter where you paste it. Mixed references like $A1 or A$1 lock only the column or the row. Mastering the dollar sign and the F4 key is essential to copying formulas that produce the correct results every time.
This guide walks you through every method Excel offers for copying formulas, including practical examples, common pitfalls, and advanced techniques like vlookup excel formulas copied across lookup tables. By the end, you will be able to handle any copy-paste scenario with confidence, whether you are working in Excel 365, Excel 2021, or older versions like Excel 2016. Let us dig into the mechanics of how formula copying actually works under the hood.
One more thing to know before we start: Excel always copies the formula itself, not the displayed value. If cell B2 shows 100 because A2 contains the formula =50+50, copying A2 to A3 will copy the formula and recalculate, not the literal number 100. If you want to copy the value instead of the formula, you need Paste Special > Values, which we will cover in detail below.
Microsoft Excel: upcoming dates and official changes
- Jun 30, 2026: Certiport retired the Microsoft Office Specialist 2016 certifications, including MOS Excel 2016, on June 30, 2026. The Microsoft 365 Apps and Office 2019 Excel exams are still available. (certiport.pearsonvue.com)
- Updated Oct 16, 2026: Delivery is now Proctored live-in-the-application exam via Certiport Authorized Testing Centers (in-center or remote-proctored) (previously Proctored live-in-the-application exam via Certiport Authorized Testing Centers).
- Updated Oct 16, 2026: Eligibility is now No formal prerequisites; about 150 hours of instruction and hands-on experience recommended (previously No formal prerequisites; ~150 hours of instruction and hands-on experience recommended (Associate)).
- Updated Oct 16, 2026: Time limit is now 50 minutes (60 minutes seat time) (previously 50 minutes (60 minutes seat time)).
Facts verified Oct 17, 2026 against Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business. Dates that have passed are removed automatically.
- The MOS Excel (Microsoft 365 Apps) exam has 33 to 37 questions. (certiport.pearsonvue.com)
- The MOS Excel Expert (Microsoft 365 Apps) exam has 23 to 27 questions. (certiport.pearsonvue.com)
- Microsoft Office Specialist Excel exams last 50 minutes, with 60 minutes of total seat time. (Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business)
- The passing score for Microsoft Office Specialist Excel exams is always 700 on Certiport's 1 to 1000 scaled score. (certiport.pearsonvue.com)
- After failing a Microsoft Office Specialist Excel exam, candidates must wait 24 hours before the first retake and 48 hours before each later retake, and there is no annual limit on attempts. (Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business)
- Microsoft Office Specialist Excel certifications earned on or after September 23, 2025 are valid for five years; those earned earlier do not expire. (certiport.pearsonvue.com)
- The Microsoft Office Specialist Excel exam is proctored and delivered live in the Excel application through Certiport Authorized Testing Centers, either in person or remote-proctored. (Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business)
- Microsoft recommends that candidates for the MOS Excel Associate (Microsoft 365 Apps) certification have about 150 hours of instruction and hands-on experience with Excel. (Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business)
- Candidates get their MOS Excel score report as soon as they finish the exam, and it can also be viewed in their Certiport profile. (certiport.pearsonvue.com)
Facts verified Oct 17, 2026 against Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business. Dates that have passed are removed automatically.
Microsoft Excel Exam at a Glance
| Item | Detail | Official source |
|---|---|---|
| Number of questions (Excel Associate (MO-210)) | 33-37 questions | certiport.pearsonvue.com |
| Number of questions (Excel Expert (MO-211)) | 23-27 questions | certiport.pearsonvue.com |
| Time limit | 50 minutes (60 minutes seat time) | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Passing score | 700 on a 1โ1000 scale | certiport.pearsonvue.com |
| Retake policy | Wait 24h after 1st fail, 48h before each later retake; no annual limit | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Validity | Valid 5 years (earned on/after Sept 23, 2025) | certiport.pearsonvue.com |
| Delivery | Proctored live-in-the-application exam via Certiport Authorized Testing Centers (in-center or remote-proctored) | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Eligibility | No formal prerequisites; about 150 hours of instruction and hands-on experience recommended | Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business |
| Score release | Score report at end of exam; also in Certiport profile | certiport.pearsonvue.com |
Facts verified Oct 17, 2026 against Microsoft (Microsoft Office Specialist program), delivered by Certiport, a Pearson VUE business. Dates that have passed are removed automatically.
Free Excel Study Tools
Interactive Excel resources on PracticeTestGeeks, free to start:
- Free Excel Questions and Answers โ 16 questions with instant scoring and answer explanations
- Excel flashcards โ review key terms and concepts card by card
- Excel study guide โ topic-by-topic overview of what the exam covers
- Excel cheat sheet โ one-page printable summary for last-minute review
Copying Formulas in Excel by the Numbers

Five Methods to Copy a Formula in Excel
Drag the Fill Handle
Double-Click the Fill Handle
Copy and Paste with Ctrl+C / Ctrl+V
Use Ctrl+D or Ctrl+R
Paste Special
Understanding cell references is the single most important concept when copying formulas in Excel. There are three types of references you need to know: relative, absolute, and mixed. A relative reference such as A1 automatically adjusts when you copy the formula to another cell. If you copy a formula containing A1 from B1 to B2, the reference shifts to A2. This is the default behavior and it is what makes Excel so useful for repetitive calculations across rows or columns.
An absolute reference, written as $A$1, locks both the column and the row. No matter where you paste the formula, $A$1 will always point to cell A1. This is essential when you have a constant value, such as a tax rate or exchange rate, that needs to be referenced by every calculation in a column. To toggle between reference types quickly, click on the reference in the formula bar and press F4 repeatedly. Each press cycles through A1, $A$1, A$1, and $A1.
Mixed references combine the two. $A1 locks the column but lets the row change, while A$1 locks the row but lets the column change. These are powerful in multiplication tables, two-way lookups, and amortization schedules. For example, if you build a sales tax matrix where rows are products and columns are states, you would use mixed references to copy a single formula across the entire grid and still get correct results in every cell.
A common related task is using vlookup excel formulas across multiple rows. When you write =VLOOKUP(A2, $D$2:$E$100, 2, FALSE) and copy it down, A2 becomes A3, A4, and so on, while the lookup table $D$2:$E$100 stays locked. Forgetting the dollar signs around the lookup range is one of the most frequent reasons VLOOKUP returns N/A errors after copying. Always lock your table arrays before copying any lookup formula.
Excel also offers structured references when you work inside an Excel Table. Instead of A2:A100, you would see something like Table1[Sales]. These references behave like a hybrid of relative and absolute, automatically expanding as the table grows. Copying a formula inside a structured table is even easier because Excel typically fills the entire column for you the moment you press Enter. This auto-fill behavior makes tables ideal for dashboards and recurring reports.
The R1C1 reference style is another option that some advanced users prefer. Instead of letters and numbers like A1, references appear as R1C1, R2C1, etc. This style makes relative offsets explicit. For example, R[-1]C means the cell one row above in the same column. You can toggle this in File > Options > Formulas. While most users stick with A1 style, R1C1 is sometimes easier for VBA macro work or complex offset calculations.
Finally, keep in mind that Excel evaluates formulas in calculation order, not visual order. When you copy a formula that references another cell which also contains a formula, Excel chains the dependencies and recalculates the entire workbook. For very large models, you may want to enable manual calculation mode through Formulas > Calculation Options to prevent slowdowns while pasting many formulas at once.

Microsoft Excel Practice Test Questions
Prepare for the Microsoft Excel exam with our free practice test modules. Each quiz covers key topics to help you pass on your first try.
Microsoft Excel Excel Basic and Advance
Microsoft Excel Exam Questions covering Excel Basic and Advance. Master Microsoft Excel Test concepts for certification prep.
Microsoft Excel Excel Formulas
Free Microsoft Excel Practice Test featuring Excel Formulas. Improve your Microsoft Excel Exam score with mock test prep.
Microsoft Excel Excel Functions
Microsoft Excel Mock Exam on Excel Functions. Microsoft Excel Study Guide questions to pass on your first try.
Microsoft Excel Excel MCQ
Microsoft Excel Test Prep for Excel MCQ. Practice Microsoft Excel Quiz questions and boost your score.
Microsoft Excel Excel
Microsoft Excel Questions and Answers on Excel. Free Microsoft Excel practice for exam readiness.
Microsoft Excel Excel Trivia
Microsoft Excel Mock Test covering Excel Trivia. Online Microsoft Excel Test practice with instant feedback.
Step-by-Step: VLOOKUP Excel Formula Copy Methods
The fill handle is the small green square at the bottom-right corner of the active cell or selection. To copy a formula, click on the cell containing the formula, hover over the green square until your cursor becomes a thin black cross, then drag in the direction you want to fill. Release the mouse button when you reach the last cell. Excel adjusts relative references as it copies.
If you need to fill thousands of rows, double-clicking the fill handle is much faster than dragging. Excel detects the last row of data in the adjacent column and stops there. Be careful if your adjacent column has blank cells, because the fill will stop at the first gap. To avoid this, ensure your dataset is continuous before double-clicking, or use the Ctrl+D method described in the next tab.
Fill Handle vs Keyboard Shortcuts: Which Should You Use?
- +Fill handle is visual and intuitive for new Excel users
- +Double-clicking the fill handle fills thousands of rows in milliseconds
- +Ctrl+D and Ctrl+R do not overwrite the clipboard buffer
- +Paste Special offers fine-grained control over formulas, values, and formats
- +Keyboard shortcuts are faster once memorized for repetitive tasks
- +Copy-paste works across worksheets and even different workbooks
- โFill handle stops at empty cells in adjacent columns when double-clicked
- โDragging the fill handle is slow and error-prone for very long ranges
- โCtrl+C and Ctrl+V can accidentally overwrite formatting in destination cells
- โBeginners often confuse relative and absolute references when copying
- โPaste Special dialog has 18 options that can overwhelm new users
- โVLOOKUP and similar lookup formulas break if absolute references are missing
Pros and Cons at a Glance
| Pros | Cons |
|---|---|
| Fill handle is visual and intuitive for new Excel users | Fill handle stops at empty cells in adjacent columns when double-clicked |
| Double-clicking the fill handle fills thousands of rows in milliseconds | Dragging the fill handle is slow and error-prone for very long ranges |
| Ctrl+D and Ctrl+R do not overwrite the clipboard buffer | Ctrl+C and Ctrl+V can accidentally overwrite formatting in destination cells |
| Paste Special offers fine-grained control over formulas, values, and formats | Beginners often confuse relative and absolute references when copying |
| Keyboard shortcuts are faster once memorized for repetitive tasks | VLOOKUP and similar lookup formulas break if absolute references are missing |

Checklist Before You Copy a Formula in Excel
- โVerify that your source formula produces the correct result in its current cell
- โIdentify which cell references should be relative versus absolute using $
- โPress F4 on each reference in the formula bar to toggle reference types
- โConfirm the adjacent column has continuous data if using double-click fill
- โChoose between Ctrl+C/V, Ctrl+D, fill handle, or Paste Special based on need
- โUse Paste Special > Formulas to preserve destination cell formatting
- โUse Paste Special > Values when you need to lock in calculated results
- โLock lookup table arrays with $ signs before copying VLOOKUP or INDEX-MATCH
- โCheck the first and last copied cells to confirm references adjusted properly
- โEnable Show Formulas (Ctrl+`) to audit all pasted formulas at once
Save Hours with One Double-Click
If you regularly copy formulas down thousands of rows, stop dragging. Click the source cell, hover over the bottom-right green square until the cursor becomes a thin cross, then double-click. Excel will fill the formula down the entire column automatically as long as the adjacent column has continuous data. This single trick can save you several minutes per spreadsheet every single day.
Even experienced Excel users run into copy-formula errors, and most of them stem from misunderstanding how references behave. The most common mistake is forgetting to add dollar signs to a lookup range. Imagine you write =VLOOKUP(A2, D2:E100, 2, FALSE) in cell F2 and copy it to F100. Without dollar signs, the lookup table shifts to D100:E198 in the last row, missing most of your data and returning N/A. Always lock the table array with $D$2:$E$100 before copying.
Another frequent error is the #REF! error, which appears when a copied formula references a cell that no longer exists. This happens when you copy a formula that points to a cell above its source up into row 1, or to the left of column A. There is simply no row 0 or column zero, so Excel returns #REF!. To fix this, either use IFERROR to handle the edge cases or restructure your formula to avoid referencing cells outside the worksheet boundary.
Circular reference warnings can also appear after copying. This happens when a formula directly or indirectly refers to its own cell. If you copy a SUM formula into one of the cells it sums, you create a circular loop. Excel will pop a warning, and the cell will display zero or an incorrect total. Check your formula range carefully and exclude the destination cell from any aggregation ranges before copying.
The #VALUE! error after copying usually means a formula expected a number but received text, often because of a relative reference shift into a header row or text column. Convert text to numbers using the VALUE function, or anchor the reference with absolute notation so it does not drift into the wrong data type. Similarly, #DIV/0! errors after copying point to a divisor cell that became blank or zero in the new location.
A subtler problem is formatting loss. When you use Ctrl+V to paste a formula, the destination cell inherits the source formatting, including font, fill color, and borders. This can wreck a carefully designed worksheet. To avoid it, use Paste Special > Formulas instead, which copies only the formula and preserves the destination formatting. You can also press Ctrl+Alt+V then F then Enter as a quick keyboard sequence to paste formulas only.
Some users encounter problems when copying formulas between worksheets or workbooks. If the source formula references cells like Sheet1!A1, those references move with the formula when pasted into another sheet. To avoid broken references across workbooks, use full file paths with bracket notation, or convert to values before sharing. Linked workbooks can also break when the source file is renamed, moved, or stored on a different drive than the destination.
Finally, watch out for hidden rows and columns. The fill handle and Ctrl+D both fill across hidden cells by default, which means hidden rows get populated with formulas you might not expect. If you want to skip hidden cells, copy the visible cells only using Alt+; (Alt-semicolon) to select visible cells before pasting. This is essential when working with filtered datasets where some rows are temporarily hidden.
The #1 reason VLOOKUP, INDEX-MATCH, and SUMIFS formulas break after copying is missing dollar signs on the lookup range. Before you drag, double-click, or Ctrl+D, click into the formula bar, select your table array, and press F4 to lock both rows and columns. Skipping this step is the most common cause of silent data errors in business spreadsheets.
Beyond the basic copy methods, Excel offers several advanced techniques that experienced users rely on every day. The Name Manager (Ctrl+F3) lets you assign a name to a cell or range, such as TaxRate for cell B1. Once named, you can use =A2*TaxRate in any formula and copy it anywhere in the workbook without worrying about absolute references. Named ranges are effectively always absolute and they make your formulas dramatically more readable for anyone auditing your work later.
Array formulas, introduced as dynamic arrays in Excel 365, change the copy game entirely. Functions like FILTER, SORT, UNIQUE, and SEQUENCE spill results into adjacent cells automatically. You write the formula once in one cell, and Excel populates a range of cells with the output. There is no need to copy the formula at all because the array spills. This makes dynamic dashboards and reporting tools significantly easier to maintain than traditional drag-down formulas.
For repetitive tasks, recording a macro to copy formulas across specific ranges can save hours. Press Alt+F8 to open the macros dialog, record your formula copy actions once, and replay them with a single click on future workbooks. VBA gives you even more power, including conditional copying, error handling, and bulk operations across multiple worksheets. Even basic macro recording without learning VBA syntax is a huge productivity boost for accountants and analysts.
Power Query offers another approach for handling large datasets. Instead of copying formulas down millions of rows, you load the data into Power Query, apply transformations once, and load the result back into Excel. Power Query is faster, more reliable, and easier to audit than thousands of pasted formulas. It is included free with Excel 2016 and later under the Data tab as Get & Transform.
If you frequently work with templates, consider using table autofill. When you type a formula in any cell within an Excel Table (Ctrl+T to create one), Excel automatically copies it to every other row in that column. New rows added to the bottom of the table inherit the formula instantly. This eliminates the need to copy formulas after each data import, which is a huge time saver for monthly reporting workflows or live data dashboards.
Conditional copy-paste using the Go To Special menu (F5 then Special) lets you target only blanks, only formulas, only constants, or only visible cells. For example, to fill blanks in a column with the value above, select the range, press F5, click Special, choose Blanks, then type =A1 (or whatever cell is appropriate) and press Ctrl+Enter. This fills every blank with the correct relative reference in one keyboard sequence, which is extremely efficient.
Finally, do not overlook the simple right-click drag method. Hold the right mouse button while dragging the fill handle, and Excel pops up a context menu when you release. The menu lets you choose Copy Cells, Fill Series, Fill Formatting Only, Fill Without Formatting, and several other intelligent options. This is the secret sauce many Excel trainers teach for fastest formula management with maximum flexibility on a per-drag basis.
Now that you understand the mechanics and pitfalls of copying formulas in Excel, let us cover the practical tips that separate competent users from true Excel wizards. First, always preview a few sample cells after copying a large range. Check the formula in the first row, a middle row, and the last row to confirm references adjusted as expected. This three-spot check takes ten seconds and catches 95 percent of copy-paste errors before they cause downstream problems in reports.
Second, build the habit of using Ctrl+` (backtick) to toggle Show Formulas mode. This view replaces calculated values with the underlying formulas, making it easy to audit every cell in a worksheet at once. After a big copy operation, switch to Show Formulas, scan the column, and verify all references look consistent. Press Ctrl+` again to return to normal view. This single keyboard shortcut is the fastest way to QA a freshly populated spreadsheet.
Third, learn to love Paste Special. The dialog has options for Formulas, Values, Formats, Comments, Validation, All Using Source Theme, All Except Borders, Column Widths, Formulas and Number Formats, and Values and Number Formats. Each combination solves a specific problem. The most useful for daily work are Values (to lock in numbers) and Formulas (to copy logic without disturbing formatting). Memorize Ctrl+Alt+V as your gateway to all of these.
Fourth, when working in shared workbooks, always convert critical formulas to values before sending. This prevents recipients from accidentally breaking your model when they paste their own data on top of source cells. Use Ctrl+A, Ctrl+C, then Ctrl+Alt+V > V > Enter to convert an entire worksheet from formulas to values in one quick sequence. Keep a backup with formulas intact for your own future updates and analysis.
Fifth, consider documenting your formulas with cell comments or a separate documentation sheet, especially in workbooks that other people will maintain. Right-click any cell and choose New Comment to add a note explaining what a formula does, which references are deliberately absolute, and any assumptions baked into the math. Future you and your colleagues will appreciate this discipline more than any other Excel habit on this list, guaranteed.
Sixth, practice with real datasets rather than artificial examples. Download free sample workbooks from financial databases, public records, or sports statistics sites and try to replicate the calculations using only formula copying. Repetition with varied data builds the intuition you need to handle any copy-paste challenge that comes your way at work. Practice exams and quizzes are also excellent tools for building speed under realistic conditions and time pressure.
Finally, never stop learning. Excel updates introduce new functions, new shortcuts, and new copy methods every year. Dynamic arrays, LAMBDA functions, and the new XLOOKUP are recent additions that fundamentally change how formulas are written and copied. Subscribe to a reputable Excel blog, follow YouTube tutorials, and take a free certification practice test once a quarter to keep your skills sharp. Mastery is a journey, not a destination, and Excel rewards the curious dedicated student handsomely over time.
Sample Excel Practice Questions
Try these questions from our free Excel practice tests. The correct answer and an explanation follow each question.
A user wants to copy a range of cells containing formulas (A1:A10) to a new location (B1:B10), but needs to paste only the calculated values, not the formulas themselves. Which Paste Special option should be used?
- A. Formulas
- B. Values
- C. Formats
- D. Transpose
Answer: B. Values
The 'Values' option in Paste Special is used to paste the static, calculated results of formulas. This action removes the underlying formulas, leaving only the visible data.
Which formula counts cells in A1:A20 that contain values greater than 50?
- A. =COUNT(A1:A20,">50")
- B. =COUNTIF(A1:A20,">50")
- C. =SUMIF(A1:A20,">50")
- D. =COUNTIFS(A1:A20,50)
Answer: B. =COUNTIF(A1:A20,">50")
COUNTIF counts cells matching a single criterion; ">50" selects cells with values greater than 50.
A financial analyst created the formula `=C2*D2` in cell E2 to calculate the total cost. They want to copy this formula down to cell E10. However, the tax rate used in the calculation is in a fixed cell, G1. Which formula should be entered in E2 before copying it down to ensure the tax rate reference does not change?
- A. =C2*D2+$G$1
- B. =(C2*D2)*$G1
- C. =C2*$G$1
- D. =C2*G1
Answer: C. =C2*$G$1
Using dollar signs ($) creates an absolute reference. `$G$1` locks both the column 'G' and the row '1', so when the formula is copied down, the reference to cell G1 remains constant, while the relative reference C2 will adjust to C3, C4, etc.
A user enters the formula `=FILTER(A2:D50, C2:C50>100)` into cell F2. The result of this formula automatically populates cells F2 through I10. This behavior is known as:
- A. Spilling
- B. Arraying
- C. Dropping
- D. Cascading
Answer: A. Spilling
Spilling is the behavior in modern Excel where a formula that produces multiple results automatically populates, or 'spills', into adjacent cells. Dynamic array functions like FILTER, SORT, and UNIQUE exhibit this behavior.
Excel Questions and Answers
Written and edited by Nathan Beja, founder of Practice Test Geeks, with AI assistance. Exam facts are checked against the official sources linked below. Editorial policy
Sources & references
Exam facts on this page (fees, format, dates, passing scores) are checked against these official sources; the date is our last check. Editorial policy
- learn.microsoft.com โ Retake policy, test delivery, time limit, eligibility ยท verified Oct 2026
- certiport.pearsonvue.com โ Exam format, score validity, passing score, score release, exam information ยท verified Oct 2026




