Yes—Excel’s OFFSET function can return a cell or range relative to a starting reference: specify the reference, how many rows and columns to move, and, when needed, the output range’s height and width. OFFSET returns a reference; it does not move cells or copy data, so the formula you put around it determines the output you see.
What OFFSET means in Excel
OFFSET is useful when the location of an input or result changes according to a row or column setting. It starts from a reference cell, shifts by the number of rows and columns you specify, and can describe a larger range by adding its height and width. A zero row shift or column shift means stay in that direction; a negative shift moves up or left, provided the resulting reference remains inside the worksheet.
For example, a formula can point to the cell two rows below and one column to the right of a starting cell. You can also wrap OFFSET in a function such as SUM to calculate from the range it identifies. The distinction matters: OFFSET itself identifies a location, while SUM, AVERAGE, or another function calculates a result from that location.
OFFSET is available in current desktop Excel for Microsoft 365 on Windows, as well as other modern Excel editions. Its argument names are reference, rows, columns, height, and width. Height and width are optional; when omitted, the returned reference has the same dimensions as the starting reference. With a single starting cell, omitting them gives a single-cell reference.
How input and output references work
Imagine that A1 is the starting point, B2 contains a row offset, and B3 contains a column offset. If A1 is the reference, a value of 2 in B2 moves two rows down and a value of 1 in B3 moves one column right. The selected output location is C3. This is handy when a user chooses a position using input cells rather than editing the formula each time.
With A1 as the starting reference, B2 set to 2, B3 set to 1, and the value 72 in C3, enter this formula in an output cell:
=OFFSET(A1,B2,B3)
The result is 72. The formula does not show the text of the address C3; Excel evaluates the reference and returns the value at that location. If C3 is blank, the output will appear blank or as zero depending on how the reference is used. If you want the result to update, change B2 or B3; if you want a fixed value, copy the result and use Paste Special > Values.
Return a shifted range, not just one cell
To use OFFSET for a range, specify its height and width after the row and column shifts. For example, assume A1 is the anchor cell and B2:C4 contains the six numbers 2, 3, 4, 5, 6, and 7. In a separate output cell, enter:
=SUM(OFFSET(A1,1,1,3,2))
Starting at A1, the formula moves one row down and one column right to B2. The requested range is three rows high and two columns wide, so it covers B2:C4. SUM returns 27, because 2 + 3 + 4 + 5 + 6 + 7 = 27. A common mistake is to confuse the shift with the range size: the first two numbers after A1 position the range; 3 and 2 set its dimensions.
Check that the height and width match what the receiving calculation expects. If a chart, total, or lookup is meant to use four months across one row, for instance, a one-row-by-four-column range is different from a four-row-by-one-column range. Both contain four cells, but their orientation affects downstream formulas and charts.
Create a dynamic input range
OFFSET can build a range that grows as new entries are added. Suppose A1 contains the heading Amount, A2:A4 contain 25, 40, and 50, and there are no other filled cells in column A. Put this formula in a separate total cell:
=SUM(OFFSET(A1,1,0,COUNTA(A:A)-1,1))
COUNTA counts four filled cells in column A, including the heading. Subtracting one gives a range height of three; OFFSET starts one row below A1 and returns A2:A4. SUM therefore returns 115. Add another amount directly below the existing entries and the range expands to include it.
This approach depends on the data being contiguous and column A containing no unrelated values. A blank in the middle can make the count too large for the actual uninterrupted list, while an unrelated note farther down the column can also inflate the range. COUNTA also counts cells containing formulas that display an empty string. For regularly maintained lists, an Excel Table is usually more dependable: select the data and choose Insert > Table, then use its named column in a total. Tables expand as rows are added and make the input range easier to understand.
Common OFFSET errors and how to fix them
- #REF! means the requested reference goes beyond the worksheet’s edge, or the reference is otherwise invalid. Check the starting cell and the direction and size of the shift. For instance, moving upward from row 1 cannot produce a valid cell.
- #VALUE! can occur when a row, column, height, or width argument is not a usable number. Check for text in the cells supplying offset controls, and confirm that the calculations producing the dimensions return numeric values.
- The wrong cell or range appears. Confirm that rows and columns are counted from the anchor, not from the formula cell. Remember that positive values move down and right, while zero leaves that direction unchanged.
- The total includes too much or too little. Inspect the selected range and its height and width. With a COUNTA-based range, look for gaps, extra entries in the column, or formulas that return empty text.
- The formula changes unexpectedly. OFFSET is volatile, which means Excel recalculates it when recalculation occurs even if its direct inputs have not changed. A workbook with many volatile formulas can recalculate more often than necessary.
If the formula’s inputs are on another sheet, include the sheet name in the reference, such as a reference to a cell on a sheet named Inputs. If the sheet name contains spaces, Excel encloses it in single quotation marks. You can also select the referenced cells while building a formula rather than typing an address, which reduces mistakes when the source is on another worksheet.
OFFSET compared with INDEX and Excel Tables
OFFSET is convenient when the output needs to move in more than one direction or when a range’s size is calculated from inputs. Its main trade-off is volatility. INDEX can often describe a changing endpoint without making the reference function volatile. For the Amount list in A1:A4 described above, where A1 is the heading and A2:A4 are 25, 40, and 50, this alternative returns the same total:
=SUM(A2:INDEX(A:A,COUNTA(A:A)))
COUNTA returns 4, so INDEX identifies A4 as the final cell and SUM adds A2:A4. The result is 115. This alternative still assumes a contiguous list and no unrelated filled cells in column A, but it avoids OFFSET. For a clean expanding data set, a Table with a named column is often clearer still.
Use XLOOKUP when the actual task is to find a value by matching a key, rather than shifting from a starting cell. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. Use INDEX or a Table when you need a changing range but want a less volatile or more readable setup. Keep OFFSET when the offset itself is meaningful—for example, a user-selected row and column—or when it makes the formula materially easier to follow.
Check the workbook before relying on the result
Before using an OFFSET result in a report, chart, or operational workbook, test the boundaries as well as the normal case. Try the smallest and largest allowed row and column inputs, confirm that the returned range has the intended shape, and verify that adding a new input updates the output correctly. Label the control cells so another person can tell whether a value means rows, columns, or range size.
For easier auditing, avoid embedding long chains of calculations inside OFFSET’s arguments when a helper cell would make the row count or column shift clearer. Keep source data separate from the output formula, and do not put the formula inside the very range it is designed to total; that can create a circular reference. In Excel for Microsoft 365 on Windows, the formula bar is useful for inspecting a long formula, while selecting a reference within a formula can help identify which part of the sheet it points to.
Excel for Mac has the OFFSET function as well, and the formulas above use the same syntax. Ribbon placement and keyboard shortcuts can differ between Windows and Mac, but the core troubleshooting is the same: verify the anchor, offsets, dimensions, and source data. In Excel for the web, formulas are supported too, although some workbook-management and inspection features may be arranged differently from the desktop app.
Frequently Asked Questions
Why does OFFSET return zero instead of the value I expect?
Check whether the selected cell is blank and whether the function is being used inside another calculation. A reference to an empty cell can be treated as zero in a calculation. Also verify the anchor and offsets; a one-row or one-column error can point to a different, empty cell.
Does OFFSET move or copy the source cells?
No. It returns a reference to a location or range; it does not rearrange, copy, or overwrite worksheet data. Use cut-and-paste or a dedicated editing operation if you actually need to move cells.
Can OFFSET move left or up?
Yes. Negative row or column offsets move up or left from the starting reference, as long as the resulting reference stays within the worksheet. A shift beyond the sheet boundary produces an invalid reference.
Why is my OFFSET formula making Excel slow?
OFFSET is volatile and recalculates during recalculation events. If a workbook uses it many times, consider whether INDEX, a Table, or a direct reference can do the same job with less recalculation. The best replacement depends on how the source range changes.
Can I use OFFSET to create a changing chart range?
Yes, it can define a range that changes with data or control inputs, but the setup must match the chart’s expected orientation and size. For many everyday charts, converting the source to an Excel Table is simpler because the chart can expand as table rows are added.
Related
- How to Find and Clear a Circular Reference in Excel
- How to Enter an Absolute Cell Reference in Excel
- How to Use Absolute Cell References in Excel on Dell
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Not Working Correctly? Causes and Reliable FixesIf Excel is not working correctly, first save a copy of the workbook, note exactly what is failing,…
- Excel Stale Value Error: What It Means and How to Fix ItExcel’s “stale value” is usually not a standard formula error such as #VALUE!; it means a displayed result…
- Clean Excel Formatting: Remove Clutter and Fix StylesTo remove formatting in Excel for Microsoft 365 on Windows, select the cells, then choose Home > Clear…
- Remove Spill Error in Excel: Causes and FixesTo remove a #SPILL! error in Excel, select the error cell, inspect the outlined spill range, and clear…
- Excel VLOOKUP Value Not Available Error?In Excel, the VLOOKUP “value not available” error is usually #N/A: Excel cannot find the lookup value in…
- Excel Print Area Won’t Clear? Fix Stuck Print SettingsIf an Excel print area will not clear, select the affected worksheet and choose Page Layout > Print…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.