reference - Required. Starting cell or range from which to offset (e.g., A1 or Sheet1!$A$1)
rows - Required. Number of rows to offset. Positive = down, negative = up, 0 = same row
cols - Required. Number of columns to offset. Positive = right, negative = left, 0 = same column
height - Optional. Optional. Number of rows in the returned range. Defaults to reference height
width - Optional. Optional. Number of columns in the returned range. Defaults to reference width
| A | B | |
|---|---|---|
| 1 | Data | Value |
| 2 | 100 | Start here |
| 3 | 200 | |
| 4 | 300 | Result |
| 5 | Formula: | =OFFSET(A2, 2, 0) 300 |
Example: A1=100, A3=300
=OFFSET(A1, 2 rows, 0 cols) → 300
Example: Last 3 filled cells
Auto-updates as new months added
Example: Range grows with data
Use in charts for auto-expansion
Example: Sum last 10 entries
Always sums most recent 10 values
Calculate a rolling 3-month average that automatically updates as you add new monthly sales data. The OFFSET function in Excel combined with COUNTA dynamically identifies the last 3 months of data, perfect for sales dashboards, financial reporting, and KPI tracking. This Excel OFFSET formula eliminates manual range updates when new months are added, making it essential for executive dashboards, monthly business reviews, and automated trend reports where data grows over time.
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | 3-Month Avg |
| 2 | Jan | $120K | |
| 3 | Feb | $135K | |
| 4 | Mar | $128K | |
| 5 | Apr | $142K | |
| 6 | Formula: | =AVERAGE(OFFSET(A2,COUNTA(A2:A13)-3,0,3,1)) $135K |
Create a named range for charts that automatically expands as you add new data rows, eliminating the need to manually update chart data ranges. The Excel OFFSET function creates self-maintaining chart ranges essential for automated reporting dashboards, live performance trackers, and business intelligence tools. This OFFSET formula pattern is critical for finance teams with monthly reporting cycles, sales managers tracking daily metrics, and operations teams monitoring real-time KPIs where data constantly grows.
| A | B | ||
|---|---|---|---|
| 1 | Setup | Steps: | |
| 2 | 1 | Go to Formulas → Name Manager | |
| 3 | 2 | Create name: DynamicSales | |
| 4 | 3 | Refers to: | =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1) |
| 5 | 4 | Use "DynamicSales" in chart |
❌ The Problem:
✅ Solution:
=OFFSET(A1, MIN(100, COUNTA(A:A)-1), 0)Use MIN with COUNTA to prevent offsetting beyond available data. This Excel OFFSET formula pattern ensures you never exceed the data range, even as dataset size changes dynamically.
❌ The Problem:
✅ Solution:
=OFFSET(A1, 2, 0, 10, 1)Start from single-cell reference (A1), then use height and width to define range size. The OFFSET function in Excel is clearer when you explicitly control range dimensions rather than starting from a range reference.
❌ The Problem:
✅ Solution:
Use INDEX/MATCH for single-cell lookupsReserve the Excel OFFSET function for truly dynamic ranges (named ranges, rolling calculations, charts). For lookups, use INDEX/MATCH which is non-volatile and faster. OFFSET formula should be strategic, not default.
Master these OFFSET Function variants:
From basics to advanced - AI generates perfect formulas instantly.