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.

First here’s the technical explanation for the OFFSET function.

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

The Excel 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 for the OFFSET function 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 down the OFFSET function components:

=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 about OFFSET

• 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 OFFSET in this example 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 OFFSET will return a range that is the same height and width as the reference.

OFFSET Function Examples

Practical Uses for Excel’s OFFSET Function

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

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 rows 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 formulas, on its own OFFSET isn’t much use, but team it up with other formulas and it’s a winner.

How do you use OFFSET? Share your knowlege in the comments below.

Share the knowlege with your friends and colleagues. Click the icons below for Twitter, Facebook, Stumbleupon and many more.

Tagged as: lookup