Use OFFSET Function for Dynamic Referencing
Excel has a powerful function called OFFSET. It allows dynamic referencing without using visual basic modules or macros to do the work. Please the below example. Please note that no VB module or macro is used. The trick is to use the OFFSET function.
File to download: Excel Dynamic Referencing.xlsx
Related articles: Data Referencing, VLOOKUP and HLOOKUP functions
For the top picture, I only have data up to May, so the chart only shows January to May.
In the bottom picture, I have data up to August, the chart automatically expands itself to show the data from January to August.
Microsoft’s web site (https://office.microsoft.com/en-us/excel-help/offset-function-HP010342739.aspx) describe the OFFSET function in Excel as below :
Description
Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned.
Syntax
OFFSET(reference, rows, cols, [height], [width])
The OFFSET function syntax has the following arguments:
Reference Required. The reference from which you want to base the offset. Reference must refer to a cell or range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value.
Rows Required. The number of rows, up or down, that you want the upper-left cell to refer to. Using 5 as the rows argument specifies that the upper-left cell in the reference is five rows below reference. Rows can be positive (which means below the starting reference) or negative (which means above the starting reference).
Cols Required. The number of columns, to the left or right, that you want the upper-left cell of the result to refer to. Using 5 as the cols argument specifies that the upper-left cell in the reference is five columns to the right of reference. Cols can be positive (which means to the right of the starting reference) or negative (which means to the left of the starting reference).
Height Optional. The height, in number of rows, that you want the returned reference to be. Height must be a positive number.
Width Optional. The width, in number of columns, that you want the returned reference to be. Width must be a positive number.
To use the OFFSET function for the dynamic referencing, the 2 optional parameters (Height and Width) are the keys. The Height and Width tells you the scope of the values that you want to include in the referencing.
For the above example of the top picture, we want to use this formula:
=OFFSET(Chart!$B$4,0,0,1,5)
Reference = Chart!$B$4 (I put the chart on the “Chart” worksheet). $B$4 is the cell with January revenue data which is the first data on the row)
Rows = 0 (indicate to referencing data from the same row)
Cols = 0 (indicate to referencing data from the same column)
Height = 1 (indicate to referencing data from the same column)
Width = 5 (indicate to referencing data from the first cell (B4) the fifth cell (F4) for data from January to May)
For the above example of the bottom picture, we want to use this formula:
=OFFSET(Chart!$B$4,0,0,1,8)
Width = 5 (indicate to referencing data from the first cell (B4) the fifth cell (I4) for data from January to August)
So you can see that the only difference of formula in Figure 1 and 2 is the “Width” reference. I can automate that by using a COUNT function. The COUNT function will return the number of the data present in the row, including zeros but not empty spaces. The final formula will be
=OFFSET(Chart!$B$4,0,0,1,COUNT(Chart!$B$4:$M$4))
Now I need to incorporate the OFFSET function into the chart. First I define the data range by name and let’s call it “ChartDataRange”. (Formulas → Name Manager)
Click “New …” button, put in the “ChartDataRange” in the “Name” box and copy our OFFSET formula to the “Refers to:” box. Click “OK” button to accept.
Now you should be able to see it in the “Name Manager” screen. Click “Close” button to close the pop-up screen.
You can now insert an Area chart. First highlight the data (from cell A3 to M4), then click Insert → Area.
Then modify the chart data by right-clicking the chart and select the “Select Data … “ command.
Click the “Edit” button and edit the “Series Values”
Additional usage for the OFFSET function
If you type the formula (e.g. =OFFSET(Chart!$B$4,0,0,1,8)) directly into a cell of the Excel worksheet, you will get a #VALUE! error. The reason is that the formula only specifies a range of cell. You need to add additional function to tell Excel what you want to do with the range. For example, you can use “SUM”, “AVERAGE”, “COUNT” function in addition to the OFFSET function. Please see the below examples:
=SUM(OFFSET(Chart!$B$4,0,0,1,8))
=AVERAGE(OFFSET(Chart!$B$4,0,0,1,8))
This flexibility allows us to expand the use of the OFFSET function for dynamic referencing for reporting purposes. The below example shows how the use of OFFSET function calculates the YTD total and average, based on the selection of month by the user.
