Which function returns a reference to a cell that is a specified distance from a base cell

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

Like this:

3 Practical cases of Excel OFFSET is shown in this Video

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

The Complete Course is Here!

You’d like to Improve your Excel Skills? Check out my Course: Advanced Excel Tips & Formulas to Work Smarter!

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 row, 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.

Excel Dashboards that Inform & Impress

Use these techniques in your own reports

Unbeatable value!

14 Comments

1. IF December 10, 2017 at 3:56 am

   I 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 Gharani December 11, 2017 at 8:22 am

     Hi – 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.

2. IF December 11, 2017 at 9:09 pm

   Awesome!! 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:))

3. RW January 17, 2018 at 6:21 pm

   I 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 Gharani January 18, 2018 at 8:00 am

     How 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.

4. Ben June 25, 2018 at 6:02 pm

   Hi 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

5. Renei September 27, 2018 at 2:56 am

   Hi,

   I work with financial performance data and for calculating inception performance I need inception count.

   I wanted to find out whether I am able to insert an offset function that will automatically include new entries when I populate performance on a monthly bases while still fixing the inception cell.

   For example
   With a count formula, I can have
   Count (#B3:BF3) to fix the inception column B, which is fine. But this will require an update everytime new data is populated-eg, if an extra month of performance is added, reference BF3 will have to be updated to BG3.

   , I figured an offset function could be more efficient as the formulas automatically update to include new data, however, I don’t know how to fix the inception reference B3, and still have the formula updating to BG3.

   Is there a way to achieve this with offset functions?

6. Joy February 11, 2019 at 2:42 am

   Leila, do you have examples where you use the OFFSET function to create a dynamic range name?

   Thank you so much for all your amazing work! As a trainer teaching Excel for over 30 years, I wish I had someone like you as I developed my skills.

   Cheers!
   Joy

   • Leila Gharani February 11, 2019 at 6:01 pm

     Hi Joy – Thank you! I’m glad you enjoy the tutorials. You’ll find an example of offset in name manager in this video. Towards the end, where I create the dependent drop down. I try to use the INDEX formula instead wherever I can, as INDEX is not volatile.

7. It is very needed to be a professional at least in one of Microsoft office programs. Your post is very useful for beginners in this matter. Excel is very hard to learn program.

8. Wow. This function is really useful. I often use this program, especially in the learning process. And the offset function allows you to save time to fill the data. Fine.

9. Maarten Jong June 12, 2019 at 4:28 pm

   Hi Leila,
   It is true that offset is a volatile function but of all the ways you could slow down an Excel file, offset should be the least of your worries. It is a great function that trumps its little brother ‘index’ as it is not bound to any limits.

   Point is: people, use Offset as much as you like. Don’t worry about it being volatile.

10. oztims November 20, 2019 at 11:23 am

    I use OFFSET in balance sheets. So anything set of columns that includes a running total, adding credits and subtracting debits, and adding the the balance above. The reason for this is could can safely cut and paste to re-order lines, and doing so doesn’t screw up the balance in between the source and the destination line. If you use the normal method =C2+A1-A2 and run that formula down the balance column, then when you decide to move a line later, the balance on that particular line will then refer to the new location, and the running total balance is now wrong. This leads you to have to always insert blank line at the point where you want the data moved to (perhaps because your data is out of order), copy the data to the correct (rather than move), delete the original line, recopy the balance formulas. You have to be careful not to mess it up. Using the offset method for the balance column means you can move data around as you like – you can even sort the table by date and the balances will still be correct.

    • Chris January 11, 2020 at 4:12 pm

      Great use of Offset. Thank you for your input!

Comments are closed.