Rolling seven days
=AVERAGE(OFFSET(B2, 0, 0, 1, 7)) averages seven cells in row 2 from B2.
What is OFFSET in Google Sheets?
OFFSET starts from a reference cell and returns a range moved by a row and column offset with optional height and width. Rolling averages, dynamic windows, and shifted blocks use OFFSET, though INDEX and FILTER are often clearer today.
When to use it
Use OFFSET for rolling N-day windows, dynamic ranges that grow with a counter, or legacy models already built around OFFSET logic.
When to skip it
Skip OFFSET in new work when INDEX, FILTER, or named dynamic ranges are easier to audit. OFFSET is volatile.
How it works
-
1
Anchor on a starting cell reference.
-
2
Set rows down and columns right to move the top-left corner.
-
3
Set height and width for how big the returned range is.
-
4
Wrap OFFSET inside SUM or AVERAGE for rolling metrics.
-
5
Keep anchors absolute with $ where the start must not drift.
-
6
Test height arguments when lists grow so windows do not truncate.
Examples in Google Sheets
Growing range
=SUM(OFFSET(A$2, 0, 0, COUNTA(A:A)-1, 1)) sums a column that expands with new rows.
Shifted block
=OFFSET(D5, 2, 1, 10, 3) returns a 10-by-3 block starting two rows down and one column right of D5.
Build this without starting from a blank cell
Use a Better Sheets tool for OFFSET, then watch a walkthrough when you want the full pattern.
Better Sheets resources
Common mistakes
- Volatile OFFSET forcing full workbook recalc on every edit.
- Wrong height so new rows fall outside the summed window.
- OFFSET pointing off the sheet edge causing #REF!.
- Using OFFSET when a simple B2:B range would stay dynamic with open-ended refs.
- Mixing relative anchor so copied formulas shift unintentionally.
Frequently asked questions
- OFFSET vs INDEX?
- INDEX returns values at positions. OFFSET returns a movable range reference.
- Is OFFSET volatile?
- Yes in traditional spreadsheet terms. It recalculates frequently.
- OFFSET with charts?
- Named ranges using OFFSET once fed dynamic chart sources.
- OFFSET height zero?
- Invalid dimensions cause errors. Height and width must be positive.
- Modern alternative?
- FILTER, TAKE, and dynamic references replace many OFFSET patterns.
- OFFSET across sheets?
- Anchor must be on the target sheet; cross-sheet OFFSET is uncommon.
- OFFSET in conditional formatting?
- Possible with custom formulas referencing OFFSET ranges.
Related glossary terms
INDIRECT
INDIRECT evaluates a text string as a cell reference. Build addresses from dropdown selections, month tab names, or column letters generated by formulas. It powers dynamic dashboards but is volatile and easy to break with typos.
Read guide →INDEX
INDEX fetches a value from a range by row and column number. Combined with MATCH it powers flexible lookups. Alone it picks nth items from lists, dynamic headers, or matrix intersections without VLOOKUP column limits.
Read guide →MATCH
MATCH searches a range for a value and returns its relative position. Feeding INDEX or OFFSET with MATCH replaces brittle column index math. Sorted approximate match modes support tier tables and date brackets.
Read guide →VLOOKUP
VLOOKUP searches the first column of a range for a key and returns a value from a specified column to the right. It is still everywhere in legacy templates even as XLOOKUP offers more flexible replacements.
Read guide →Done reading about OFFSET?
Membership unlocks 636+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.