How to Make Dynamic Chart From Static Chart

In this lesson, 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.

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 chart. It should be just basic statis chart which you will turn into dynamic chart.

Static Basic Chart

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

Use OFFSET function. Formulas are:

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

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

Define name year

Define name sales

Next go to chart. Right click on the chart and go to Select Data.

Select Chart Data

Edit your series. In 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 is 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.

See also  Chart with a single x-axis but two different ranges

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 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

There are second way to dynamic the chart in Excel. Dynamic means here that chart will expand when you will add new data. It’s useful when you have worksheets which are often updated.

You can use the table of data and the static chart from Example1. You must change the name which you define. It should be:

=OFFSET(Sheet1!$C$2;;;COUNTA(Sheet1!$C:$C)-1) for sales

=OFFSET(Sheet1!$B$2;;;COUNTA(Sheet1!$C:$C)-1) for years

Define name year expanding

Dynamic chart define name sales expanding

Now expanding the dynamic chart is ready. When you will add some data the chart will dynamically expand.

Expanding Dynamic Chart

Dynamic chart is one of the most impressive Excel charts which I know. It works really good and proves that you are Excel’s PRO.

Key Takeaways

  • Dynamic charts are charts that change when you change the scope of data.
  • There are two ways to create dynamic charts in Excel: by using the OFFSET function or by using the COUNTA function.
  • Dynamic charts are useful when you want to have charts that automatically update as you add new data.
See also  How to Make Gauss Normal Distribution Chart

FAQ

  • Q: What is the difference between a static chart and a dynamic chart?
  • A: A static chart is a chart that does not change when you change the scope of data. A dynamic chart changes as you add new data.
  • Q: How do I create a dynamic chart using the OFFSET function?
  • A: To create a dynamic chart using the OFFSET function, you first need to define two names: one for the year column and one for the sales column. Then, you need to use the OFFSET function to create a formula that returns the values from the year and sales columns. Finally, you need to use the Select Data dialog box to set the series name and values for the dynamic chart.
  • Q: How do I create a dynamic chart using the COUNTA function?
  • A: To create a dynamic chart using the COUNTA function, you first need to define two names: one for the year column and one for the sales column. Then, you need to use the COUNTA function to create a formula that returns the number of rows in the year and sales columns. Finally, you need to use the Select Data dialog box to set the series name and values for the dynamic chart.

You can download a free dynamic template from this link.