How to Make Dynamic Chart From Static Chart

6Nov

How to Make Dynamic Chart From Static Chart

In this charting tutorial, you will learn how to create dynamic chart in Excel. There are useful when you want to have charts which change when you change the scope of data.

People who can make dynamic charts in Excel are truly Excel’s experts. Become one of them and learn how to create dynamic charts.

There are two ways to create dynamic charts in Excel.

Table of Contents

Example 1 Dynamic chart depended from value in cell

First, prepare table of data. In my example there are values of sales.

Dynamic Chart Table Data

Create a chart. It should be just basic static chart which you will turn into dynamic chart.

Static Basic Chart

Next, go to Ribbon > Formulas > Define name. Define B and C column as Year and Sales.

For the “Year” data (assuming it starts in B2), use the formula: =OFFSET(Sheet1!$B$2,0,0,Sheet1!$A$1,1) for Year

Define name year

For the “Sales” data (starting in C2), use =OFFSET(Sheet1!$C$2,0,0,Sheet1!$A$1,1)

Define name sales

Cell A1 will control the number of rows included in the chart. Then, right-click the chart, select Select Data.

Select Chart Data

Edit the series. If there is only one serie – Sales but you can have more.

Select Data Source

Series name is: =Sheet1!$C$1

Sheet1 is the name of my sheet.

C1 is the cell where I have my Sales serie.

Series values are: =‘Dynamic chart.xlsx’!Sales

Dynamic chart is the name of my spreadsheet.

Sales is the name which I defined.

Edit Series

Now, the chart becomes dynamic. You created dynamic chart which works really easy way.

Just write into A1 cell value of years you want to see on the chart.

There is an example for 13 years.

Sales 13 years

The is an example for only 6 years.

Sales 6 years

As you see chart changes. On the chart there are only as many years as you wrote in A1 cell before.

You can also start from the newest year. Just change formulas from:

=OFFSET(Sheet1!$B$2,0,Sheet1!$A$1) for Year

=OFFSET(Sheet1!$C$2,0,Sheet1!$A$1) for Sales

to:

=OFFSET(Sheet1!$B$14,0,Sheet1!$A$1) for Year

=OFFSET(Sheet1!$C$14,0,Sheet1!$A$1) for Sales

Now, write some values into A1 cell with minus sign. That’s how it looks like for 6 years and 8 years.

Sales -6 years

Sales -8 years

Chart changes really good. Isn’t it amazing?

Example 2 Dynamic chart which expanding scope

Another way to create a dynamic chart is to have it automatically expand its data range when new data is added to the source table. This is particularly useful for regularly updated worksheets. Using the same initial chart setup, redefine the named ranges using the COUNTA function within the OFFSET formula. For Sales, use =OFFSET(Sheet1!$C$2,COUNTA(Sheet1!$C:$C)-1)

Define name year expanding
For Year, use =OFFSET(Sheet1!$B$2,COUNTA(Sheet1!$C:$C)-1)

The COUNTA function dynamically counts the number of entries in the specified column, automatically adjusting the named range and, consequently, the chart’s data source, as new data is entered.

Dynamic chart define name sales expanding