Dynamic chart

Example 1 Dynamic chart depended from value in cell

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

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

Next go to Ribbon > Formulas > Define name. Define 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

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

Edit your series. In there is only one series – Sales but you can have more.

Series name is: =Sheet1!$C$1

Sheet1 is the name of my sheet

C1 is the cell where I have my Sales series

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

Dynamic chart is the name of my spreadsheet

Sales is the name which I defined

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.

There is an example for 13 years.

The is an example for only 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.

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

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

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.