Refer to a range dynamically (using OFFSET)

Instructions: You can type values or formulas into cells above but to access Excel functionality, or if it is easier to work directly in Excel, open the file by clicking on the Excel Icon at the bottom.

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

One of the key techniques of spreadsheet modelling is to have a range of inputs (each in a separate column) and then for the correct range/column to be picked up according to the scenario selected by the user. So the range of inputs to be picked up has to be dynamic, flexing according to the scenario chosen by the user. One of the most effective ways to do this is by use of the OFFSET function which is easily one of the most powerful and important functions in Excel.

In the Topic Example Workbook we have a set of sales figures for each month in the ‘Sales data’ tab and then in the ‘analysis’ tab we want to give the sum of sales for each employee for any given month. In cell A5 we have a [drop-down box] of months and when we select a month we want our SUMIF formulas to automatically pick up the correct range to sum from our sales table. This is achieved by combining the r SUMIF function with the OFFSET and MATCH functions.

The OFFSET function allows you to define a range, either as a specified number of rows/columns away from an existing range or by taking an existing cell/range and extending it by a specified number of rows/columns.

The syntax of the OFFSET function is =OFFSET(reference,rows,cols,height,width). The ‘reference’ is our base range, entered by selecting a cell/range of cells. The rows/cols parameters are used when we want to use the OFFSET function to return a range that is a number of rows/columns away from our base range. For example = OFFSET(A2:B2,1,0) will offset the range A2:B2 by 1 row and so will return the range A3:B3; = OFFSET(A2:B2,1,1) will offset it by one row and one column so will return the range B3:C3.

Alternatively (or as well) we can use the OFFSET function to take our base range and extend it by a number of rows or columns. To do this we use the height and width parameters of the OFFSET Function. The height parameter represents the number of rows we want our new range to have and the width parameter represents the number of columns we want to have our new range to have. For example = OFFSET(A2:B2,0,0,2,2) will take the range A2:B2 and starting from this base, will return a range that is 2 columns wide and 2 rows high, i.e. the range A2:B3.

Remember, though, that a normal Excel formula can’t return a range of values within a single cell so we have to tell Excel to aggregate the values in some way (e.g sum them or count them) and return a single result. So a valid formula in the OFFSET example of returning a range away from our base range could be =SUM(OFFSET(‘Sales Data’!$A$2:$A$11,,1)) which would return the sum of values in the range B1:B11 (i.e. the sum of values for Jan), in this case 5500.

Let’s now return to the formula we want in our analysis tab. We want to use a SUMIF formula to pick up sales (in the ‘Sales’ sheet) for each employee in the range A7:A11 of our Reporting sheet. So for example for January sales the formula needed in cell C3 would be =SUMIF(‘Sales Data’!$A$2:$A$11,Reporting!$A7,’Sales Data’!$B$2:$B$11) .This returns the sum of all sales numbers in the Range B2:B11 of the Sales Data where the corresponding value in the range A2:A11 is “Andrew”. Equally the Sales data for Andrew for February is =SUMIF(‘Sales Data’!$A$2:$A$11,Reporting!$A7,’Sales Data’!$C$2:$C$11) – exactly the same as the January formula but instead of looking in Column B of the Sales Data tab we need to look in Column C.

So for January, the Sales data we need to reference is $B$2:$B$11, for February it is $C$2:$C$11, for March it is $D$2:$D$11 and so on. So considering the range $A$2:$A$11 as our base range, in January we need to offset this by 1 column to return the range $B$2:$B$11, in Febuary we need to offset it by 2 columns ( to return $C$2:$C$11) and so on. So if we know how many columns to offset by for any given month then we can use this as part of an OFFSET function (the rows/columns parameters).

To identify this number of columns to offset by, we can look in row 1 of the Sales data tab for the month and find the column number on which it occurs. For that we can use the MATCH function-it can take a Lookup value and a row to look across and will return the column number on which that value occurs. So the formula =MATCH(“Jan”, Salesdata!1:1,0) returns 2 because within row 1 of the Sales Data tab the value “Jan” occurs in the second column (column B). Similarly =MATCH(“Feb”,’Sales Data’!1:1,0) returns 3 because in the Sales Data tab the value “Feb” occurs on the third column (Column C).

Building this into our SUMIF formula, for Jan we need to OFFSET the range ‘Sales Data’!$A$2:$A$11 by one column which is one less than the column number on which Jan occurs (row 2), so for our Columns offset parameter we need to use =MATCH(“Jan”, Salesdata!1:1,0) – 1. If we just have the MATCH function return the column number in Cell $H$3 our completed SUMIF formula combined with OFFSET and MATCH is therefore =SUMIF(‘Sales Data’!$A$2:$A$11,Reporting!$A7, OFFSET(‘Sales Data’!$A$2:$A$11,0,$H$3-1))

Another use of the OFFSET function is using the height/width parameters to give us a range of used cells on a sheet or within an area, which we can then reference for example as the source range for a Pivot Table or Chart, perhaps in cases where it isn’t practical to have our data in a Table.

With the syntax of the OFFSET function as =OFFSET(reference,rows,cols,height,width) the height parameter will need to be the number of used rows on the sheet/area and the width parameter will need to be the number of used columns on the sheet/area.

There isn’t a completely reliable formula which gives us the number of used rows or columns on a sheet (there is in VBA!) but provided there are no blanks within the data set in a particular row or column then one way to find it would be to count the number of Non-blank cells in a row/column using the COUNTA function. The COUNTA function takes a range of cells as its input and returns the number of non-blank cells in that range. For example in the Sales Data tab, =COUNTA(A:A) returns a value of 11 because there are 11 cells in the range A:A with something in them. Similarly =COUNTA(1:1) returns 13 as the data goes from columns A to M (columns 1 to 13). Therefore we want the height of our used range to be 11 and the width to be 13, with our base range as the first cell in the range ($A$1).

So our OFFSET function becomes =OFFSET(‘Sales Data’!$A$1,0,0,COUNTA(‘Sales Data’!$A:$A),COUNTA(‘Sales Data’!$1:$1)). This takes the cell A1 as a base range and extends it to a range that is 13 columns wide and 11 rows high returning the range $A$1:$M$11. If we added another value in cell A13 then this range would automatically reflect this and our formula would instead return the range $A$1:$M$12. Where the function wouldn’t work is if we one or more blank cells somewhere in the range $A$1:$A$12. In this case the range would only extend by the number of non-blank cells so wouldn’t extend far enough.

In order to use this dynamic used range formula we need to put it as a Named formula. In the Name Manager (under the Formulas tab in the Ribbon), create a new name and in the ‘Refers to’ field type or copy in the OFFSET formula (including the equals sign) which returns the used range. Whatever name we call this named formula we can then refer to as the source e.g. for a Pivot Table or chart based on this data. Also once we’ve defined a print area (by selecting a range of cells and then going to Page Layout -> Print Area -> Set Print Area), then Excel will create a named range called Print_Area. To avoid us having to change this each time new data is added, we can change what the Print_Area name refers to by having it as a dynamic used range formula instead.

In the Topic Example Workbook, populate the yellow cells in the Reporting worksheet by:

• Writing a MATCH function in cell H3 that calculates which column in the Sales Data tab the month name (In cell B1) occurs in.
• Writing a SUMIF Formula that calculates the total by Salesperson for Jan in cells B7:B11 of the Reporting tab, and the same for Feb in cells C7:C11
• In cells D7:D11 writing an SUMIF function that returns the sales for each salesperson for the month selected in cell B1, using an OFFSET function and referencing the MATCH function in cell H3.

A demonstration is shown in the video clip below. Resist the temptation to view the answers file below until you have tried this!

Once you have completed the ‘Try it Out’ exercise, the completed Excel file should look like this:

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

YouTube Video – The OFFSET function