Repeating a Formula in Excel

What this does

Repeating a formula down a column is not about typing it 200 times — Excel adjusts a plain reference like A2 to A3, A4, A5… automatically as the formula moves, because A2 is a RELATIVE reference: recorded as an offset from the formula's own cell, not as a fixed address. A reference locked with dollar signs, $A$1, is ABSOLUTE and stays pointed at that exact cell no matter where the formula is copied — essential for a tax rate, a lookup table, or any single value every row needs to share. Mixed references ($A1 or A$1) lock only the column or only the row. Getting relative vs. absolute wrong is the single most common cause of a formula that looks fine in row 2 and returns garbage by row 50. Most people learn this as a sequence of clicks and forget it by next week; learning it as a pattern instead is what lets you apply it to the next, slightly different version of the problem without starting from scratch. That is the difference this page is trying to make. The difference between a quick fix and a sheet you can trust is the extra minute you spend validating “repeating a formula in excel”. Start on a copy or a tiny sample, keep the affected formula visible, and compare the result with the tool above before you touch the real workbook. When this is a ribbon command, the selection matters more than the button: confirm the range, apply the command, then spot-check the output before saving. The point is a calculation you can defend to a CFO or an auditor, but the practical win is that someone else can open the file and understand what happened without asking you.

A worked example

C2 contains =A2*B2, multiplying quantity by unit price. Dragging the fill handle from C2 down to C50 produces =A3*B3 in C3, =A4*B4 in C4, and so on — exactly right, since each row needs its OWN quantity and price. If every row instead needs to multiply by a single tax rate sitting in F1, writing =A2*B2*F1 and filling down breaks immediately: row 3 becomes =A3*B3*F2, which is empty, so the result is 0. Writing =A2*B2*$F$1 instead keeps F1 locked in every copy: row 3 correctly becomes =A3*B3*$F$1. Almost every real spreadsheet has a formula that needs to run down hundreds of rows, and whether that goes right or produces a column of silent errors comes down entirely to which references were locked with $ before the fill. A practical tip: try it on a copy of the sheet or a handful of sample rows first and check the result before you apply it to the real data. That one habit catches almost every surprise while it is still cheap to reverse.

In Google Sheets

This is a command rather than a formula, so there is no syntax to carry over. Google Sheets arranges its commands in menus across the top instead of a ribbon, names some of them differently and does not have every Excel command — look for the equivalent in the Sheets menus or its help before assuming the steps match. The aim was to get you unstuck fast and leave you a little more capable than a copy-paste would. The answer is at the top and the detail above shows why it holds — so the next time a colleague asks, you can answer without reaching for search. Here is the takeaway for “repeating a formula in excel”: follow the steps at the top, check the result on the sheet itself, and keep undo in mind while you experiment. Done once deliberately, it becomes something you do from memory in Excel rather than something you search for again.

Common mistakes

• Leaving a shared value like a tax rate or a lookup table as a plain relative reference, so it silently shifts to a blank or wrong cell in every row after the first — the formula returns 0 or an error instead of flagging the mistake clearly.
• Double-clicking the fill handle expecting it to fill the whole intended range when the adjacent column has a gap — the fill stops at the first blank row instead of continuing to the true end of the data.
• Locking EVERY reference as absolute out of caution, which then stops row-specific values like quantity or price from updating per row at all.
• Copying and pasting a formula with Ctrl+C/Ctrl+V instead of filling, then being surprised formatting (borders, fill color) came along with it — Ctrl+D and the fill handle carry formatting too; use Fill ▸ Formulas Only or Paste Special ▸ Formulas to avoid that.

Frequently asked questions