Why would you use the OFFSET function in Excel, and how do you use the OFFSET function in
Excel? A very common reason why people use the OFFSET
function in Excel, is having standardized reporting with changing datasets. For example,
the two charts that you see in this Excel workbook represent the number of monthly views
on my Finance Storyteller YouTube channel on the left, and the number of quarterly views
on the right. Using the dropdown box at the top, I can switch between the years: 2017,
2018, 2019, 2020, and future years once that data is available. The views data and month
labels for the blue line on the left always get pulled from cells K21 through L32, whatever
year I select. The views data and quarter labels for the blue bars on the right always
get pulled from cells M21 through N24, whatever year I select. You could do the same with
your charts on revenue, expenses, margins, headcount, or whatever the metric is that
you are tracking. The reporting is standardized and stays in the same format. I just want
to add new rows and new columns to the dataset when data becomes available for new time periods.
On a monthly basis, I just add the latest results, without having to update any formulas.
That’s what the OFFSET function in Excel is doing for us: OFFSET is used here as the
linking pin between dataset and charts. Let’s hide the charts for a moment, and
show you how the OFFSET function works. If we change the year in cell F1, then the data
in columns L and N is changing. The original dataset, which is in cells A20 through I32,
does not change. The OFFSET function doesn't actually move any cells; it just returns a
reference. The central cell that makes things happen is L20, which is a calculation of the
offset in number of years versus the first year in the table. If we go to Formulas – Trace
Dependents, we see that L20 impacts each of the months in cells L21 through L32, and each
of the quarters in cells N21 through N24. If the year is 2016, then the offset is zero,
and the data in column L is the same as in column B.
If the year is 2017, then the offset is one, and the data in column L is the same as in
column C. If the year is 2018, then the offset is two,
and the data in column L is the same as in column D.
If the year is 2019, then the offset is three, and the data in column L is the same as in
column E. Let’s get into the detail of the function
arguments of the OFFSET function. Let’s review those with the year set to 2017. Select
cell L21, go to the Home tab – Functions – More Functions, and review the function
arguments in the dialog box. You use OFFSET to find certain data for you, so you need
to tell the function where to go for that data. The first argument is called Reference.
This is like defining a “base station” for the expedition, the starting point. I
took the top left cell of the table: B21. I could also have taken a range of cells,
like B21:I21. This would have given me the same result, as Excel would take the left-most
cell out of that range as the starting point. Next argument is Rows. How many rows, up or
down, would you like to go from starting point B21? Rows can be positive (which means below
the starting reference) or negative (which means above the starting reference). In this
OFFSET function, zero, we want to stay on the same row, which is row 21. Next is Columns.
How many columns, left or right, would you like to go from starting point B21? Columns
can be positive (which means to the right of the starting reference) or negative (which
means to the left of the starting reference). Here I didn’t input a fixed number of columns,
I made this dynamic by referring to cell L20, which in turn depends on the year selected
from the dropdown box. I have locked column L and row 20 in this function argument with
$ signs, as we don’t want this column and row reference to change when we copy the cell.
Then there are two optional arguments: height and width. How high, in number of rows, would
you like the returned reference to be. How wide, in number of columns, would you like
the returned reference to be. We go with 1 and 1, the size of a single cell. This formula
I have then copied down the column for each of the months, and I added a sum total at
the bottom. Could I use a different starting point somewhere
else in the table, and get the same result returned? Most certainly. Let’s go back
to L21. We can start our offset from cell I32, move up 11 rows by typing -11, and move
left 6 columns by typing -6. This returns the same result as before (which is the number
of views in cell C21), but the formula is now no longer dynamic as it does not depend
on cell L20. Let’s undo that change. Two very common error messages that can occur
when using the OFFSET function in Excel are #REF! and #VALUE! If I ask the OFFSET function
to start at cell B21, and then move two columns to the left (I input -2), then the offset
is outside the range of the worksheet, creating the #REF! error. Asking the OFFSET function
to return for example a result of 2 rows in height and 2 columns in width into one single
cell, creates the #VALUE! error. The OFFSET function can be used in combination
with other functions. In fact, it can be used with any function expecting a reference argument.
For example, in column N, I have nested the OFFSET function within a SUM function. In
order to get quarterly numbers, I need to sum three months. This number over here is
the sum of these numbers over here. And this number over here, is the sum of these numbers.
The OFFSET function that is nested here is very similar to the OFFSET function that we
saw before. It uses cell B21 as the reference, the starting point. Then it moves zero rows,
in other words stays in the same row. It takes a look at cell L20 to find out how many columns
to move to the right. It has a height of three, in other words return us the cell you find
and the two cells below it. It has a width of one, just the one column. For Q2, start
three rows lower, in April. For Q3, use July as the reference. For Q4, use October. As
a check, I put a sum total for the quarters, which should give the same number as the sum
total for the months. So that’s the OFFSET function nested within another function, in
this case SUM. We can also nest other functions within the
OFFSET function. Let’s take a look at G35. The function arguments dialog box shows a
preview on the right. The reference, or starting point, is cell G20. The row argument has a
nested COUNTA function inside it. COUNTA gives you the number of cells in a range that are
not empty. What we are asking Excel here is to look at cells G21 through G32, which is
the 12 months in this year, and find out how many rows out of these 12 have data. The column
holds data from January through July, so that’s 7 months. Dear OFFSET function, please start
at G20, move down 7 rows, stay in the same column, and return the results of just the
one cell that you find. In this way, you can use the OFFSET function in Excel to always
give you the latest results for a column. If we add a new value to the dataset, then
G35 will display that value, as the number of cells in the range that are not empty has
now increased by one. Bonus tip. Let’s unhide the charts and zoom
out a little. How is it possible to have that chart title change (the year displayed, and
the number of views for that year displayed) every time we change the year selection in
the dropdown box? There is no possibility to insert a direct formula in the chart title,
but you can link to a cell that has a formula. The titles of both charts pick up their data
from cell L18. This cell simply combines the year in cell F1 with the word “views”
and the sum of the total year views in cell L33.