**Do you want to create formulas that “move” with your raw data set?**

That’s when you need to use Excel’s OFFSET function.

OFFSET is a great formula whenever you have dynamic ranges involved. Some examples are:

- Calculating the average of the last three months when you add new data to your table
- Getting data from the last cell in your raw data range – either last row or last column in that range
- Grabbing data from a data table based on a point of view chosen by the user
- Complex calculations that require “moving” data sets

OFFSET is a difficult one to get the hang of because of the number of arguments involved.

Think of it as a GPS:

You give it a starting point and then you tell it:

- how many rows to go down
- how many columns to move across
- and what range you want returned

## 3 Practical cases of Excel OFFSET is shown in this Video

## Alternatively read on if you prefer that to watching the video…

## Excel OFFSET Function for Dynamic Calculations – Explained in Simple Steps

Offset is an interesting formula and it’s one that can do so much more than meets the eye. **Offset is a way of giving Excel an address to go to.**

You start off by telling it how many rows to move and then how many columns to move to get to its destination. This destination can be a single house (e.g. a single cell), it can be a street (e.g. many cells in one row or one column), or it can also be a town (e.g. an area of rows and columns).

## Excel Offset: Data Arranged Vertically (0:45)

In this example, we have a table with **Months** in the first column and **Sales** Revenue in the second column.

One way of getting the average of the last six months, is to use the **AVERAGE** function. To do that, you highlight the cells under the Sales column.

However, the problem is that with a dynamic report such as this, there would be new monthly data coming in after August, and so you would have to manually drag the range of the **AVERAGE** function to refer to the most recent six months.

The **OFFSET** function helps you avoid having to do this!

To start, let us go through the **OFFSET** function on its own before we move on to integrating it in the **AVERAGE** function. The syntax of **OFFSET** is:

**=OFFSET(reference, rows, cols, [height], [width])**

### Let’s do a simple example:

You always need a starting point as a reference. Pick any cell that is close to the range that you want to move around in. In this example, we can select cell B3, which is the column header of the Sales column.

After that, indicate how many rows you want to move down. Let’s say I want to move down **1** row. Then specify how many columns you want to move. I want to stay in this column, I will type **0**.

The last two arguments are the **[height]** and the **[width]**. Writing down **1 **for** height **and** 1** for **width**, means I want one cell. These last two arguments can never be **0,0**. The minimum they can be is **1,1**. If you want to refer to three rows, you’d write **3,1**.

For this example, let’s start with using **1,1**. My formula at this stage look like this:

**=OFFSET(B3,1,0,1,1)**

You will notice that the formula’s result is 100. What happens is that it starts at cell B3, and goes down 1 row, then it doesn’t move columns, then it gives us one cell. This gives us 100.

If you set the **row** parameter to **3**, it would move down three rows and give you 130.

**=OFFSET(B3,3,0,1,1)**

For the **column** parameter, putting a **-1** would move one column to the left and return the date in cell A6. Using a **+1** for the **column** parameter moves one column to the right and returns a **0** because cell C6 is empty. This is how **OFFSET** works.

If we want to use a range instead of a single cell, we would specify a value greater than 1 (or smaller than -1) for the **[height]** parameter. Writing the syntax as:

** =OFFSET(B3,3,0,3,1)**

This results in an error. Why? Let’s track what it’s doing. It starts from cell B3, moves down three cells to B6 and then it tries to give us cells B6, B7 and B8. However, it can’t actually put these three cells in one cell.

Whenever the **OFFSET** function is used in a way where the last two arguments are ranges, and they’re not just returning one cell but a range of cells, you need to wrap your **OFFSET** formula in a formula that can handle ranges. Examples of this would be the **AVERAGE, SUM, **or the **COUNT** formula.

In this case, we’re going to use the **AVERAGE** formula. The function would now be:

**=AVERAGE(OFFSET(B3,3,0,3,1))**

It results in 108, which is the average of B6, B7 and B8 cells. If we were to expand on this formula to get the last six months, we have to write the formula differently.

One way is by identifying the last cell in the column using the **COUNT** function and then use **-6** as the **[height]**, which helps us highlight our range “backwards”.

Another way is to find the last cell, then go back 6 rows and then specify the **[height]** as **+6**. It might seem confusing at the start, so let’s start with the simpler method first.

Start by using cell B3 as your **reference**. How many cells should we move down? Use the **COUNT **or **COUNTA** function to count the number of filled cells that we have in the Sales column right below B3.

The **COUNT **formula only counts if values are **numbers**.

The **COUNTA** function counts a cell **even** if it’s **text**.

In this case, it doesn’t matter if we use the **COUNT** or the **COUNTA **function.

Using the **COUNT** function, we highlight the cells under B3. It’s essential to highlight more cells than what you’re currently using, because you want to take into account new data that will be added in the future. The formula would now be:

**=AVERAGE(OFFSET(B3,COUNTA(B4:B15),0,-6,1))**

This is a breakdown of the formula:

**Reference**is cell B3.**Row**parameter uses**COUNTA**function which results in a 7. This tells the**OFFSET**to move 7 cells below B3.**Column**parameter is set to**0**, which means it stays in the same column.**[Height]**parameter is**-6**because we want to take the last 6 cells (move backwards). In this case, it will highlight cells B5 to B10.**[Width]**parameter is**1**by default.

This formula results in 113.

Now, when we add data for a new month on cell B11 – let’s say, **200** – the formula automatically updates to an average of 127, which we can check by comparing it to the average displayed on the status bar when cells B6 to B11 are highlighted.

We can also check this by going to **Formulas > Evaluate Formula**, and then click on the **Evaluate** button. You will see that the final range that it’s taking is the average of B6 to B11, which is what we intended.

The other way of writing this average offset formula is:

**=AVERAGE(OFFSET(B4,COUNT(B4:B14)-6,0,6,1)**

The breakdown of this is:

**Reference**is set to the first data value, B4**Row**uses a**COUNTA**function which counts all the filled cells from B4 to B14 and subtracts 6.**Column**is**0**, indicating no movement or shift of columns.**[Height]**is**6**, which highlights 6 cells (going downwards).**[Width]**is**1**by default, since we only need one column.

This results in 127 as well. These are two different ways of writing the formula.

Let’s take another example where the data is arranged in a horizontal manner.

## Excel Offset: Data Arranged Horizontally (9:01)

To perform the same computation, we follow a similar approach. We start with the **AVERAGE** function followed by the **OFFSET** function. We write it as:

**=AVERAGE(OFFSET(A17,0,COUNT(B17:O17),1,-6))**

which translates as:

**Reference**set to A17**Row**set to**0**, indicating no movement. We want to stay on the same row.**Column**parameter uses the**COUNT**function which counts the number of cells with values from columns B to O. Column O is an estimate of where you think you will have numbers. This part gives us a 7.**[height]**is set to**1**since we are referencing only one row.**[width]**is set to –**6**, which highlights 6 columns to the left of the last completed cell (goes backwards).

This achieves the same as getting the average of the last six months from July. If we again add data for August, it automatically updates to include August and then computes the average of the last six months. This results in 127.

**Just be creative and practice this in all directions until you get the hang of it.**

## Excel Offset: Dynamic Range for Moving Average Calculations (10:42)

Another good example is the case where you allow the user to select the month and your formula provides the average of the** next 3 months.**

This is your raw data:

To perform this computation, we’re going to do the same thing by using the **AVERAGE** function together with **OFFSET**.

Let’s fix our starting point to the column header, B25.

For the r**ow**, we want to move down the number of cells to arrive at the selected month that the user picked from the drop-down. The **MATCH** function would give us the position of the selected month in the data table.

For example, if the selected month is 04/2014, which corresponds to 90, the formula needs to find the position of 90 in the data table, which is the fourth row. To use the **MATCH() **function, the syntax is:

**=MATCH(lookup_value, array, [match_type])**

We refer to the drop-down cell, F25, as the **lookup_value**, and the Month column A26:A51 as the **array.** **[match_type]** is set **0** for a perfect match. The formula will read:

**=MATCH(F25,A25:A51,0)**.

This would count the position of the selected month. In the case of 04/2014, it results in 4.

**Column** can be set to **0** because we want to stay in column B.

**[Height]** depends on the question. If we want to include the selected month in the computation, we would use a **3**.

**[Width]** is set to **1** .

The resulting formula is:

**=AVERAGE(OFFSET(B25,MATCH(F25,A26:A51,0),0,3,1)**

which yields 102.

If we do not want to include the selected month in the computation, we have to add a **+1** to our **MATCH **function. This tells the formula to find this selected month and go down one extra cell. The final formula becomes:

**=AVERAGE(OFFSET(B25,MATCH(F25,A26:A51,0)+1,0,3,1)**

The average yields 113, which is the average of the three months after the selected month, i.e. average of B30:B32.

This is completely dynamic so if you were to select December 2014, you would get an average of 232 for the three months after this month.

This is how you can use the **AVERAGE **function, the **OFFSET** function, and the **MATCH **functions all together to create this dynamic effect in your Excel files.

## Caution!

A word of caution is that the **OFFSET** is a volatile Excel formula, which means it calculates every time you make a move in Excel. To be honest, in the files that I’ve used **OFFSET** in, I’ve hardly experienced my files becoming slow because of using this function. But I also didn’t overdose on them. I recommend that you don’t overuse them.

Download the Workbook HERE.

# The new Excel Dashboards course is here!

Visually Effective Excel Dashboards for your reports.

Redeem your coupon for 75% off below.

IFDecember 10, 2017 at 3:56 amI use OFFSET and Define Name to dynamically track the most recent 5 test results on a spreadsheet that expands over time… and that data is fed to rolling charts.

=OFFSET(‘Test Results’!J6,1,COUNTA(‘Test Results’!$I$5:$DD$5)-5,1,-1)

The problem I run into is when entering the first few results. Any time there are less columns (or rows) with data than you have indicated in your OFFSET formula, the formula will reference irrelevant cells or produce errors if it runs out of columns (or rows).

For example, if my OFFSET is set to -5 (columns), but I only have 2 or 3 columns of data, my Offset formula will still try to reference 5 columns… so it will end up referencing column titles or whatever is to the left of my starting reference, or it will produce an error because there are no more columns remaining on the spreadsheet.

Should I be using INDEX instead? Or maybe there’s some kind of parameter that tells OFFSET to stop at a specified column, or mixing in an IF, THEN formula? I feel like I’m missing something simple:/

Leila GharaniDecember 11, 2017 at 8:22 amHi – Yes you can use INDEX or stick with OFFSET and use an IF. Depending on what you want returned, here are some options:

=INDEX($J$7:$DD$7,,IF(COUNTA($I$5:$DD$5)-5>0,COUNTA($I$5:$DD$5)-5,0)) which returns 0 if the count is below 5 or with offset

you can use this formula for the column argument:

IF(COUNTA($I$5:$DD$5)-5>0,COUNTA($I$5:$DD$5)-5,ABS(COUNTA($I$5:$DD$5)-5)) or:

IF(COUNTA($I$5:$DD$5)<5,CHOOSE(COUNTA($I$5:$DD$5),1,2,3,4),COUNTA($I$5:$DD$5)-5) for the column argument. It depends on what you want returned if the value is below 5.

IFDecember 11, 2017 at 9:09 pmAwesome!! I used a similar version of OFFSET and added a couple MIN/MAX COLUMNS parameters. I’m also going to check out the INDEX option too, just as a less volatile approach. REEEEaally appreciate the info:))

RWJanuary 17, 2018 at 6:21 pmI use the offset function to show the most recent 12 weeks of data on my graphs but there is one recent week where we literally don’t have any data. I want to leave the cell blank but still include it in the cell count which would result in one missing data point on my graph. The COUNT and COUNTA functions won’t allow that and COUNTBLANK only counts the blank cells when I need to count all the cells which from the past 12 weeks which includes 11 cells with numbers and 1 blank cell (the missing data point). Is there a way to use the COUNTIF function to count both blank cells and cells containing data or do you have any other advice?

Leila GharaniJanuary 18, 2018 at 8:00 amHow about using countif with wildcards to find at least one character similar to what I show in this video: https://youtu.be/7fYlWeMQ6L8 Fast forward to 12:20.

You might also find the video interesting if you want to skip blanks in your chart: https://youtu.be/lQG-g6_noPc

Hope this helps.

BenJune 25, 2018 at 6:02 pmHi Leila,

What is the difference between offset and index? Is there anything offset can do that index can’t? If no, why would anybody want to use offset instead of index when it is know that offset is more volatile and can slow down larger files?

PS: I’m using offset for dependent drop-down lists (source list is dynamic, adding rows of data up to 10 times a month), but would definitely change it to index if possible.

Thanks,

Ben