Free practice workbook
Get the OFFSET Workbook
The exact file from the tutorial, ready to use.
Enter your email for instant access.
Free. Instant access. No setup needed.
The OFFSET function in Excel lets you find a cell or range of cells by moving a certain number of rows and columns from a starting cell. This is very useful for creating dynamic ranges and calculations that can change automatically as your data changes.
Think of OFFSET Excel as a GPS:
You give it a starting point and then you tell it:
- how many rows to go down
- how many columns to move across
- and what range you want returned
Like this:
Is OFFSET still useful in Excel 2026?
OFFSET is not deprecated. Microsoft still lists it as supported in Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016.
Whether you should use it depends on what you’re trying to do.
Use OFFSET when you need:
- A dynamic chart source range that grows when new data is added
- Scenario analysis where new scenarios get appended to the right of an existing block
- Backward compatibility with files opened in Excel 2016 or 2019
- A range whose height or width is calculated by another formula at runtime
Skip OFFSET and use a modern alternative when:
- You’re creating a dynamic named range for general use (Excel Tables do this without volatility)
- You need the last N rows of a column (TAKE is one argument)
- You want to filter data dynamically (FILTER replaces complex OFFSET combinations)
- Performance matters and your file is large (OFFSET recalculates every time anything changes anywhere in the workbook)
The rest of this guide shows how OFFSET works, then gives you the modern swap for each pattern.
OFFSET function syntax
=OFFSET(reference, rows, cols, [height], [width])- reference: The starting point (a cell or range of cells).
- rows: The number of rows to move from the starting point.
- cols: The number of columns to move from the starting point.
- height: (Optional) The number of rows to return.
- width: (Optional) The number of columns to return.
How does OFFSET work? A simple example
Let’s say we have data in cells A1 to A5. We want to get the value in cell A3 using the Excel OFFSET function.
=OFFSET(A1, 2, 0)Explanation:
- A1 is the starting point.
- The formula moves down 2 rows from A1 (to A3).
- It doesn’t move any columns to the left or right (0 columns).
So, the formula returns the value in cell A3, which is 30.
How to create a dynamic range with OFFSET
Dynamic ranges adjust automatically as you add or remove data. Here’s how to create a dynamic range for calculating the average of the last six months of sales data.
Imagine you have a table with months in column A and sales revenue in column B.
To get the average of the last six months, follow these steps:
- Starting Point: Use cell B3 as your reference (header of the Sales column).
- Rows to Move: Use the COUNTA function to count the number of filled cells below B3.
- Height: Use -6 to move backward and select the last six cells.
Formula:
=AVERAGE(OFFSET(B3, COUNTA(B4:B15), 0, -6, 1))Explanation:
- B3: Starting point.
- COUNTA(B4): Counts filled cells below B3.
- 0: Stays in the same column.
- -6: Selects the last six cells.
- 1: Width is 1 column.
This formula calculates the average of the last six months dynamically. If new data is added, the formula automatically updates to include the latest six months.
How to use OFFSET with horizontal data
Let’s look at an example where data is arranged horizontally. This time, we will use the OFFSET function to calculate the average of the last six months’ sales.
Imagine you have sales data for each month from January to July in cells B17 to H17.
Step-by-Step Instructions
- Starting Point: Use cell A17 as your reference point.
- No Row Movement: Set the row parameter to 0 to stay on the same row.
- Count Columns: Use the COUNT function to count the number of filled cells from columns B to O.
- Height: Set to 1 since we are referencing only one row.
- Width: Set to -6 to select the last six columns.
Formula
=AVERAGE(OFFSET(A17, 0, COUNT(B17:O17), 1, -6))Explanation:
- A17: Starting point.
- 0: No row movement.
- COUNT(B17): Counts the number of filled cells from B17 to O17.
- 1: Height is 1 row.
- -6: Width is -6 columns (moving backward to get the last six months).
This formula calculates the average of the last six months dynamically. If you add data for August, the formula automatically updates to include August and calculate the new average.
How to combine OFFSET with MATCH for moving averages
The OFFSET function can be combined with other functions to create dynamic moving average calculations. This allows you to select a month and automatically get the average of the next three months.
Example: Dynamic Moving Average Calculation
Imagine you have monthly sales data in column A. You want to calculate the average sales for the three months following the selected month in the drop down in cell E25.
Step-by-Step Instructions:
- Starting Point: Use cell B25 as your reference point (header of the Sales column).
- Finding the Selected Month: Use the MATCH function to find the row number of the selected month from the drop-down list.
MATCH Function:
=MATCH(E25, A26:A51, 0)- E25: The cell with the selected month.
- A26: The range of months in your data.
- 0: Exact match.
For example, if the selected month is April 2024, and it is the fourth item in the list, MATCH returns 4.
- Using OFFSET with MATCH: Combine OFFSET and MATCH to move to the selected month and average the next three months.
Formula
=AVERAGE(OFFSET(B25, MATCH(E25, A26:A51, 0), 0, 3, 1))
Explanation
- B25: Starting point.
- MATCH(E25, A26, 0): Finds the row of the selected month.
- 0: No column movement.
- 3: Height is 3 rows (next three months).
- 1: Width is 1 column.
This formula calculates the average of the selected month and the next two months.
If you want to exclude the selected month from the average, add 1 to the MATCH result:
=AVERAGE(OFFSET(B25, MATCH(E25, A26:A51, 0) + 1, 0, 3, 1))OFFSET vs Excel Tables vs INDEX vs FILTER (which to use when)
| Tool | Volatile? | Best For | When to Skip |
|---|---|---|---|
| OFFSET | Yes | Dynamic chart sources, scenario analysis, ranges sized by another formula | Large files where performance matters |
| Excel Tables | No | Auto-expanding ranges for formulas, charts, and PivotTables | Scenarios where the range needs to extend rightward, not just downward |
| INDEX | No | Non-volatile dynamic named ranges, single-cell lookups | When you need a range that can grow in both directions at once |
| FILTER | No | Pulling subsets of data based on criteria, dynamic dashboards | Excel 2019 and older versions (FILTER requires Microsoft 365 or Excel 2021+) |
For most use cases that today’s tutorials show with OFFSET, an Excel Table is the simpler choice. Convert your data range to a Table with Ctrl+T and any formula that references the Table automatically adjusts when rows are added or removed.
The cases where OFFSET still wins are narrower than they were in 2017: scenario analysis with appendable columns, charts where the range size is driven by a formula in another cell, and any environment where dynamic arrays aren’t available.
Modern alternatives to OFFSET in 2026
Each of these patterns shows the OFFSET version and the modern replacement side by side.
OFFSET + COUNTA replaced by Excel Tables
Old way (volatile, breaks when data has gaps):
=OFFSET(B3, 0, 0, COUNTA(B:B)-1, 1)New way: Convert the data range to a Table (Ctrl+T). Reference the Table column by name:
=Table1[Sales]The Table reference auto-expands when new rows are added. No volatility, no formula needed.
OFFSET for last N rows replaced by TAKE
Old way:
=AVERAGE(OFFSET(B3, COUNTA(B4:B10), 0, -6, 1))New way (Excel for Microsoft 365 and Excel 2024):
=AVERAGE(TAKE(B4:B10, -6))
TAKE returns the last 6 rows directly. The negative number means “from the end.” No COUNTA, no negative height argument, no volatility.
See our full guide on the TAKE function for more patterns, and TRIMRANGE for cleaning up spilled ranges with trailing blanks.
OFFSET + dynamic range replaced by INDEX
Old way (volatile):
=SUM(OFFSET($B$4, 0, 0, COUNTA($B$4:$B$1000), 1))New way (non-volatile):
=SUM($B$4:INDEX($B$4:$B$1000, COUNTA($B$4:$B$1000)))
COUNTA counts the filled cells in the data range. INDEX returns the last one. The colon between $B$4 and INDEX builds the range from the first row to the last. Same result as OFFSET, no recalculation overhead.
For more on INDEX and how it pairs with MATCH for non-volatile lookups, see our INDEX and MATCH guide.
OFFSET for filtered data replaced by FILTER
For pulling rows that match criteria, FILTER replaces complex OFFSET and MATCH combinations entirely:
=FILTER(B4:B15, A4:A15 = "April")Returns all April rows in one formula. See our guide on the FILTER function with multiple criteria for the full breakdown.
Practical Applications of Excel OFFSET
- Tracking Inventory: Use OFFSET to create a dynamic range that updates as you add or remove items in your inventory list.
- Managing Budgets: Create dynamic financial models where income and expense data can be updated regularly without altering core formulas.
- Analyzing Trends: Combine OFFSET with functions like AVERAGE or SUM to analyze data trends over time dynamically.
What are the limitations of the OFFSET function?
OFFSET is volatile.
OFFSET recalculates every time any cell in the workbook changes, even cells unrelated to the OFFSET formula. In a workbook with a few OFFSET formulas this is invisible. In a workbook with hundreds of them, Excel can become noticeably slow.
OFFSET breaks dynamic arrays.
If an OFFSET formula sits inside a spilled array context, it can return #SPILL! errors or unexpected results. For data that needs to feed into FILTER, SORT, or UNIQUE, use a non-volatile alternative.
OFFSET is hard to audit.
Because the formula returns a reference rather than a value, there’s no visual cue showing what range it actually points to. Large nested OFFSET formulas are difficult to debug. Excel Tables and named INDEX ranges are easier to maintain.
Common errors to watch for:
- #REF! error: OFFSET is pointing to a range outside the worksheet bounds. Check your row and column arguments.
- #VALUE! error: The reference argument is not a valid cell or contiguous range.
- Unexpected blank results: COUNTA or COUNT inside OFFSET is counting empty cells you didn’t expect, often because of formulas that return “”.
Frequently asked questions
Is the OFFSET function deprecated in Excel 2026?
No. OFFSET is fully supported in Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016. Microsoft has not announced any plans to deprecate it. It is, however, considered a legacy approach for many use cases that modern functions handle better.
What is the difference between OFFSET and INDEX in Excel?
OFFSET is volatile, meaning it recalculates every time the workbook changes. INDEX is not volatile, so it only recalculates when its inputs change. Both can return a reference to a range, and both can be used to create dynamic ranges. For performance, INDEX is usually the better choice. OFFSET still wins when the range size needs to be calculated dynamically by another formula.
Why does my OFFSET formula slow down Excel?
OFFSET is a volatile function. Every time anything in the workbook changes, every OFFSET formula recalculates, even if its inputs didn’t change. In large workbooks with many OFFSET formulas, this adds up. Replace with Excel Tables for general dynamic ranges, or INDEX for non-volatile named ranges.
What is the modern way to create a dynamic range in Excel?
Convert your data to an Excel Table with Ctrl+T. Tables automatically expand when new rows are added, and any formula or chart that references the Table updates without recalculation overhead. For more advanced needs, use INDEX:INDEX for non-volatile named ranges, or FILTER for criteria-based subsets.
Can I use OFFSET with dynamic arrays and spilled ranges?
OFFSET works alongside dynamic arrays but should not be used as the source for SORT, FILTER, or UNIQUE. The volatile nature of OFFSET can cause #SPILL! errors and inconsistent recalculation. For dynamic array workflows, use Excel Tables, INDEX, or TAKE as the source instead.
Does OFFSET work in Excel for the web?
Yes. OFFSET works in Excel for the web, Excel for Microsoft 365 (Windows and Mac), Excel 2024, Excel 2021, Excel 2019, and Excel 2016. Behavior is consistent across versions.
Download the Workbook
Download the same OFFSET workbook used in this tutorial. Includes the dynamic range examples, horizontal data layout, and the OFFSET plus MATCH moving average formula.
Download Practice FileMost modern OFFSET alternatives, including FILTER, SORT, UNIQUE, XLOOKUP, and SEQUENCE, come from the dynamic array family. Our Modern Excel Functions course covers all of them with real-world examples.
Featured Course
Master Excel’s Essential Modern Functions
Leila Gharani
Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.
