In Excel, it is often the case that you will have rows of data with similar characteristics, which need to be graphed but you do not want to create a graph for each row. Via a list box and the use of the OFFSET function, one graph can be used to illustrate each row of data in turn with the user choosing the row from the list.

Imagine you have sales data from a number of different brands. The data are monthly sales volumes measured in kilo-units for the year and monthly sales totals in a common currency. You need a graph which displays both data sets in the same graph, with two different Y axes for any one brand, of which there may be anything up to, say, one hundred.

Let’s say your volume data are on a worksheet called Volumes in rows 2 to 100 with column headings in row 1. Column A contains the name of the brand and columns B to M the volume figures. The same pattern occurs in the Value Sheet for sales by value. In each case we add four Quarter totals in columns N to Q.

In the Graph worksheet , we will create our graph and create two rows of data which will contain an offset so we can move to different rows, depending on the choice made in the list box, which refers to the column containing the brands. Before defining the offset formula, we need to insert a list-box which will reference the brands in column A of either the Volumes or Values sheet. This is the input range and the cell link can be any free cell such as J3, which will then be hidden behind the list-box.

The OFFSET function for the Volumes row is as follows:

=OFFSET(Volumes!A$2,-1+$J$3,0)

and for the Values row:

=OFFSET(Value!A$2,-1+$J$3,0)

The resulting graph and list box look like this:

Advertisements