Excel OFFSET function
Returns a range shifted from a cell, with the size you choose. Ideal for "the last N months". In Spanish Excel it is called DESREF.
Syntax
English Excel
=OFFSET(reference, rows, cols, [height], [width])
Spanish Excel (Mexico)
=DESREF(ref, filas, columnas, [alto], [ancho])
Spanish Excel (Spain, Latin America)
=DESREF(ref; filas; columnas; [alto]; [ancho])
| Argument | What it is |
|---|---|
| reference | The starting cell. |
| rows | How many rows to move down (negative moves up). |
| cols | How many columns to move right. |
| heightoptional | How many rows the resulting range has. |
| widthoptional | How many columns the resulting range has. |
Example
OFFSET(start, rows, columns, height, width) returns a range shifted from a cell. With COUNT you know how many months are filled in, so the range moves by itself every time you add one.
=SUM(OFFSET(B1,COUNT(B2:B13)-2,0,3,1))
Common mistake
Combine it with COUNT so the range grows by itself as data is added. Like INDIRECT, it is volatile.
About Ads
This site uses Google AdSense to display ads. These ads help us keep WebHerramientas completely free. Ads are managed by Google and meet strict security and privacy standards.
Ads help us keep WebHerramientas free for everyone
Practice OFFSET
Type the formula in the sheet and check it instantly. Spanish or English function names both work. See all lessons
OFFSET: the last 3 months
OFFSET(start, rows, columns, height, width) returns a range shifted from a cell. With COUNT you know how many months are filled in, so the range moves by itself every time you add one.
In E1, add up the sales of the last 3 filled-in months with OFFSET, so it updates when September is entered.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | Last 3 months | |||
| 2 | Jan | 84,000.00 | Average of last 3 | |||
| 3 | Feb | 91,500.00 | ||||
| 4 | Mar | 78,200.00 | ||||
| 5 | Apr | 102,300.00 | ||||
| 6 | May | 96,800.00 | ||||
| 7 | Jun | 110,400.00 | ||||
| 8 | Jul | 99,700.00 | ||||
| 9 | Aug | 118,900.00 | ||||
| 10 | Sep | |||||
| 11 | Oct | |||||
| 12 | Nov | |||||
| 13 | Dec | |||||
| 14 |
Tip: while typing, click a cell or drag across several to insert their reference. F4 anchors the reference with $.
Frequently asked questions
- What is OFFSET called in Spanish?
- In Spanish Excel it is DESREF. The formula =SUM(OFFSET(B1,COUNT(B2:B13)-2,0,3,1)) is written =SUMA(DESREF(B1;CONTAR(B2:B13)-2;0;3;1)) (or with commas in Mexico).
- Why does OFFSET use semicolons in some Excel versions?
- It depends on regional settings. Where the comma is the decimal mark (Spain, much of South America), Excel separates arguments with semicolons. English Excel and Mexican Spanish Excel use commas.
- What is the most common OFFSET mistake?
- Combine it with COUNT so the range grows by itself as data is added. Like INDIRECT, it is volatile.