In this part, we will be sharing with you how to use the offset function to create dynamic ranges.

Before you could do that, you need to know 2 more functions from Excel. One is the COUNTA function and the other is NAMED RANGES.

COUNTA function counts the number of non blank cells in a pre-defined range. For example, if within a selected range from A1 to A100, the first 20 rows contains data, then counta will return 20 indicating that there are 20 rows that are non-blank cells. This COUNTA function will replace the height of the offset as shown =OFFSET(A1,0,0,COUNTA($A$1:$A$100),1). It makes the range dynamic and varies according to length of the list. In this case, we will be able to find out how many datasets are there in a chart data series.

After defining the formula that will vary according to the length of the list, you need to give it a name. Do this from the menu bar insert, name, define to create the range name. See the diagram below:

In the dialog box that follows, inout the name of the range (in this case called “chart_range”) and input the formula “=OFFSET(A1,0,0,COUNTA($A$1:$A$100),1)” in the refers to box.

Then create the chart defining the range either as a x-axis or y-axis.

Once that is done, you have a dynamic chart that changes as more datasets are added or removed.

Leave a Reply