---
title: "OFFSET function in Excel: syntax and examples | PracticeExcel"
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://practiceexcelonline.com/functions/offset"
locale: en
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://practiceexcelonline.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 | PracticeExcel"
  "twitter:image": "https://practiceexcelonline.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://practiceexcelonline.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(

## **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 |  | |

## **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>

## **Related functions**

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

```json
{"@context":"https://schema.org","@graph":[{"@id":"https://practiceexcelonline.com/#identity","@type":"Organization","name":"PracticeExcel","url":"https://practiceexcelonline.com/","logo":{"@id":"https://practiceexcelonline.com/#logo"}},{"@id":"https://practiceexcelonline.com/#website","@type":"WebSite","inLanguage":"en-US","name":"PracticeExcel","url":"https://practiceexcelonline.com/","alternateName":["Practice Excel Online","PracticeExcelOnline.com"],"publisher":{"@id":"https://practiceexcelonline.com/#identity"},"workTranslation":{"@id":"https://excelopgaver.dk/#website"}},{"@id":"https://practiceexcelonline.com/functions/offset#webpage","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://practiceexcelonline.com/functions/offset","@type":["WebPage","FAQPage"],"breadcrumb":{"@id":"https://practiceexcelonline.com/functions/offset#breadcrumb"},"isPartOf":{"@id":"https://practiceexcelonline.com/#website"},"mainEntity":[{"@id":"https://practiceexcelonline.com/functions/offset#/schema/question/1"},{"@id":"https://practiceexcelonline.com/functions/offset#/schema/question/2"}],"potentialAction":[{"@type":"ReadAction","target":["https://practiceexcelonline.com/functions/offset"]}],"primaryImageOfPage":{"@id":"https://practiceexcelonline.com/#logo"}},{"@id":"https://practiceexcelonline.com/functions/offset#breadcrumb","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","name":"Home","item":"https://practiceexcelonline.com","position":1},{"@type":"ListItem","name":"Excel functions","item":"https://practiceexcelonline.com/functions","position":2},{"@type":"ListItem","name":"OFFSET function in Excel: syntax and examples","position":3}]},{"@id":"https://practiceexcelonline.com/functions/offset#article","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://practiceexcelonline.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://practiceexcelonline.com/#identity"},"image":{"@id":"https://practiceexcelonline.com/#/schema/image/1"},"isPartOf":{"@id":"https://practiceexcelonline.com/functions/offset#webpage"},"mainEntityOfPage":{"@id":"https://practiceexcelonline.com/functions/offset#webpage"},"publisher":{"@id":"https://practiceexcelonline.com/#identity"}},{"@id":"https://practiceexcelonline.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://practiceexcelonline.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://practiceexcelonline.com/#logo","@type":"ImageObject","caption":"PracticeExcel","contentUrl":"https://practiceexcelonline.com/brand/practiceexcel/icon-512.png","inLanguage":"en-US","url":"https://practiceexcelonline.com/brand/practiceexcel/icon-512.png"},{"@id":"https://practiceexcelonline.com/#/schema/image/1","@type":"ImageObject","contentUrl":"https://practiceexcelonline.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://practiceexcelonline.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://practiceexcelonline.com/sitemap.md) for all pages.