📎 This article includes 1 downloadable practice file ↓
In this article
OFFSET returns a range a set number of rows and columns away, optionally with a new size. Classic for rolling totals and dynamic chart ranges.
Syntax
=OFFSET(reference, rows, cols, [height], [width])| Argument | What it means |
|---|---|
rows, cols |
How far to move. |
height, width |
Size of the returned range. |
Examples
Example 1
=SUM(OFFSET(B1, COUNT(B:B)-2, 0, 3, 1))Sum of the last 3 values in column B.
💡 OFFSET is volatile — it recalculates on every change and can slow big files. INDEX-based ranges are faster: =SUM(INDEX(B:B,COUNT(B:B)-1):INDEX(B:B,COUNT(B:B)+1)).
OFFSET in practice
Where you will use it
- Last N months for a chart
- Dynamic ranges
- Moving totals
Worked example
H1:H12 holds monthly sales 100…210; the formula sums the last N months.
| Months | Formula | Result |
|---|---|---|
| 3 | =SUM(OFFSET($H$1,COUNT($H$1:$H$12)-A2,0,A2,1)) | 600 |
| 6 | =SUM(OFFSET($H$1,COUNT($H$1:$H$12)-A3,0,A3,1)) | 1110 |
| 1 | =SUM(OFFSET($H$1,COUNT($H$1:$H$12)-A4,0,A4,1)) | 210 |
Results calculated in Excel.
Mistakes people make
| Mistake | What to do instead |
|---|---|
| Volatile | Recalculates every time; INDEX:INDEX ranges are faster |
| Off-by-one | Check height and start carefully |
Related functions
📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.
⚡ Interactive Practice Lab & Formula Tester:
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
- 📗Practice workbook: Lookup & reference17 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.⬇ XLSX · 35 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
✨ Ask AI about this article
Stuck on a step? Ask a question and the AI answers using this article.
Free · AI can be wrong