Dynamic Chart in Excel

Table of Contents

• Dynamic Charts in Excel
• How to Create a Dynamic Chart in Excel using Name Range?
• How to Create Dynamic Chart Using Excel Tables

What are Dynamic Charts in Excel?

There are many built-in chart styles available to present the report in a better way. Creating dynamic range chart in excel needs some of the formulas to make the data range dynamic.

A dynamic chart in excel is nothing but as the range of the dynamic chart in excel increase, the dynamic chart in excel also should increase automatically.

Probably at the end of this article, you will say goodbye to normal chart and adopt this dynamic ranges chart to all your Excel charts. Updating the chart manually is a boring and less interesting task I have ever come across – whether it is updating the chart or deletion of the chart, you have to do it manually.

How to Create a Dynamic Chart in Excel Using Name Range?

Dynamic Chart in Excel is very simple and easy to use. Let understand the working of with some examples.

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 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 and apply the formula as shown in the below image. This will create the dynamic range in excel for Salary column.

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

Now we have created two dynamic range 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 column chart.

Step 5: Click on the Column chart and select 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 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 mention this along with the sheet name too i.e. =’Chart Sheet’!Salary_Range

 

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

Step 9: Once you click on 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 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 the below one.

Step 12: Now again add those two profession 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 the chart range. Now we have a dynamic chart range to update the dynamic chart in excel ranges automatically.

How to Create Dynamic Chart Using Excel Tables

We can create a dynamic range in excel tables and it probably easier than the first one. Follow below steps to explore that 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 About Dynamic Chart in Excel

• When created Named Ranges make sure, there are no blank values. Because 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’!

You can download this Dynamic Chart in Excel template here – Dynamic Chart Excel Template

Recommended Articles

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

• Dynamic Tables in Excel
• Dynamic Named Range in Excel
• Excel Auto Formatting
• Using the formula to Remove Blank Rows in Excel
• Insert Hyperlinks
• Excel Tables
• Pareto Chart Excel
• Bubble Chart Excel
• Waterfall Chart Excel
• Clustered Bar Chart