In this tutorial we’ll take a look at the OFFSET function, specifically the difference between using a single cell vs. a range of cells for the reference argument, and using it in conjunction with the SUM function.
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
Here I have a worksheet that has the twelve months along with twelve rows of data, and the totals for each row. I am going to use the SUM and OFFSET functions to selectively total up each row. The totals in column “O” are just to confirm the accuracy of my formula.
First, let’s look at what the OFFSET function is. The description says that OFFSET “Returns a reference to a range that is a given number of rows and columns from a given reference”.
The syntax for this function is:
=OFFSET(reference, rows, columns, [height}, [width])
Note that the last two arguments are optional.
So, how the OFFSET function works is you give it a reference or starting point, then define, either by entering in a number or via a formula, the number or rows down and columns over you want to go. The formula will then return the contents of that cell.
For example, if I enter this formula:
Excel will start at cell B1, go down 4 rows and over 3 columns, which will be cell E5, and return the contents of that cell, which is 40:
The optional arguments of height and width are when you want the result to be a range of cells. These allow you to define how high and how wide you want that range to be. This will become more evident in our upcoming example.
Although OFFSET can be useful on its own, it is often used in conjunction with other functions. In our example, we are going to use it with the SUM function to add up a range of cells, namely the row that we select. We are also going to look at the difference between having the reference argument be a single cell vs. a range of cells.
In cell B15 we can enter the row that we want to sum. In cell C15 we will enter the following formula:
…and we get the result of 327, which is the sum of the 12 months in row 4.
Let’s break down the arguments of the OFFSET formula:
Reference: B1 – this is our starting point
Rows: B15 – in B15 we entered a 4, that tells OFFSET to go down 4 rows
Columns: 1 – this tells OFFSET to go over 1 column.
So, starting in cell B1, if we go down 4 rows and over 1 column, we are not in cell C5.
[Height]: 1 – this tells OFFSET that we want the result to be 1 row high.
[Width]: 12 – this tells OFFSET that we want toe result to be 12 columns wide.
Therefore, the result of the OFFSET formula is: C5:N5, which contain the numbers 29, 2, 40, 14, 27, 33, 17, 37, 46, 49, 18, 15. With those being the values for the SUM function, we get the result fo 327.
An alternative to this structure is to use a range as our reference. Here, in cell C16 we’ll enter the following formula:
…and again we’ll get the result of 327.
Let’s break down the arguments of the OFFSET function for this formula:
Reference: C1:N1 – here we are defining a range of cells as the starting point rather than a single cell.
Rows: B15 – again, we are telling the OFFSET function to go down 4 rows.
Columns: 0 – since we started our range in cell C1, and column C is where the first value is, we don’t need to move over any columns.
We don’t need either of the optional arguments. By default, Excel assumes the row height (or column width) to be 1. Since our reference was a range that was 12 columns wide, the result of the OFFSET function will also be 12 columns wide, and the result will be the same as the previous formula:
So either way, we get the same result, whether using a single cell as a reference and defining the width as 12 columns, or using a range that is 12 columns wide and not needing to define the range for the result.
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!