INDIRECT in Excel: How to Drag Formulas to New Cells

If you want an INDIRECT formula to follow different cells as you drag it, build its address from a relative cell reference or the current row or column; an address written as fixed text will not change. With cell addresses such as A2, A3, and A4 entered in B2:B4, put =INDIRECT(B2) in C2 and fill down: each row reads the cell named in its corresponding B cell.

Why INDIRECT may not change when you drag it

INDIRECT takes a text string that represents a cell or range reference and turns that text into a usable reference. That makes it useful when a reference is stored in a cell or assembled from pieces, but it also explains a common surprise: Excel cannot adjust address text in the same way it adjusts an ordinary cell reference.

For example, a conventional formula that refers directly to A1 normally changes its reference when copied one column to the right. If a formula instead contains the literal text A1 inside INDIRECT, that text remains A1 as the formula is copied. The formula can therefore keep returning the value in A1, even though it has been dragged several rows or columns away. The drag operation is working; the address is simply fixed inside the text.

INDIRECT can still respond to copying when the text comes from a cell reference. In the opening example, copying the formula from C2 to C3 changes its ordinary reference from B2 to B3. INDIRECT then reads the text in B3 and uses that address. The key distinction is between a reference Excel can adjust and an address that exists only as fixed characters.

Make a reference follow the row

When the intended pattern is one source row per formula row, use the ROW function to build the address from the formula’s position. With the values to retrieve in A1:A5 and the formula starting in B1, enter =INDIRECT("A"&ROW()) in B1 and fill down. In B1, ROW returns 1, so the formula reads A1; in B2 it reads A2, and in B5 it reads A5.

This approach is useful when the cells you want to read are in a known column and the formula starts on the same row as the first source value. If your formula starts in row 2 but the first source value is in row 1, the row numbers need an offset. With source values in A1:A5 and the first formula in B2, use =INDIRECT("A"&ROW()-1) in B2 and fill down. B2 then reads A1, B3 reads A2, and B6 reads A5. Check the starting row and the intended source row before filling a long range.

ROW can also be used when the reference is not in column A. With source values in D1:D5 and formulas beginning in E1, enter =INDIRECT("D"&ROW()) in E1 and fill down. The text combines the fixed column letter D with the changing row number. This is simple for a single column; it is less convenient if the column itself must change too.

Make a reference follow the column

If formulas run across a row and each one should read the cell immediately to its left, build the address from both the current row and column. With a value in A1 and the formula placed in B1, enter =INDIRECT(ADDRESS(ROW(),COLUMN()-1)) in B1 and fill right. In B1, COLUMN()-1 points to column A; after filling into C1, it points to column B. The row remains 1 as the formula moves across.

ADDRESS returns a reference written as text, and INDIRECT interprets that text. The expression uses the formula’s current row and the column one position to its left, so it can be filled across multiple rows as well. For example, if you place the same pattern in B2, it reads A2; filling it right reads the cell immediately to the left on row 2. This pattern is handy for rolling calculations or templates where each result depends on the preceding cell.

Before using this pattern across a large area, verify which direction the formula is meant to follow. “One cell to the left” is different from “always read column A.” For the latter, a row-based address is usually clearer. A formula that moves with the current column can quietly return valid but unintended values if it is started in the wrong place.

Use a cell that contains the address

Often the clearest setup is to keep the address text in a separate cell and let INDIRECT read it. With text references A2, A3, and A4 in B2:B4, enter =INDIRECT(B2) in C2. C2 returns the value from A2; filling down makes C3 read B3 and C4 read B4. This keeps the address list visible and easy to edit, which can be safer than hiding the address inside a complicated expression.

Whether the address-input reference changes during filling depends on its cell-reference style. In this example, B2 is relative, so it becomes B3 when the formula is filled down. If you instead want every formula to use the same address text, make that input reference absolute. With the address text A2 in B1 and the formula placed in C1, =INDIRECT($B$1) keeps reading B1 when filled in any direction.

Dollar signs control the ordinary reference to the cell holding the text; they do not alter the contents of that text. If B1 contains A2, an absolute reference to B1 keeps using the text A2. To make the target address itself change, change the text or construct it from row or column information. This distinction is a frequent source of confusion when a formula contains both INDIRECT and dollar signs.

Refer to a worksheet by name

INDIRECT can also assemble a reference to another worksheet. With worksheet names listed in A2:A4, the target value in cell B2 on each named sheet, and the formula in C2, enter =INDIRECT("'"&A2&"'!B2") and fill down. If A2 contains North Region, the assembled reference points to cell B2 on the North Region sheet. The single quotation marks around the sheet name allow names containing spaces to be interpreted correctly.

This example changes the worksheet name as the formula moves down, because A2 is a relative reference. The target cell remains B2 on each sheet because those characters are fixed in the formula. If you want the target row to change as well, build that part from ROW or store complete references in the input cells. Whatever pattern you choose, make sure each worksheet name exists and that the referenced cell is valid on every sheet.

INDIRECT is not always the best choice for consolidating many worksheets. A mistyped sheet name can produce an error, and the references are less visible to Excel’s dependency tools than direct references. If the worksheets have the same layout and the task is recurring, Power Query, a summary sheet with explicit references, or a purpose-built lookup may be easier to maintain.

Fill the formula down or across

After entering a formula in the first cell, use the fill handle—the small square at the lower-right corner of the selected cell—to drag it down or across. For a controlled fill, select the formula cell and the destination range, then use Home > Fill > Down or Home > Fill > Right. On Windows, Ctrl+D fills down and Ctrl+R fills right. On Mac, the Home > Fill commands are a reliable alternative if the keyboard shortcut differs because of the Excel version or keyboard settings.

Dragging copies the formula pattern, not its displayed result. Excel adjusts ordinary relative references as the formula moves, while absolute references marked with dollar signs stay fixed. INDIRECT then evaluates the reference text in the copied formula. To check what is happening, select a filled cell and inspect the formula bar: confirm that the address-input reference moved as intended, and that any text used to form the target address changes in the way you expected.

Use fill down when each formula should use the next row’s address or data.
Use fill right when each formula should use the next column’s address or data.
Use a fixed input reference when every copied formula should read the same cell containing the address text.
Test the first two or three results before filling a large range, especially when formulas refer to other sheets.

Double-clicking the fill handle can fill a formula down alongside neighboring data, but the extent depends on the adjacent column’s filled cells. It is convenient for a continuous list, yet it may stop early or extend farther than intended when there are blanks or separate data blocks. Dragging or selecting an explicit destination range gives more control.

Common errors and performance considerations

A #REF! error generally means the text does not identify a valid reference. Check for a misspelled worksheet name, a missing exclamation mark between the sheet name and cell address, or a row or column number outside the worksheet. If the address text is assembled from cells, inspect the final text for extra spaces or missing characters. A blank input cell can also produce an unusable reference rather than the result you want, so decide how blank inputs should be handled.

INDIRECT is a volatile function: Excel recalculates it whenever calculation occurs, even if the referenced cell has not changed. A few INDIRECT formulas are usually manageable, but a workbook with thousands of them can recalculate more often than a workbook using direct references. If performance becomes an issue, consider replacing formulas with direct links, INDEX-based references, or a lookup approach that fits the task.

INDIRECT also cannot retrieve a reference from a closed external workbook. A formula that depends on an external workbook through INDIRECT can return #REF! when that source workbook is closed. If you need dependable links to closed workbooks, use a different design and confirm that it supports the way the workbook will be opened and refreshed.

For references beyond the worksheet grid, remember that the resulting address must still be valid in the workbook. Also consider maintainability: a reference assembled from several fragments may be harder for another person to audit than a direct reference. Keep a clear address-input column or add a brief label explaining the formula pattern when the workbook will be shared.

When to choose another approach

Choose INDIRECT when the reference itself needs to be selected from text—for example, when a user chooses a worksheet name from a list—or when a formula must construct a reference dynamically. It is not necessary merely to copy a normal formula down. In ordinary calculations, direct references are easier to read and usually easier to maintain because Excel can adjust them naturally.

For a lookup based on a key such as a product ID, a lookup function is generally more appropriate than building a cell address from text. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, while older Excel versions may require a different lookup method. For a known range where you need a value at a specified row and column, INDEX can be a suitable alternative. The right choice depends on whether the task is truly about changing the reference or about finding a matching value.

As a practical rule, first write down the pattern in plain language: “read the cell named in this row,” “read the cell to the left,” or “read the same address on the worksheet named here.” Then choose the simplest formula that expresses that pattern. This prevents INDIRECT from becoming an unnecessary layer in a formula that could use a normal relative reference.

Frequently Asked Questions

Why does my INDIRECT formula keep referring to the same cell when I drag it?
If the target address is typed as fixed text inside the formula, copying does not change that text. Use a cell containing the address, or build the row or column portion dynamically, so the target can change as the formula moves.

Does INDIRECT adjust relative references automatically?
It does not adjust an address that is fixed text. However, a normal reference to a cell containing address text can adjust when copied. For example, a relative input-cell reference can move from one row to the next, causing INDIRECT to read a different address string.

How do I keep INDIRECT pointing to the same address cell?
Make the reference to the input cell absolute with dollar signs. This keeps the formula connected to that input cell when you copy it. The text stored in the input cell remains separate: changing that text changes the target address.

Can INDIRECT refer to a worksheet whose name contains spaces?
Yes. Put single quotation marks around the worksheet name in the assembled reference text, followed by an exclamation mark and the cell address. Check that the name matches the worksheet tab exactly.

Can INDIRECT link to a closed workbook?
No. INDIRECT requires the external workbook it refers to to be open. If the source workbook is closed, the reference can return #REF!, so use another linking or data-import method if closed-workbook access is required.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.