Excel OFFSET Function: Dynamic Ranges and Examples

Formulas & functions · Updated November 22, 2026

In Excel, OFFSET is a function that returns a reference shifted a specified number of rows and columns from a starting cell or range. It can create a dynamic range whose size changes with your data; for example, use =SUM(OFFSET(A2,0,0,COUNTA(A2:A1000),1)) to total a contiguous list beginning in A2 as new entries are added.

What the OFFSET function does

OFFSET is useful when a formula, chart, or other Excel feature needs a reference that moves or changes size. It does not itself perform a calculation such as addition. Instead, it identifies a cell or range, which another formula can use. This distinction explains why OFFSET often appears inside SUM, AVERAGE, COUNT, or other functions.

The function is available in Microsoft Excel for Microsoft 365 on Windows, as well as current Mac and web versions. Its basic behavior and syntax are consistent across those versions. People often call it a dynamic offset function because its row, column, height, or width arguments can be calculated from worksheet data rather than typed as fixed numbers.

OFFSET is not the only way to build a changing range, and it is not automatically the best choice. It is particularly useful in older workbooks and in formulas that need a reference. For new work, Excel Tables or a nonvolatile alternative may be simpler and easier to maintain.

Syntax and arguments

The syntax is =OFFSET(reference,rows,cols,[height],[width]). The first three arguments are required. Height and width are optional; if omitted, the result has the same dimensions as the starting reference.

  • reference: The starting cell or range, such as A1 or A1:B3.
  • rows: How many rows to move from the starting reference. A positive number moves down; a negative number moves up.
  • cols: How many columns to move. A positive number moves right; a negative number moves left.
  • height: The number of rows in the returned reference. It must be positive.
  • width: The number of columns in the returned reference. It must be positive.

Rows and columns are relative to the reference, not absolute worksheet coordinates. If the requested reference would extend beyond the worksheet edge, Excel returns a #REF! error. The returned range can be one cell or multiple cells, depending on the starting reference and the optional dimensions.

A simple OFFSET example

Suppose cell A1 is the starting point and B3 contains 42. With that layout, enter =OFFSET(A1,2,1) in a worksheet cell. The function moves two rows down and one column right, so it refers to B3 and returns 42 when used as a single-cell formula. It has not added two to the value or changed A1; it has selected a different reference.

To see how the size arguments work, assume A2 contains 10, A3 contains 20, and A4 contains 30. With those values in place, =SUM(OFFSET(A2,0,0,3,1)) sums the three-cell range A2:A4 and returns 60. The zero row and column offsets keep the range anchored at A2; the height of 3 and width of 1 define its dimensions.

A negative offset can point upward or leftward, but it needs a valid starting point. For example, starting from B3 and moving one row up and one column left identifies A2. Check both the direction and the size of the range when adapting an example, especially near the first row or column.

Build a dynamic range that grows with a list

A common use is to make a range expand as new entries are added. Assume numbers are entered contiguously in A2:A4, cells A2:A4 contain 10, 20, and 30, and the remaining cells through A1000 are blank. In that layout, =SUM(OFFSET(A2,0,0,COUNTA(A2:A1000),1)) returns 60. COUNTA counts the three nonblank cells, and that count becomes OFFSET’s height, so SUM totals A2:A4.

When another number is typed in A5, COUNTA returns 4 and the calculated reference grows to A2:A5. The formula can therefore include new entries without manually changing its end row. This approach assumes the list is contiguous: a blank in the middle can make the counted height too short, leaving later entries out. It also assumes the cells in the counting area contain only the relevant list, not notes or other content.

For a growing range with a header in A1, the counting range should normally start below the header, as in the example. If the header is included in COUNTA, subtracting one can account for it, but this is easy to get wrong if the column contains other nonblank cells. Keep the data layout simple, and test the formula after adding and removing entries.

Use OFFSET with named ranges and charts

A dynamic reference can be useful for a chart that should include newly added rows. One approach is to create a defined name whose Refers to expression uses OFFSET, then use that name as the chart series range. In Excel for Microsoft 365 on Windows, open Formulas > Name Manager > New to define a name. The dialog and general workflow are similar in current Mac versions, though ribbon placement can vary slightly.

For a defined name called MonthlyValues, assume A1 contains a header and the contiguous values in A2:A4 are the only nonblank cells in column A. In the Name Manager Refers to box, use =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1). The count includes the header, so subtracting one gives a three-row range, A2:A4. If the source data is on a sheet with a different name, replace Sheet1 with that sheet’s actual name. This setup relies on a dedicated column and a single header; extra content can make the count incorrect.

Defined names can make chart references less cumbersome, but dynamic chart setup can vary with the chart type and workbook structure. If a chart does not expand, inspect the series reference and confirm the name is workbook-scoped or sheet-scoped as intended. In many everyday workbooks, converting the list to an Excel Table is simpler: charts and formulas that refer to a table column can adapt when table rows are added.

Volatile recalculation and performance

OFFSET is a volatile function. Excel recalculates volatile formulas whenever a recalculation occurs, even when a change does not affect the cells that the formula refers to. A handful of OFFSET formulas is usually manageable, but a large workbook with many volatile formulas can recalculate more often and feel slower.

That behavior matters most in large models, workbooks with extensive dependent formulas, or files that already recalculate slowly. If performance becomes a concern, look for repeated OFFSET formulas, especially formulas copied down thousands of rows. Avoid using a volatile function merely because it is familiar when a fixed range, an Excel Table, or a nonvolatile formula would meet the same need.

Volatile does not mean inaccurate or unsafe. It describes recalculation behavior, not a defect in the result. A formula can still return the intended reference, but workbook design should account for the extra recalculation work.

OFFSET compared with INDEX and Excel Tables

INDEX can often create a changing endpoint without OFFSET’s volatility. Suppose A1 contains a header, A2:A4 contain 10, 20, and 30, and there are no other nonblank cells in column A. With that layout, =SUM(A2:INDEX(A:A,COUNTA(A:A))) returns 60. COUNTA counts the header and three numbers, so INDEX points to A4 and the sum covers A2:A4. This method also depends on a contiguous list and a clean column.

For structured source data, a Table is often more readable. Convert the data to a table using Insert > Table, make sure the table has a header, and name its numeric column Sales. If the table is named Table1 and its Sales values are 10, 20, and 30, =SUM(Table1[Sales]) returns 60 and includes additional rows added to that table. Table references describe the data by column name, which can be easier for another person to understand than a nested reference formula.

Choose based on what the workbook needs. OFFSET is useful when a reference must shift by a calculated number of rows or columns, or when maintaining a workbook that already uses it. INDEX can provide a nonvolatile dynamic endpoint. Tables are usually convenient for expandable lists with headers. For spilled, formula-generated arrays, modern functions such as FILTER or TAKE may be more suitable, but they produce results differently and are not direct replacements for every reference-based use of OFFSET.

Common errors and how to fix them

A #REF! error usually means the requested reference extends beyond the worksheet. Recheck the starting cell and the signs and sizes of the row and column offsets. A #VALUE! error can arise when an argument is not an acceptable number, while a formula that returns an unexpected total often points to an incorrect height, width, or source range.

If a dynamic range stops growing, check for blanks inside the list and confirm that the COUNTA range includes all intended entries. COUNTA counts any nonempty cell, including text and formulas that display an empty string, so it may count cells that do not represent a data value. If values can be missing or the column contains other content, use a more deliberate data structure such as a Table rather than relying on a simple count.

When a formula appears to refer to the wrong place, remember that rows and columns are offsets from the starting reference. A row argument of 1 means one row below that starting point, not worksheet row 1. Also verify that the formula’s starting reference has not shifted unexpectedly when it was copied; use dollar signs to make a reference absolute where appropriate.

Frequently Asked Questions

What does OFFSET do in Excel?
OFFSET returns a reference a chosen number of rows and columns from a starting reference. Optional height and width arguments make that reference larger than one cell. Other functions, charts, or defined names can use the returned reference.

Can OFFSET create a dynamic range?
Yes. Its height or width can be calculated from worksheet data, allowing a reference to grow or shrink as a list changes. A simple COUNTA-based version works best when entries are contiguous and the counting area contains no unrelated data.

Why is OFFSET considered volatile?
Excel recalculates OFFSET formulas during recalculation even when a particular edit does not change their referenced cells. A few formulas are generally straightforward to manage, but many of them can add work in a large workbook.

Is OFFSET better than INDEX?
Neither is always better. OFFSET is convenient for shifting a reference in multiple directions or changing its dimensions, but it is volatile. INDEX can create a dynamic endpoint without that volatility, while Tables often offer a clearer option for growing lists.

Does OFFSET work in Excel for Mac and Excel for the web?
Yes, OFFSET is available in current Excel for Mac and Excel for the web, and its function syntax is the same. Ribbon locations and some interface details can differ by platform. In regional settings that use semicolons as formula separators, Excel may display separators differently from the comma-based examples here.

Related

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.