Charting Data With Formula Links In Excel By David Hager

You might have a need to chart data that is formula linked from its original source. For example, the data is generated in Columns A (Date) & B (Data), but you want to chart the data from Columns U & V. So, you would put this formula in cell U1 (=A1) copy it to Column V and fill down to whatever # of rows you needed. As the data is generated in A & B, you would then want to chart the data from U & V. In the following Figure is an example what U & V might look like.


Notice what the columns look like where no information has been added at the data source. The column containing the dates return at the bottom 01/00/1900, which is 0 with a date format. The column containing the data return at the bottom 0’s. An attempt to chart this data as is shown in the following Figure. = SERIES(Control!$V$1,Control!$U$2:$U$39,Control!$V$2:$V$39,1)


Clearly, the chart does not produce the desired effect. A method of charting just the “good” data is needed. In order to identify the end of the good data, this defined name formula looks at column V and finds the 1st position of a 0.

EndOfData= MATCH(0,Control!$V:$V,0)-2

Actually, the formula for doing that would exclude the -2, but it is needed for sizing the subsequent OFFSET formulas.

DateRange= OFFSET(Control!$U:$U,1,,EndOfData,)

Data Range= OFFSET(Control!$V:$V,1,,EndOfData,)

So, the defined names formulas can now be used in the chart series formula.


Note that the defined names are at the workbook level. The desired chart is shown below.


Hope this helps!


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s