To specify a range with OFFSET in Excel, use the formula =OFFSET(reference, rows, cols, [height], [width]), which returns a range that is a given number of rows and columns from a starting reference. For example, =SUM(OFFSET(A1,2,1,3,2)) sums the 3-row by 2-column range starting at cell B3 (2 rows down, 1 column right from A1).
What Is OFFSET and Why Use It?
OFFSET is a volatile worksheet function that returns a reference to a range that is offset from a starting cell or range by a specified number of rows and columns. It can also return a range of a specific size (height and width). This makes it powerful for building dynamic ranges that automatically adjust when your data changes, such as when you add new sales entries or monthly figures.
Unlike static ranges like A1:B10, OFFSET can be combined with functions like COUNTA to create ranges that expand or contract. For instance, =SUM(OFFSET(A1,0,0,COUNTA(A:A),1)) sums all non-empty cells in column A, starting from A1.
However, OFFSET is volatile, meaning Excel recalculates it every time any change is made to the workbook, which can slow down large spreadsheets. For most dynamic range needs, Excel 365 users can use the newer FILTER or INDEX functions instead, but OFFSET remains widely used for compatibility.
How OFFSET Works: Arguments Explained
The OFFSET function takes five arguments, the last two are optional:
- reference (required): The starting point. It can be a cell or a range. If you use a range, OFFSET starts from the top-left cell of that range.
- rows (required): The number of rows to move up (negative) or down (positive) from the reference.
- cols (required): The number of columns to move left (negative) or right (positive).
- height (optional): The number of rows in the returned range. If omitted, the height is the same as the reference range.
- width (optional): The number of columns in the returned range. If omitted, the width is the same as the reference.
For example, =OFFSET(C3,2,-1,4,1) starts at C3, moves 2 rows down to C5, then 1 column left to B5, and returns a range that is 4 rows tall and 1 column wide, i.e., B5:B8.
Practical Examples of Using OFFSET
Here are three common ways to use OFFSET in real spreadsheets:
- Dynamic last N values: Suppose you have daily sales in B2:B100. To sum the last 7 values (assuming no blanks), use
=SUM(OFFSET(B1,COUNTA(B:B)-7,0,7,1)). This moves down from B1 by the count of non-empty cells minus 7, then takes 7 rows. - Creating a moving average: To average the last 3 months from a list in A2:A13, use
=AVERAGE(OFFSET(A1,COUNTA(A:A)-3,0,3,1)). This updates as you add new months. - Two-dimensional offset: To sum a block of data that is 2 rows down and 3 columns to the right of a starting cell, use
=SUM(OFFSET(A1,2,3,5,2))which returns a 5-row by 2-column range starting at D3.
For a worked example: With data in A1:C5, =OFFSET(A1,1,1,2,2) returns the range B2:C3. If you put that formula in a cell, it will spill the values if used as a dynamic array, but in older Excel it may return a #VALUE! error unless used inside a function that accepts a range.
How to Choose Between OFFSET and INDEX
INDEX is a non-volatile alternative that can often achieve the same dynamic range without the performance hit. For example, to return the last value in column A, you can use =INDEX(A:A,COUNTA(A:A)). To create a dynamic range for a chart, you can define a named formula using INDEX: =Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A)).
Choose OFFSET when you need to move a fixed number of rows/columns from a reference, especially when the starting point itself is dynamic. Choose INDEX when you need to return a specific cell or a range that ends at a certain position, as it is more efficient and easier to debug. In Excel 365, consider using FILTER or TAKE for simpler dynamic arrays.
Common Problems and How to Fix Them
Here are frequent issues users encounter with OFFSET:
- #VALUE! error: This occurs when the height or width is negative or when the offset goes beyond the worksheet boundary. Check your rows and cols arguments.
- #REF! error: The reference is invalid, often because the starting cell has been deleted or the sheet is protected.
- Volatile recalculation: OFFSET recalculates on every change, slowing down large workbooks. Minimize use or replace with INDEX.
- Spill issues: In Excel 365, if you enter OFFSET without a surrounding function, it may spill the entire range. Wrap it in SUM, AVERAGE, or another function if you only need a single value.
To debug, use the Evaluate Formula tool (Formulas tab > Formula Auditing > Evaluate Formula) to see each step.
OFFSET in Excel for Mac and Excel for the Web
OFFSET works identically in Excel for Mac and Excel for the web. The syntax and arguments are the same. On Mac, the formula bar works the same, but keyboard shortcuts differ: use Cmd+Enter to confirm an array formula (though OFFSET rarely needs array entry). In Excel for the web, OFFSET is supported, but dynamic array functions may require a Microsoft 365 subscription. The only difference is that on Mac, you might need to use Fn keys for some function shortcuts.
Alternatives to OFFSET for Dynamic Ranges
If you are using Excel 365 or Excel 2021, you have several modern alternatives:
- INDEX: Non-volatile and widely compatible.
- FILTER: Returns a dynamic array based on criteria, e.g.,
=FILTER(A2:B10,B2:B10>100). - TAKE and DROP: New functions to extract first/last N rows or columns.
- LET: To define variables for complex calculations.
For example, to sum the last 3 values in a column, you can use =SUM(TAKE(FILTER(A:A,A:A<>""),-3)) in Excel 365. This is more readable and non-volatile.
Frequently Asked Questions
How do I use OFFSET with a named range? You can define a named formula that uses OFFSET, e.g., create a name “MyRange” with =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1). Then use =SUM(MyRange).
Why does OFFSET return #VALUE! when I use it in a cell? OFFSET returns a range reference, which Excel cannot display in a single cell unless you wrap it in a function that accepts a range, like SUM or AVERAGE. If you want to see the range, use it inside a formula like =SUM(OFFSET(...)).
Can OFFSET return a range that is not rectangular? No, OFFSET always returns a rectangular range defined by height and width. For non-rectangular selections, you need to use multiple OFFSETs or other functions.
Is OFFSET available in all Excel versions? Yes, OFFSET has been available since Excel 97 and works in all modern versions, including Excel for Mac and Excel for the web.
How can I make a chart update automatically with OFFSET? Create a named range using OFFSET and use it as the chart’s data source. For example, define a name “ChartData” as =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),2) and then set the chart’s series values to that name.
Does OFFSET work with conditional formatting? Yes, you can use OFFSET in a conditional formatting formula to apply formatting based on a dynamic range. For example, highlight cells in A1:A10 that are greater than the average of the last 3 values: =A1>AVERAGE(OFFSET($A$1,COUNTA($A:$A)-3,0,3,1)).
Related
- Excel Power Query Merge Join Kinds Explained
- Excel Power Query: Merge Multiple Sheets Easily
- Excel Power Query Issues: Fixes & Common Errors
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel CONCAT Formula: Join Text and CellsExcel’s CONCAT function joins text from cells or text you type into a formula; it does not add…
- Excel Average by Range: Formula & ExamplesTo average a range of cells in Excel, use the AVERAGE function: =AVERAGE(A1:A10) averages all numeric values in…
- Recursive LAMBDA in Excel: Examples That WorkRecursive LAMBDA formulas call themselves to repeat a calculation, such as finding a factorial or stepping through a…
- Excel Formula for Checking If a Date Is Before Another DateTo check whether one date is earlier than another in Excel, use the less-than operator: with the date…
- Excel XLOOKUP Examples: Formulas and TipsMicrosoft Excel’s XLOOKUP finds a value in one range and returns the corresponding value from another; for example,…
- SUMIFS in Excel: Sum Values by Multiple Criteria ExplainedSUMIFS in Excel adds values only when they meet one or more conditions. For example, with sales in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.