Excel OFFSET Function Explained

The OFFSET function is one of Excel’s best kept secrets. Probably because it’s quite tricky to explain, and can be confusing to understand.

So, in this tutorial I’m going to do my best to keep it simple so you can get your head around it and then I’ll cover a few of the most useful ways I find to use OFFSET.

Download the workbook and practice what you learn, or reverse engineer the formulas for deeper understanding.

Excel OFFSET function technical explanation.

Skip this if you want, but I know some of you will want to know it too.

The OFFSET function returns a cell or range of cells that are a specified number of rows and columns from the original cell or range of cells.

The Excel syntax is:

=OFFSET(reference,rows,columns,height,width)

What the? That’s what I thought when I first stumbled across OFFSET.

Here’s an easy way to think about it. I liken it to a treasure map (an easy one my 4 year old son could follow). The spot marked ‘X’ can either be a single cell or a range of cells (more on this later).

In our example let’s say our starting point is cell A1 and the spot marked ‘X’ is cell D5.

So in treasure map speak our OFFSET function would read:

=OFFSET(starting at A1, step down 4 rows (you’ll be in cell A5), then step across 3 columns (you’ll be in cell D5), Including cell D5 you’ll find the treasure in a range that’s 1 cell high, and one cell wide i.e. cell D5 )

This is how our treasure map function would read in Excel:

=OFFSET(A1,4,3,1,1)

Let’s break it down into the arguments:

=OFFSET(reference,rows,columns,height,width)

1) The reference is the starting point in your treasure map/worksheet.

2) Rows are the number of rows you want Excel to move from the starting point.

3) Columns are the number of columns you want Excel to move from the starting point.

4) Height is the number of rows ‘X’ occupies, in our example it’s 1.

5) Width is the number of columns ‘X’ occupies, in our example it’s 1.

That’s all good and well, but I can’t think of any pirates who are likely to be using Excel for their treasure maps, so what else is it good for?

Hold up, let’s walk before we run.

Things you should know

You can enter the OFFSET formula in any cell, except of course the cell/cells where ‘X’ is. In the above example our formula is in cell A7. Although in this example it returns the value in cell A7, it isn’t actually designed to use on its own, but because in this example we’re asking OFFSET to only return 1 cell, it returns the value in the cell. If we asked it to return a range of cells it would return an error.

The reference can be a single cell or a range of cells, likewise the result OFFSET returns

The height and width arguments are optional, if you don’t enter a height and width it will return a range that is the same height and width as the reference.

Examples

Practical Uses

1) Create a Dynamic SUM Formula

How often do you have to update the range of a SUM formula because you’ve added a row just above the SUM and it hasn’t automatically picked it up? If you’re like me it’s all the time. Here’s how I use OFFSET to save time by making my SUM formulas dynamic.

In the table below I have Totals in row 56. In cell D56 my SUM formula using OFFSET would look like this:

=SUM(D45:(OFFSET(D56,-1,0)))

2) Use OFFSET and MATCH functions together with SUM and MAX

There are a few things going on in the example below:

I. Just to make it a bit fancy and link to a previous tutorial, in Cell B61 there is a drop down list or data validation list as they’re called in Excel.

By changing the selection in cell B61 my formulas in cells B62 and B63 dynamically change to give the SUM of the viewers for the selected program and the MAX viewers.

Since I already covered SUM with OFFSET above, I’m just going to cover the OFFSET and MATCH section of the formula in the example below.

II. Working through the ‘Sum of Viewers’ formula, the reference cell for the OFFSET function is B66, i.e. the junction of the table.

III. We then use the MATCH function to find the row that Ben Ten is on from the range B67:B73, with row 67 being 1, row 68 being 2 etc. This result is then used to instruct the OFFSET function how many rows from B66 Ben Ten is on. In this case it is 2.

IV. We then use 1 as the number of columns so that the start of the SUM is from column C.

V. The number of rows in our range we want summed is 1

VI. The number of columns in our range we want summed is 4.

VII. The MAX and MIN formulas for the Highest and Lowest Viewers works in the same way, only instead of SUM we used MAX and MIN.

3) SUM COUNT and OFFSET

Being an accountant I like to double check everything. For example, when working with tables that have totals on columns and rows I like to have a ‘Control’ total that cross checks to make sure the totals add up correctly. My ‘Control’ total should always come back to 0 to indicate there is no discrepancy. Like in the example below in column G, I usually colour the font grey as it’s something that isn’t part of my analysis or anything that I want to print.

The control formula in cell G86 is

=SUM(OFFSET($A$78,1,1,COUNT($B$79:OFFSET($B$86,-1,0,1,1)),4))-F86

Sure I could have just used

=SUM(B79:E85)-F86

which works too. But if I keep adding rows or columns to my table I have to alter my SUM control formula too.

Let me explain how this works.

I use the SUM to add the figures in the body of the table, and I use the OFFSET and COUNT to tell me what the range is. Should I ever add any rows to the table, my Control formula will automatically pick them up.

i) OFFSET($A$78,1,1 is telling Excel to start my SUM from cell B79.

ii) COUNT($B$79:OFFSET($B$86,-1,0,1,1)) is telling Excel how many rows in my range

iii) ,4)) is telling Excel how many columns in my range. I could have used another COUNT OFFSET combination like I did above to calculate the number of columns in my range, but since I’m confident I’ll only ever have 4 regions (East, North, South and West) I’m happy to leave this hard coded at 4 columns.

iv) –F86 is simply making sure the total of my Control is equal to the total of the table in cell F86. It’s quicker and easier to check Controls equal 0 than comparing two numbers visually to make sure they match.

Like many Excel functions, on its own OFFSET isn’t much use, but team it up with other functions and it’s a winner.

I hope you liked this tutorial. Please leave me a comment below and spread the knowledge by sharing this post with your friends and colleagues on Facebook, Twitter, Google+ and LinkedIn.

Hi Jitin,
Select 7 consecutive cells, go to formula bar and paste the formula there, then press Ctrl+Shift+Enter. This will enter the formula in all 7 cells, and all 7 results will be displayed on those cells.
Catalin

Table 2 is a table which column A and B should be identical to column A and B of table 1.

Table 1 has column A,B,C,D and E

Table 2 has columns A,B (same as table 1 so i used OFFSET formula) columns C,D and E which
are different from columns from table 1

the problem, while I add a new row in table 1 , it will be added to Table 2 but the text
in column C, D and E will not move with its original row as it was related
before adding the new row. the text in column C,D and E will be on the same row
of the new row in case i inserted the new row above.

So how can i able to insert a row on Table 1 and the related text of this row in table 2 will continue to appears on the same row?

Hi Liran,
Please upload a sample workbook with your data structure and details on what you want to do, itwill be easier for us to understand your situation.
You can use our Help Desk system to upload the file.
Thanks
Catalin

Where you have used the formula ,=SUM(OFFSET(B66,MATCH(B61,B67:B73,0),1,1,4)))
for calculating the sum of BEN TEN’S viewers, what if we add/have one more row of BEN TEN in the data table, how can we then change the formula to sum up that row too?

Hi Raj,
Ben Ten Viewers are in columns C,D,E and F (not in rows!) To add one more column with Viewers, just increase the last argument ([width]) of the OFFSET formula, from 4 to 5.
Adding rows to the table means adding a new Program, not viewers to BEN TEN. In case you want to add a new Program (a new row), the only change that must be made is in MATCH formula, to extend the search range with 1 row: from MATCH(B61,B67:B73,0) to MATCH(B61,B67:B74,0)
Hope it helps,
Catalin

Question, how can I use the above example to create a “Top 10″ or “Bottom 10″ list?

In my case, I’m refering my list from a pivot table (which works great), but I’m limited to using Auto Filter to sort from smallest to highest. Which isn’t all that great as every time the subject changes to provide a new list, the filtering resets.

Hi,
I would like to “transfer” (= show somewhere else) content of my columns based on P_name header (e.g. if P1, transfer A,B,C; if P4, transfer L,M,N).
P_name P1 P2 P3 P4
content A D G L
B E H M
C F K N
I tried to use OFFSET/MATCH formula, but it is showing this formula contains an error: =OFFSET(MATCH(header,P_name,0),1,0)
Any idea, what I am doing wrong?
I can see the problem, if I enter it as an array formula that I get always values from first row only, because my OFFSET formula defines row 1 from the reference. How can I force the formula to “adjust” rows in an array format?
Thanks,
Peter

Unfortunately its a big file with much irrelevant but confidential stuff in it.
The problem formula is quite simple though; its a simple row of numbers from which i need an average. I use OFFSET to keep it flexible with the number of months passed.

Hi Mynda,
Thank you for your suggestion.
Unfortunately this only provides an absolute value of the total after adding both plusses and minusses.
The offset formula was taking 1 row and 5 columns, i.e. 5 cells with both positive and negative numbers, and i wanted an average of the total regardless of foresign. (values W26 = 2, X20 = 5)

I played a bit around and the following formula seems to work.
=SUMPRODUCT(ABS(OFFSET(J26;0;W26;1;$X20)))/$X20

To be honest i am not sure why the sumproduct functions works for me though

Ah, now I understand that the OFFSET returns more than one cell it makes more sense.

The reason SUMPRODUCT works is because it can handle arrays. So, the OFFSET function returns an array of 5 cells, which are then fed to the ABS function which strips out the negative signs, and SUMPRODUCT then adds them up.

You can read more on array formulas here (SUMPRODUCT is an array formula however you don’t need to enter it with CTRL+SHIFT+ENTER like typical array formulas.

No, you can’t use the HLOOKUP to return the cell reference for OFFSET because HLOOKUP returns the value in the cell you’re looking up, not the cell address or reference which is what OFFSET needs. But you can use the INDEX function to return the cell reference.

INDEX and MATCH together can work in the same way as VLOOKUP or HLOOKUP. More on INDEX & MATCH here. e.g.

=INDEX(your range, the row number in the range you want returned, MATCH(lookup value, lookup range, 0))

Note: you can also use MATCH to return the row number you want returned. So it would be an INDEX, MATCH, MATCH formula

Your training site is very helpful in explaining the offset concept and its application. For the problem I am trying to solve I tried using your response to few of the questions which come close to mine, however it did not work.

Let me explain the Excel problem I have:

I have two worksheets in my work book. Worksheet A and Worksheet B.

Worksheet A has 10 sections with 10 rows each. Column A for each of the 10 rows in the sections has “add question here” as the content. There are other columns in the sections that have formulas in them. I have a macro that is executed by a button in this sheet which basically adds a row above the last row in each of the 10 sections based on placement of cursor.

Worksheet B is a summary of the 10 sections in Worksheet B. There is a formula in this worksheet that counts the rows in each section using the Count A function. My issues is that when my macro in worksheet A adds a new row, the formula for Count A does not update the row count

Since this is my first introduction to this function, I have not used it yet. But I think I can apply this to a rolling 4 quarter (or rolling 12 month) drug cost (or utilization) summary and then graph those results. I really like the combination with =match() and a drop down list. I can then provide a dynamic tool for clinical pharmacists to graphically review utilization and cost over time for any therapeutic class of medications. They can scroll through each class using the drop down.

Clearly, the key to effectively applying =offset() is in how you have the data arranged. You really need to know your end result before you arrange the initial table.

Thanks very much for your very informative and very detailed explanations on excel functions. On your table above,on using OFFSET,MATCH, SUM,MAX/MIN, functions, how do you set the same formuals (sum,max & min) to select Bat man instead of Ben ten, ie select the program as Bat man and insert the formulas that will give sum of viewers, highest and lowest viewers, as you have done for Ben ten.i have tried this but its not working out.

The formula is referencing cell B61 which contains ‘Ben Ten’. To change the formula to return results for Bat Man you can either type ‘Bat Man’ in cell B61 or replace the reference to B61 in the formula with “Bat Man” (including the double quotes around Bat Man).

I recommend you use the Evaluate Formula tool (Formulas tab of the ribbon > Evaluate Formula) to inspect the formulas and troubleshoot where you’re going wrong. If you’re still stuck you can send me the file via the help desk.

I have an amortization spread template with an x & y axis graph that can go anywhere from say, 5 to 10 yrs for a loan amortization. While I can readily adjust the graph for different amortizations, I have a department that works for me that are not near as conversent on Excel. So I need a way to make the graph “range” dynamic and adjust to the changing amortization periods. I have read the above OFFSET explanation and think that it may be useful, but I can’t seems to intergrate it into a “range”. Any suggestions

You claim that the OFFSET() function can return a range of cells. Can you show me how that can be done. So far it only returns the value in one cell. I would like to know what function can return a range of cells in the form A4:B16 for example.

If you enter OFFSET in one cell but the formula is returning a range of cells Excel can only display the first value in the range since you only entered the formula in one cell.

If you wan’t OFFSET to return the values from a range of cells (as opposed to passing that range to another formula) then you first have to select the number cells you need. i.e. if you want to return a range that is 5 cells high then you first need to select 5 cells, say D1:D5, then you enter your OFFSET formula in the active cell (D1) say, =OFFSET(A1,,,5) and press CTRL+SHIFT+ENTER to return a multi-cell array. This will enter the 5 values in cells A1:A5 in cells D1:D5.

The Examples given are really nice.
But can you Please share how can we use offsets in Charts. I want to present the data of 15 days in line graph and want when ever I insert a new column for a new date my oldest date should be removed from the chart. I insert a new column in the beginning of the workbook. I want the range to be fixed.
example- I have fixed the range of my chart from column ‘D’ to ‘I’ and if i insert a new column ‘E’ then ‘J’ column which was earlier ‘I’ should not be shown in graph.
Please Help. Thanks

I raked my brains trying to get an average with a variable range that depends on the month we are in, for a spreadsheet that tracks expenses. I read several other forums, tutorials, and explanations, but I couldn’t figure it out until I read your page.

How did I use it? I have one column (F) where I want the average to show, and twelve columns for expenses, one for each month, with entries that start at row 7. But what I want is the monthly average to date, and AVERAGE() spreads it over 12 months, so there came OFFSET() to the rescue, and this is what I did:

=AVERAGE(OFFSET(F7,0,1,1,MONTH(TODAY())))

But I didn’t want the partial expenses of the current month to skew my averages, so I added a -1 after the month:

=AVERAGE(OFFSET(F7,0,1,1,MONTH(TODAY())-1))

The problem I have now is that it will never include December. So I introduce an IF():

I find the explanation is very effective and helpful. I am the first time user of this site and presently working on Pricing file, I faced difficulties while using the rank function, in trying to rank the sales and margins of various products within the Product family – eg. we have over 200 product families and within each family on an average 50 Item numbers, all in one worksheet – I need to rank the sales and margin of individual items within the family – can you suggest how to achieve this without specifying the Rank formula for each family.
Thanks

I use OFFSET primarily for dynamically creating a data validation list.

I encountered an error, however and have not seen anyone speak to it. I created the following data validation list in an xlsx workbook using Excel 2010. The drop-down works as expected. When I open the same workbook in Excel 2007, there is no drop-down and the data validation list is set to “any value”.
An error appeared in 2007 stating an OFFSET could not be used to address areas on other worksheets. Unfortunately, there is no way around it. Any ideas?

At any rate, I tried to simulate the dynamic range using your offset function, And the only time
I got the error was when there were no data yet. Exactly, as you described it; that is, the data validation
list is set to “any value”. After I have reset it, the error doesn’t occur anymore.

Yours is the 5th example I read, including excel forums. All I needed was the first example of the treasure map to understand. Once I did, I kind of felt stupid for how simple it was. And that is the genius of a great teacher!
Thank you. Thank you. Thank you. !

AWESOME FORMULA! I was trying to figure out a way to capture data dynamically in a pivot table. I was currently using a named range, but then it made the file large because of the empty cells it was capturing. This eliminates the need to have named ranges. Awesome:-)

However, isolating the offset functions which concern your question,
I can see that the column headers at row 6 must have started in column B.
So the maker of this formula had to improvise by deducting 1.

The reference is column A.
The row argument is 0, hence, the formula is in any row in column A… let’s just say at row 7.
So the formula is at row 7 where the first data is for the “Rate” and “Qty” columns are.
Hence: Offset(A:A,0… (zero).
The column argument uses the Match Function. It is looking for “Rate” in the
column headers at row 6. Now… to answer your question. Why -1?
Consider the data below. The first column header is at column B, and
the formula uses 6:6 reference to represent all of the column at row 6.
In other words, It started counting from column A… so if you isolate
MATCH(“Rate”,6:6,0) this will return 5. Now if you put 5 in the Offset function:
Offset(A:A,0,5) it means row 7 and col A + 5 or column 6 which will
return “Qty” actually for the Offset Function’s purpose. Hence, the minus 1.

The same goes for the second offset function finding “Qty”.
Try to experiment and isolate the OFFSET and MATCH Function by placing them
in column A.

THANK YOU!
This is a great explanation. I was killing myself trying to figure out what I was doing wrong. It was so simple, ” If we asked it to return a range of cells it would return an error.” No where else I checked made that statement not even Microsoft (unless I missed it in my frustration).

I tried to use this dynamic offset for COUNTIF and it didn’t work for me. The only time my if would count is if I inserted a row before the last row, not after. But I noted that the count advanced whether I used the dynamic formula or not. Does this only work for SUM?

This will work with countif or any function that accepts a range for an argument.

I replaced the SUM with COUNTIF in the “Dynamic SUM using OFFSET” table under NIL column:
and inserted some with good results.

=COUNTIF(E45:(OFFSET(E56,-1,0)),"<6")

Please make sure that the range argument of your OFFSET function
should be where your formula is before you insert a row.
In the example, the formulas are in row 56. So make sure,
you must be in the same row. So that when you offset by -1 row; that is,
(i.e. 56-1) 55, your addends should be within the range from 45 to 55.
When you insert a row, your formula will automatically adjusts to 57. Hence,
-1 row, your addends are now within 45 to 56.

I’m using the OFFSET function in an Excel gradebook to create easily printed grade sheets for each of my students. I’m wondering if there is any way to bring the cell formatting along with the offset. For example, I hightlight the scores of tasks that were submitted late in the “homework” sheet and am wanting those scores to also be highlighted when they are brought over to the “report” sheet using OFFSET. Thank you!

4 Add a CommandButton. Find it here : Adding a CommandButton from the Developer’s Ribbon
5 Double Click the Button then Copy and Paste the code in the sheet where your offset formulas are.(You may also refer to the link in number 4 on
where to place the codes.) Note: In copying below do not include the Event Procedure Name: Private Sub CommandButton1_Click() and End Sub. It is already provided after you double click.

Excellent coverage of a very confusing topic. I have one question. In the formula COUNT($B$79:OFFSET($B$86,-1,0,1,1)) what does the -1 mean
in the OFFSET portion. I assume it means the cell with last data before the total. You have used the -1 in several examples explaining the OFFSET function .

Hi Mynda,
I am really feel very obliged to you for sharing such wonderful information in excel. All tips are so well explained in simple language & I find it extermely useful for anyone however well he/she at excel.
Thanks a lot.
Dnyandeo

Hello Mynda,
You have a gift of simplifying complex formulas and presenting those to your readers in an effective way. Previously I made several failed attempts to understand some of the Excel’s functions now you helped me to learn how they work. Thank you for your time and effort to share your knowledge with us.

Mynda, I’m testing this function right now and I’m not sure if it’ll work in my case, but I’m truly grateful at you since I couldn’t find a much kinder explanation. This one of yours was very-very educational (I loved the treasure map and your sense of humour). Thanks for teaching us.

I just stumbled upon the index function… Seems to do the same as the offset function, but I guess there must be a difference that I can’t determine… If you could explain what’s the difference I’m sure most of us would be very grateful…

The OFFSET function simply returns a range of cells (it can be a single cell range). On it’s own it isn’t much use so it is typically used to return a dynamic range that is then referenced/nested in another function.

The INDEX function can also return a value or reference to a single cell or a range. The main difference is that with OFFSET you can return a range outside of your starting point by using minus values for the rows and cols arguments, whereas the INDEX can only return a cell or range of cells from within the array you specify.

I am an intermediate level business analyst, found your examples and description method outrageously amazing, never thought I would get this level of help on net. Thanks to your team for all the efforts.

It’s 2:57 in the AM and I am so frustrated, hence my reaching out to you.

I am working on a cash flow spreadsheet and my last missing link is to resolve the issue I am having with accurately showing payment of inventory purchases out 30, 60, 90, 120 days. I have successfully copied formulas for my receivables and was able to reflect actual revenues 120 days out, but having horrible time trying to do the same successfully with my cost of goods! Any chance you might be able to help?

For instance, this is the formula appearing which seems to be crashing after I punch in more than 60 days.

I need to move/offset it so I end up with 3 columns and 27 rows. The first column should contain A1,D1,G1,J1,M1,etc. The second column should contain B1,E1,H1,K1,N1,etc. The third column should contain C1,F1,I1,L1,O1,etc.

I think your row numbers in these two lines are incorrect. They should be one row larger (65 should be 66, 66 should be 67, etc.).

II. Working through the ‘Sum of Viewers’ formula, the reference cell for the OFFSET function is B65, i.e. the junction of the table.

III.We then use the MATCH function to find the row that Ben Ten is on from the range B66:B72, with row 66 being 1, row 67 being 2 etc. This result is then used to instruct the OFFSET function how many rows from B65 Ben Ten is on. In this case it is 2.

I need a formula that calculate the rows from total row for the calculation.
Ex :
I have data in rows B15:B20
the total row in B21, I need a formula that calculates how many rows from B20 to B15.
In other occurrence, I want to calculate B30:B33 by copying the formula in row B21

To give you the best answer I’d need to see how your data is laid out.

For example, I’m wondering are there any blank rows between your data you want to sum, are the groups of data B15:B20 and B30:B33 the same number of rows apart despite being different lengths.

The formula below counts the number or cells containing data between cell B15 and B20 and sums them, but without seeing how your data is laid out I can’t tell if it will be suitable or not.

=SUM(OFFSET(B20,-COUNTA(B15:B20),0):OFFSET(B21,-1,0))

I’m also thinking, if you’re going to copy the formula why don’t you just click on each cell you want the total in and enter the shortcut key for SUM which is ALT+= as this would be just as quick as copying and pasting a formula.

If you’d like to send me your example file you can do so by logging a ticket on the help desk.

Hello Mynda,
Thanks for color coding of each segment of the argument string. That really helped me digest the examples as they got progressively more complex…”fancy”. So true – on their own many functions don’t seem of much [practical] use.

Thanks for dissecting combined functions in order to show practical applications.

Excellent material, very brilliant delivery. First time user of your site. Just downloaded the excel blog file. Thanks a million times for your effort and time. I desire to be an awesome excel user. Not yet close but would work hard with you as my guide. Thank you once again.

My situation is a bit complex. I am an MBA student and doing my internship. I am preapring an Inventory Management file.

In one row I have my closing inventory (Say 25000 kgs of Sugar). In another row, I have my future weekly consumption of this material. Now I want to determine, the number of days, my stock will last based on my future weekly consumption. Lets say, my weekly consumption is 4120 for first week, 4230 for second week 5430 for the third week, 5400 for the fourth week, 5800 for the fifth week, 6400 for the sixth week and 7210 for the seventh week.

So is their any way we can put in a formulae that we can determine the number of days my inventory will alst based on my comsumption.

I tried using the offset function, but I guess we need to specify a range to use it. Is thier any way excel automatically counts the coloums that offset my balance.

Hi Mynda,
When I try to download the workbook at the top of the page I get some strange files but no zip containing excel file. Can you please have a look if something got corrupted? Thanks a lot.
Karine

The workbook is not zipped. It’s a .xlsx file. If you hover your mouse over the link you can see the file name ends in a .xlsx extension (usually in the bottom right or left of your browser window).

Some browsers assume files are zipped and change the file extension when you try to download them. Just make sure the file extension is a .xlsx file when you download it. You can simply type over the .zip extension with .xlsx to fix the problem while in the ‘file save as’ or similar dialog box.

Resources

Affiliate Program

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

100 Excel Tips & Tricks eBook - FREE

Almost There

Please enter your email address and click the button below to get instant access to the Excel Tips & Tricks.