We also took advantage of our named range as a definition for a series on a chart, but you can also use named ranges as parameters in formulas.Defining Series Values in a Charts and Graphs: Pretty straightforward Excel function.The result of this setup is a chain reaction where you dump data into a worksheet, your table keeps itself updated with sumifs formulas and your graph automatically keeps itself updated after that.You can experiment with this functionality by clearing the contents of cells in the Data For Our Graph worksheet and replacing it with the data from the Additional Data worksheet.Suppose you are keeping track of the maximum temperature for each day in April.You have entered the first two dates for April in Column A, which contains the field name Date in A1.This means that you can dump data in one location and then have a table update itself automatically, which in turn updates a graph automatically.Here’s an example of what our formulas look like in this automated table.

Also has many useful derivatives such as iferror, ifnumber, etc.

Example from the named range “Chart_Axis” =OFFSET(‘Automated Graph’! $B:$M)) We will create a named range for our graphs axis and each of its series.

In this case that means 3 ranges for the axis, spend, and revenue values.

Our sumifs simply keeps track of the sum of each metric for each month (this process is explained in our appendix for those of you who are new to this formula). B) We nest this in a simple if formula to prevent our sumifs from returning a zero, and replacing it with an empty text string instead.

Formula (abridged) from cell B3 SUMIFS(‘Data For Our Graph’! We then use named ranges that point towards our automatically updated tables.

Also has many useful derivatives such as iferror, ifnumber, etc.

Example from the named range “Chart_Axis” =OFFSET(‘Automated Graph’! $B:$M)) We will create a named range for our graphs axis and each of its series.

In this case that means 3 ranges for the axis, spend, and revenue values.

Our sumifs simply keeps track of the sum of each metric for each month (this process is explained in our appendix for those of you who are new to this formula). B) We nest this in a simple if formula to prevent our sumifs from returning a zero, and replacing it with an empty text string instead.

Formula (abridged) from cell B3 SUMIFS(‘Data For Our Graph’! We then use named ranges that point towards our automatically updated tables.

Help users increase productivity by automatically signing up for Tech Republic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.