Dynamic Chart in Excel

What are Dynamic Charts in Excel?

A dynamic chart in excel is a special type of chart in excel which updates itself when the range of the chart is updated, in static charts when the range is updated the chart doesn’t update itself so in order to make a chart dynamic we need to make a range dynamic  or the source of the data, it can be done by naming the ranges from excel table or using formulas like offset function.

There are 2 methods to create a dynamic chart in excel

1. Create a Dynamic Chart Using Name Range
2. Create a Dynamic Chart Using Excel Tables

Now let us explain each of the methods in detail with an example

#1 How to Create a Dynamic Chart Using Name Range?

All right, let me explain you with an example. Below is the survey data for the need of the excel knowledge across professions. Below data holds the record of excel knowledge required and the salary range for those professions.

Now insert one simple column chart show the salary ranges.

Now if the profession list increases this chart cannot take that range automatically.

I have updated the other two professions with their salary range. This chart still taking the range from A2:A6.

Now we need to make this range dynamic. To make the range dynamic we need to define the name for this range of cells.

Follow the below steps to make the range dynamic.

• Step 1: Go to the formula tab and select the Name Manager.

• Step 2: Click on Name Manager in Excel and apply the formula as shown in the below image. This will create a dynamic range in excel for the Salary column.

• Step 3: Again click on Name Manager and apply the formula as shown in the below image. This will create a dynamic range in excel for the Profession column.

Now we have created two dynamic ranges in excel for our chart range. Salary_Range & Profession_Range are the two named ranges we have created.

• Step 4: Now all we need to do is create a column chart using these named ranges. Now go to Insert tab and select the column chart.

• Step 5: Click on the Column chart in excel and select a 2D clustered column chart. At this point in time, it will insert a blank chart.

• Step 5: Right-click on the data and select “Select Data”.

• Step 6: Click on Select Data option and it will open up the below box and click the Add button.

• Step 7: once you click on the Add button, it will ask you to select the series name and series values.

• Step 8: In the series name select Salary cell and in the series values filed mention the named range we have created for salary column i.e. Salary_Range.

Note: We cannot simply mention the range name rather we need to mention this along with the sheet name too i.e. =’Chart Sheet’!Salary_Range

Click on the OK button and it will open up the below box and click on Edit option.

• Step 9: Once you click on the Edit option it will open up the below box. It will ask you to mention the Axis Label Range.

• Step 10: for this range, again we need to give our second named range name.

Note: We cannot simply mention the range name rather we need to mention this along with the sheet name too i.e. =’Chart Sheet’!Profession_Range

• Step 11: Click on Ok and it will open up one more box and click OK there as well. Your chart should like below one.

• Step 12: Now again add those two professional data. The chart should extend automatically.

Wow! It is working. Sit back and relax. Gone are the days where we need to update the data as well as the chart range. Now we have a dynamic chart range to update the dynamic chart ranges automatically.

#2 How to Create Dynamic Chart Using Excel Tables

Follow the below steps to explore this option.

• Step 1: Select the data and press CTRL + T. This will create the table for you.

• Step 2: Once the table is created > select the data from A1:B6 > go to insert tab > insert column chart.

• Step 3: Now add those two professions to the list.

Things to Remember

• When created Named Ranges make sure, there are no blank values. Because the Offset function will not do the calculation accurately if there are any blank cells.
• While giving a reference in chart data first type the name and press F3 it will open up the entire defined name list.
• While entering the sheet name always start with a single quote ( ‘) and end with a single quote ( ‘ ). For example =’Chart Sheet’!

Recommended Articles

This has been a guide to Dynamic Chart in Excel. Here we discuss the two ways to create a dynamic chart in excel using name range and excel tables, along with examples and downloadable excel templates. You may also look at these useful functions in excel –

• Surface Chart in Excel
• Dynamic Tables in Excel
• Dynamic Named Range in Excel
• Pareto Chart in Excel