How to Use the OFFSET Function in Excel (and Google Sheets)
1. Understanding the OFFSET Syntax
The OFFSET function returns a reference that is a given number of rows and columns away from a starting cell (or range). Because it returns a reference, you can nest it inside any function that accepts a range—such as SUM, AVERAGE, COUNT, INDEX, or a chart series.
Argument list
| Argument | Required? | Description | Example |
|---|---|---|---|
| reference | Yes | The starting cell or range. | A1 |
| rows | Yes | How many rows to move down (positive) or up (negative) from reference. | 2 |
| cols | Yes | How many columns to move right (positive) or left (negative) from reference. | ‑1 |
| height | No | Height, in rows, of the returned range. If omitted, Excel uses the height of reference. | 3 |
| width | No | Width, in columns, of the returned range. If omitted, Excel uses the width of reference. | 2 |
Syntax
OFFSET(reference, rows, cols, [height], [width])
- reference can be a single cell (
A1) or a multi‑cell block (A1:C3). - rows and cols are numeric values; they may be the result of other formulas.
- height and width are optional but essential when you need a dynamic range size.
Simple example
| A | B | C |
|---|---|---|
| 10 | 20 | 30 |
| 40 | 50 | 60 |
| 70 | 80 | 90 |
=OFFSET(A1, 1, 1)
Result: 50 (the value in cell B2).
Using OFFSET to create a dynamic range
If you want the sum of the three cells directly below A1 (i.e., A2:A4), combine OFFSET with SUM:
=SUM(OFFSET(A1, 1, 0, 3, 1))
OFFSET returns the range A2:A4; SUM then adds those three numbers.
2. Creating Dynamic Ranges with OFFSET
The real power of OFFSET is its ability to grow or shrink automatically as data changes. By anchoring the function to a single cell and shifting a variable number of rows/columns, you can feed a moving range into charts, data‑validation lists, or other formulas.
Example: A growing sales list
| A |
|---|
| 120 |
| 135 |
| 150 |
| 142 |
| 160 |
The last filled row is found with COUNTA(A:A). Combine that with OFFSET:
=OFFSET(A1, 0, 0, COUNTA(A:A), 1)
- reference:
A1(first sales cell) - rows / cols:
0(stay at A1) - height:
COUNTA(A:A)counts non‑blank rows (5 now) - width:
1
Result: the range A1:A5. Add a new value in A6; the same formula now returns A1:A6 automatically.
Using the dynamic range in a chart
- Define a name (Formulas ► Name Manager) called SalesData with the formula above.
- In a chart, set the series Y‑values to
=Sheet1!SalesData. - As you append rows, the chart expands without manual updates.
Common pitfalls
| Pitfall | How to avoid it |
|---|---|
Volatile function – OFFSET recalculates on every workbook change, which can slow large sheets. |
Use non‑volatile alternatives (e.g., INDEX) when a static reference is sufficient. |
| Omitting height/width – the function then returns a single cell, even if you expected a range. | Explicitly specify height and width, or use ROWS/COLUMNS to make them dynamic. |
Negative height/width – not allowed; produces #VALUE!. |
Keep those arguments positive; move the reference instead of the size. |
Reference goes off‑sheet – results in #REF!. |
Clamp the offset with MIN/MAX or verify the maximum row/column numbers. |
| Mixed absolute/relative references – copying the formula may change the base cell unintentionally. | Lock the base reference with $ (e.g., $A$1). |
Google Sheets quirks – OFFSET defaults height and width to 1, which can surprise users. |
Always include height and width in Sheets, just as in Excel. |
3. Combining OFFSET with Other Functions
Nesting OFFSET inside other formulas lets you perform calculations on a moving range.
| Combined function | What it does | Sample formula (Excel 365 / Google Sheets) | Sample data | Result |
|---|---|---|---|---|
SUM(OFFSET…) |
Adds a block of numbers | =SUM(OFFSET(A2,0,0,3,1)) |
10, 20, 30, 40, 50 | 60 (10 + 20 + 30) |
AVERAGE(OFFSET…) |
Averages a dynamic range | =AVERAGE(OFFSET(B1,1,0,5,1)) |
5, 15, 25, 35, 45, 55 | 35 |
COUNTIF(OFFSET…,criteria) |
Counts cells that meet a condition in a shifting window | =COUNTIF(OFFSET(C2,0,0,4,1),">=20") |
10, 20, 30, 40, 50 | 3 |
MAX(OFFSET…) |
Returns the largest value in a moving range | =MAX(OFFSET(D1,2,0,3,1)) |
7, 12, 9, 18, 5, 22 | 22 |
INDEX(OFFSET…,row_num) |
Retrieves a specific item from a dynamic block | =INDEX(OFFSET(E1,0,0,5,1),4) |
3, 6, 9, 12, 15 | 12 |
CHOOSE({1,2}, OFFSET…) |
Supplies two separate dynamic ranges to a chart series | =CHOOSE({1,2}, OFFSET(F2,0,0,5,1), OFFSET(G2,0,0,5,1)) |
F: 1‑5, G: 10‑50 | — (returns two arrays) |
4. Practical Examples and Use Cases
| # | Situation | Sample data | Formula | Result |
|---|---|---|---|---|
| 1 | Running total that expands automatically | A | B --- |
--- 1 |
| 2 | Pull the value n rows below a header | A | B --- |
--- Item |
| 3 | Moving 3‑month average | A | B --- |
--- Jan |
Tip: If you insert rows above the reference cell, the offset will shift. Anchor the reference with absolute addressing ($) or place the formula at the bottom of the sheet.
5. Common Mistakes and How to Avoid Them
| Mistake | Why it happens | Fix |
|---|---|---|
| Using a volatile function without need | OFFSET recalculates on every change, slowing large workbooks. |
Replace with non‑volatile alternatives like INDEX. Example: =INDEX(A:A,3) instead of =OFFSET(A1,2,0). |
| Leaving out height/width | Returns a single cell even when a range is expected. | Always specify height and width, or use ROWS/COLUMNS to calculate them. |
| Negative height or width | Excel returns #VALUE!. |
Keep those arguments positive; adjust the rows/cols offset instead. |
| Offsetting beyond the sheet limits | Produces #REF!. |
Keep rows/cols offsets inside the sheet. |
| Unintended mixed references | Copying the formula changes the base reference. | Lock the base cell with $ (e.g., $A$1). |
| Google Sheets default height/width | Sheets assumes 1 when omitted, which can surprise Excel users. | Explicitly include both arguments in Sheets. |
| Complex array formulas in pre‑365 Excel | 255‑character limit can be exceeded. | Break the logic into helper columns or use modern dynamic‑array functions (SEQUENCE, FILTER). |
6. FAQ
What is the syntax of the OFFSET function in Excel?
OFFSET(reference, rows, cols, [height], [width]) returns a range that starts rows down and cols across from reference. If height and width are omitted, the returned range has the same size as reference.
How can I use OFFSET to create a dynamic chart range?
Define a named range with OFFSET, for example =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1). Then set the chart series’ Y‑values (or X‑values) to that name. As you add data, COUNTA updates, and the chart expands automatically.
Can I combine OFFSET with INDEX for more powerful lookups?
Yes. INDEX can return a single value from the range that OFFSET creates, allowing you to look up a value a variable number of rows away. Example: =INDEX(OFFSET($A$1,0,0,10,1), MATCH(D1,$A$1:$A$10,0)).
What are the performance implications of using OFFSET in large workbooks?
OFFSET is volatile, meaning it recalculates whenever any cell changes. In large workbooks this can cause noticeable slow‑downs. Where possible, replace it with non‑volatile functions like INDEX or newer dynamic‑array formulas (FILTER, SEQUENCE).
Is there an alternative to OFFSET for newer Excel versions?
Yes. Excel 365/2021 provides the INDEX/SEQUENCE/FILTER family, which can create dynamic ranges without volatility. For example, =INDEX(A:A, SEQUENCE(COUNTA(A:A))) returns the same expanding range as OFFSET(A1,0,0,COUNTA(A:A),1) but is faster and non‑volatile.
All formulas shown work in both Excel (365/2021 and later) and Google Sheets, unless noted otherwise.