How to use the Offset Function (Part III)

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.