An Excel Graph is a great way to communicate a company’s financial performance to stakeholders. A conventional Excel Graph has a hard time updating for source data changes; unlike other Excel functions such as formulas or cell formats that update seamlessly. The introduction of dynamic data ranges in an Excel Graph will solve this problem.

 

Creating dynamic data ranges in an Excel Graph

The application of dynamic ranges is similar to creating a dynamic name range for a drop-down list. The dynamic ranges will be built from a defined name, which uses the Name Manager in the Defined Name function.

The following financial model and YouTube demonstrates the value of creating dynamic data ranges in an Excel Graph:

Strategize Financial Modelling – Dynamic data ranges in an Excel Graph.xls

There are two facets to the formula:

 

❶Name Manager

Create a short, logical naming convention for the dynamic name ranges in an Excel Graph. In the above example, the defined names in Graph 1 for Net Income employ the following defined names:

Net Income (y-axis): yAxisG1

Time Series (x-axis): xAxisG1

 

❷OFFSET function

This formula is based on the first graph example in the above YouTube video for an Excel Graph. The “Reference” cell is the first cell in the data range. Zero out both the “Rows” and “Col” parameters, as the formula does not want to offset any rows or columns. The “Height” is the number of rows, which needs to be “1”, given the source data array is horizontal and located across the sheet.

Net Income (y-axis): =OFFSET(Dashboard_Base_BO!$AB$9,0,0,1,

Time Series (x-axis): =OFFSET(Dashboard_Base_BO!$AB$8,0,0,1,

 

❸COUNTA function

The “Width” parameter states the number of columns; hence the following COUNTA function is applied to successfully create a dynamic range of y or x-axis values:

Net Income (y-axis): COUNTA(Dashboard_Base_BO!$AB$9:$AZ$9))

Time Series (x-axis): COUNTA(Dashboard_Base_BO!$AB$8:$AZ$8))

 

In summary, the defined names are as follows:

Net Income (y-axis) – yAxisG1:

=OFFSET(Dashboard_Base_BO!$AB$9,0,0,1,COUNTA(Dashboard_Base_BO!$AB$9:$AZ$9))

Time Series (x-axis) – xAxisG1:

=OFFSET(Dashboard_Base_BO!$AB$8,0,0,1,COUNTA(Dashboard_Base_BO!$AB$8:$AZ$8))

It means these ranges will be able to flex across columns AB to AZ; whenever there are values to plot into an Excel Graph.

 

Edit or Add Legend Entries (Series) in an Excel Graph

Right-click on the Excel Graph, and select “Select Data”. On the “Select Data Source”, under the “Legend Entries (Series)” (y-axis values) and the “Horizontal (Category) Axis Labels” (x-axis), select “Edit” and apply the specific defined name under “Series values” (y-axis values) and “Axis label range” (x-axis values).

 

Practical Business Modelling benefits of dynamic Excel Graph ranges

As the above video and following snapshot illustrate. Introducing dynamic data ranges in an executive dashboard will guarantee a graph’s validity into the future, whereby the revision or update of financial outputs will seamlessly update in an Excel Graph.