---
title: "OFFSET function in Excel: syntax and examples | ExcelSavvy"
description: "How to use OFFSET in Excel and Google Sheets: move from a starting cell, sum a moving range and total the last three months."
canonical_url: "https://excelsavvy.com/functions/offset"
meta:
  "og:description": "How to use OFFSET in Excel and Google Sheets: move from a starting cell, sum a moving range and total the last three months."
  "og:image": "https://excelsavvy.com/_og/d/c_Page%2Ctitle_~T0ZGU0VUIGZ1bmN0aW9uIGluIEV4Y2VsOiBzeW50YXggYW5kIGV4YW1wbGVz%2Cdescription_~SG93IHRvIHVzZSBPRkZTRVQgaW4gRXhjZWwgYW5kIEdvb2dsZSBTaGVldHM6IG1vdmUgZnJvbSBhIHN0YXJ0aW5nIGNlbGwsIHN1bSBhIG1vdmluZyByYW5nZSBhbmQgdG90YWwgdGhlIGxhc3QgdGhyZWUgbW9udGhzLg%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~T0ZGU0VUKHJlZmVyZW5jZSwgcm93cywgY29scywgW2hlaWdodF0sIFt3aWR0aF0p%2Cp_Ii9mdW5jdGlvbnMvb2Zmc2V0Ig%2Cs_-5uje68UIzaKQwAn.png"
  "og:image:height": "600"
  "og:image:type": image/png
  "og:image:width": "1200"
  "og:title": "OFFSET function in Excel: syntax and examples | ExcelSavvy"
  "twitter:image": "https://excelsavvy.com/_og/d/c_Page%2Ctitle_~T0ZGU0VUIGZ1bmN0aW9uIGluIEV4Y2VsOiBzeW50YXggYW5kIGV4YW1wbGVz%2Cdescription_~SG93IHRvIHVzZSBPRkZTRVQgaW4gRXhjZWwgYW5kIEdvb2dsZSBTaGVldHM6IG1vdmUgZnJvbSBhIHN0YXJ0aW5nIGNlbGwsIHN1bSBhIG1vdmluZyByYW5nZSBhbmQgdG90YWwgdGhlIGxhc3QgdGhyZWUgbW9udGhzLg%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~T0ZGU0VUKHJlZmVyZW5jZSwgcm93cywgY29scywgW2hlaWdodF0sIFt3aWR0aF0p%2Cp_Ii9mdW5jdGlvbnMvb2Zmc2V0Ig%2Cs_-5uje68UIzaKQwAn.png"
---

> Index of every page on this site: [llms.txt](https://excelsavvy.com/llms.txt)

# **The OFFSET function**

Returns a reference a number of rows and columns away from a starting cell, optionally a whole range of a given height and width. Wrap it in SUM or AVERAGE to work with that range.

=

OFFSET(

## **How to use OFFSET**

OFFSET starts at a cell, walks a number of rows down and columns right, and hands back whatever it lands on. Think of directions from a landmark: from A1, go two down and one right. Add a height and a width and you get a whole block of cells instead of a single one.

The point is that the target can move. Let OFFSET work out the number of rows with COUNT or COUNTA and the range grows with your data. `**=SUM(OFFSET(B1,1,0,E1,1))**` adds up as many sales figures as the number in E1 says. The same idea gives you the last three months or a moving average.

OFFSET returns a reference, not a value, so put SUM, AVERAGE or another function around it whenever the range is more than one cell. It also recalculates after every change anywhere in the workbook, so in big files INDEX is the faster choice, as in `**=SUM(B2:INDEX(B2:B100,3))**`.

## **Syntax**

```
OFFSET(reference, rows, cols, [height], [width])
```

<dl><dt>**reference **</dt>
<dd>The cell to start from.</dd>
<dt>**rows **</dt>
<dd>Rows to move down. A negative number moves up.</dd>
<dt>**cols **</dt>
<dd>Columns to move right. A negative number moves left.</dd>
<dt>**height **(optional)</dt>
<dd>How many rows the result covers. Left out, the height of reference.</dd>
<dt>**width **(optional)</dt>
<dd>How many columns the result covers. Left out, the width of reference.</dd></dl>

## **Examples**

D1`=OFFSET(<sup>1</sup>A1,<sup>2</sup>2,<sup>3</sup>1)`

|  | A | B | C | D |
| --- | --- | --- | --- | --- |
| 1 | Month | Sales |  | **95** |
| 2 | Jan | 120 |  | |
| 3 | Feb | 95 |  | |
| 4 | Mar | 140 |  | |
| 5 | Apr | 110 |  | |
| 6 | May | 80 |  | |
| 7 | Jun | 130 |  | |

**In plain words:** Start at <sup>1</sup>A1, move <sup>2</sup>2 rows down and <sup>3</sup>1 column right.

D1=SUM(OFFSET(B2,0,0,3,1))

|  | A | B | C | D |
| --- | --- | --- | --- | --- |
| 1 | Month | Sales |  | **355** |
| 2 | Jan | 120 |  | |
| 3 | Feb | 95 |  | |
| 4 | Mar | 140 |  | |
| 5 | Apr | 110 |  | |
| 6 | May | 80 |  | |
| 7 | Jun | 130 |  | |

D1=SUM(OFFSET(B1,COUNT(B2:B7)-2,0,3,1))

|  | A | B | C | D |
| --- | --- | --- | --- | --- |
| 1 | Month | Sales |  | **320** |
| 2 | Jan | 120 |  | |
| 3 | Feb | 95 |  | |
| 4 | Mar | 140 |  | |
| 5 | Apr | 110 |  | |
| 6 | May | 80 |  | |
| 7 | Jun | 130 |  | |

## **Common OFFSET mistakes**

- OFFSET returns #REF!

  The result lands outside the sheet. Moving up past row 1, as in `**=OFFSET(A1,-1,0)**`, or a height of 0 does it. Check rows, cols, height and width. If the range grows with COUNT, check that the count cannot drop to 0 when the list is empty.
- OFFSET shows an error or a row of numbers instead of one result

  A range of several cells cannot fit in one cell. Wrap OFFSET in a function that returns one value, such as `**=SUM(OFFSET(B2,0,0,3,1))**`.
- OFFSET lands one row away from where I expected

  Rows counts steps from the starting cell, not the row number of the target. `**=OFFSET(A1,2,1)**` lands in B3, not B2. Starting from a header cell, step n reaches row n+1 on the sheet.
- The last three values are wrong when the list has blanks or text

  COUNT counts only numbers. With empty cells in the middle of the list it counts too few, so the range lands too high. Fill the gaps, or count with COUNTA when the column holds text.

## **Questions**

<details>

<summary>

### How do I sum the last three values in a growing list?

</summary>

Count the values and step back from the end, as in =SUM(OFFSET(B1,COUNT(B2:B100)-2,0,3,1)). When you add a month, COUNT goes up by one and the range moves with it.

</details>

<details>

<summary>

### Why is OFFSET slow in big workbooks?

</summary>

OFFSET recalculates after every change anywhere in the workbook. INDEX can usually build the same range without that, as in =SUM(B2:INDEX(B2:B100,3)).

</details>

<details>

<summary>

### How do I make a range that grows automatically?

</summary>

Let COUNTA set the height, as in =SUM(OFFSET(B1,0,0,COUNTA(B1:B100),1)). It starts at the header and covers as many rows as there are filled cells, and SUM ignores the header text.

</details>

## **Related functions**

- [**INDEX**](https://excelsavvy.com/functions/index)
- [**INDIRECT**](https://excelsavvy.com/functions/indirect)
- [**SUM**](https://excelsavvy.com/functions/sum)

```json
{"@context":"https://schema.org","@graph":[{"@id":"https://excelsavvy.com/#identity","@type":"Organization","name":"ExcelSavvy","url":"https://excelsavvy.com/","logo":{"@id":"https://excelsavvy.com/#logo"}},{"@id":"https://excelsavvy.com/#website","@type":"WebSite","inLanguage":"en-US","name":"ExcelSavvy","url":"https://excelsavvy.com/","alternateName":["Excel Savvy","ExcelSavvy.com","PracticeExcel","Practice Excel Online"],"publisher":{"@id":"https://excelsavvy.com/#identity"},"workTranslation":[{"@id":"https://excelopgaver.dk/#website"},{"@id":"https://excelpraxis.de/#website"}]},{"@id":"https://excelsavvy.com/functions/offset#webpage","dateModified":"2026-11-16","description":"How to use OFFSET in Excel and Google Sheets: move from a starting cell, sum a moving range and total the last three months.","name":"OFFSET function in Excel: syntax and examples","url":"https://excelsavvy.com/functions/offset","@type":["WebPage","FAQPage"],"breadcrumb":{"@id":"https://excelsavvy.com/functions/offset#breadcrumb"},"isPartOf":{"@id":"https://excelsavvy.com/#website"},"mainEntity":[{"@id":"https://excelsavvy.com/functions/offset#/schema/question/1"},{"@id":"https://excelsavvy.com/functions/offset#/schema/question/2"},{"@id":"https://excelsavvy.com/functions/offset#/schema/question/3"}],"potentialAction":[{"@type":"ReadAction","target":["https://excelsavvy.com/functions/offset"]}],"primaryImageOfPage":{"@id":"https://excelsavvy.com/#logo"}},{"@id":"https://excelsavvy.com/functions/offset#breadcrumb","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","name":"Home","item":"https://excelsavvy.com","position":1},{"@type":"ListItem","name":"Excel functions","item":"https://excelsavvy.com/functions","position":2},{"@type":"ListItem","name":"OFFSET function in Excel: syntax and examples","position":3}]},{"@id":"https://excelsavvy.com/functions/offset#article","dateModified":"2026-11-16","description":"How to use OFFSET in Excel and Google Sheets: move from a starting cell, sum a moving range and total the last three months.","headline":"OFFSET function in Excel: syntax and examples","inLanguage":"en-US","thumbnailUrl":"https://excelsavvy.com/_og/d/c_Page%2Ctitle_~T0ZGU0VUIGZ1bmN0aW9uIGluIEV4Y2VsOiBzeW50YXggYW5kIGV4YW1wbGVz%2Cdescription_~SG93IHRvIHVzZSBPRkZTRVQgaW4gRXhjZWwgYW5kIEdvb2dsZSBTaGVldHM6IG1vdmUgZnJvbSBhIHN0YXJ0aW5nIGNlbGwsIHN1bSBhIG1vdmluZyByYW5nZSBhbmQgdG90YWwgdGhlIGxhc3QgdGhyZWUgbW9udGhzLg%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~T0ZGU0VUKHJlZmVyZW5jZSwgcm93cywgY29scywgW2hlaWdodF0sIFt3aWR0aF0p%2Cp_Ii9mdW5jdGlvbnMvb2Zmc2V0Ig%2Cs_-5uje68UIzaKQwAn.png","@type":["Article","TechArticle"],"author":{"@id":"https://excelsavvy.com/#identity"},"image":{"@id":"https://excelsavvy.com/#/schema/image/1"},"isPartOf":{"@id":"https://excelsavvy.com/functions/offset#webpage"},"mainEntityOfPage":{"@id":"https://excelsavvy.com/functions/offset#webpage"},"publisher":{"@id":"https://excelsavvy.com/#identity"}},{"@id":"https://excelsavvy.com/functions/offset#/schema/question/1","@type":"Question","inLanguage":"en-US","name":"How do I sum the last three values in a growing list?","acceptedAnswer":{"@type":"Answer","text":"Count the values and step back from the end, as in =SUM(OFFSET(B1,COUNT(B2:B100)-2,0,3,1)). When you add a month, COUNT goes up by one and the range moves with it."}},{"@id":"https://excelsavvy.com/functions/offset#/schema/question/2","@type":"Question","inLanguage":"en-US","name":"Why is OFFSET slow in big workbooks?","acceptedAnswer":{"@type":"Answer","text":"OFFSET recalculates after every change anywhere in the workbook. INDEX can usually build the same range without that, as in =SUM(B2:INDEX(B2:B100,3))."}},{"@id":"https://excelsavvy.com/functions/offset#/schema/question/3","@type":"Question","inLanguage":"en-US","name":"How do I make a range that grows automatically?","acceptedAnswer":{"@type":"Answer","text":"Let COUNTA set the height, as in =SUM(OFFSET(B1,0,0,COUNTA(B1:B100),1)). It starts at the header and covers as many rows as there are filled cells, and SUM ignores the header text."}},{"@id":"https://excelsavvy.com/#logo","@type":"ImageObject","caption":"ExcelSavvy","contentUrl":"https://excelsavvy.com/brand/practiceexcel/icon-512.png?v=3","inLanguage":"en-US","url":"https://excelsavvy.com/brand/practiceexcel/icon-512.png?v=3"},{"@id":"https://excelsavvy.com/#/schema/image/1","@type":"ImageObject","contentUrl":"https://excelsavvy.com/_og/d/c_Page%2Ctitle_~T0ZGU0VUIGZ1bmN0aW9uIGluIEV4Y2VsOiBzeW50YXggYW5kIGV4YW1wbGVz%2Cdescription_~SG93IHRvIHVzZSBPRkZTRVQgaW4gRXhjZWwgYW5kIEdvb2dsZSBTaGVldHM6IG1vdmUgZnJvbSBhIHN0YXJ0aW5nIGNlbGwsIHN1bSBhIG1vdmluZyByYW5nZSBhbmQgdG90YWwgdGhlIGxhc3QgdGhyZWUgbW9udGhzLg%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~T0ZGU0VUKHJlZmVyZW5jZSwgcm93cywgY29scywgW2hlaWdodF0sIFt3aWR0aF0p%2Cp_Ii9mdW5jdGlvbnMvb2Zmc2V0Ig%2Cs_-5uje68UIzaKQwAn.png","inLanguage":"en-US","url":"https://excelsavvy.com/_og/d/c_Page%2Ctitle_~T0ZGU0VUIGZ1bmN0aW9uIGluIEV4Y2VsOiBzeW50YXggYW5kIGV4YW1wbGVz%2Cdescription_~SG93IHRvIHVzZSBPRkZTRVQgaW4gRXhjZWwgYW5kIEdvb2dsZSBTaGVldHM6IG1vdmUgZnJvbSBhIHN0YXJ0aW5nIGNlbGwsIHN1bSBhIG1vdmluZyByYW5nZSBhbmQgdG90YWwgdGhlIGxhc3QgdGhyZWUgbW9udGhzLg%2Ceyebrow_Excel%2Bfunctions%2Csite_practiceexcel%2Ccode_~T0ZGU0VUKHJlZmVyZW5jZSwgcm93cywgY29scywgW2hlaWdodF0sIFt3aWR0aF0p%2Cp_Ii9mdW5jdGlvbnMvb2Zmc2V0Ig%2Cs_-5uje68UIzaKQwAn.png"}]}
```

## Sitemap

See the full [sitemap](https://excelsavvy.com/sitemap.md) for all pages.
