Durjoy Paul
When you work on a data chart, you may need to change your data in some cases. For a static chart, you must create a new one from the beginning, which is very time-consuming. In that case, you need to utilize a dynamic chart which will change your chart according to your new data input. You may use a dynamic Excel chart when you want to get a data chart for some specific condition. This article will mainly focus on how to dynamically change Excel chart data in an effective way.
Download Practice Workbook
Download the practice workbook.
3 Effective Methods to Dynamically Change Excel Chart Data
To dynamically change Excel chart data, we have found three different methods including Excel Named range, Excel Table, and INDEX function. All the methods are equally useful to dynamically change Excel chart data.
1. Using INDEX Function
At first, we will utilize the INDEX function to dynamically change Excel chart data. Here, we basically create a dynamic chart in Excel where we can see the preview of several product sales amounts in the corresponding countries. To show the process completely, we take a dataset of several countries and five different product sales amounts.
Follow the steps carefully.
Steps
- At first, go to the Developer tab in the ribbon.
- From the Controls group, select the Insert drop-down option.
- Then, from the Form Controls section, select Combo Box.
- After that, in any cell draw a box. It will shape up like the following one. See the screenshot.
- Then, to modify it, right-click on it.
- A Context Menu will appear. From there, select the Format Control option.
- After that, the Format Object dialog box will appear.
- In the Input range section, select the countries range because we want to plot a chart for a specific country.
- Then, select a cell link. It will change along with the combo box drop-down option.
- Finally, click on OK.
- Now, click on the drop-down option of the combo box.
- You will find out all the countries’ names from your dataset.
- Select any country and see the cell link will also change.
- Here, our first country is the United States. So, if you select it, the cell link will show 1 as the first country.
- If you select France from the drop-down option, the Cell link will show 2.
- After that, we want to utilize this cell link and the INDEX function to create a dynamic Excel chart.
- First, create some headers for the dynamic chart.
- Then, select cell B15.
- Write down the following formula in the formula box.
=INDEX(B5:B10,$C$12)
- Press Enter to apply the formula.
- Then, drag the Fill Handle icon right up to cell G15.
- Now, change the country from the drop-down option and select France.
- Then, look at the dynamic table. All the values of that table change and shift to France and its corresponding products sales amount.
- Select the range of cells B14 to G15.
- Then, go to the Insert tab in the ribbon.
- From the Charts group, select Recommended Charts option.
- After that, the Insert Chart dialog box will appear.
- From there, select the Clustered Column option.
- Finally, click on OK.
- As a result, we get a dynamic chart.
- Then, to change the Chart Style, select the brush sign.
- After that, select your preferred style.
- Finally, we get the following result. See the screenshot.
- As it is a dynamic chart, you can alter the drop-down option and select any country.
- The chart will represent that country.
- Here, we select Spain from the drop-down option. See the result in the following screenshot.
Read More: How to Create Dynamic Excel Charts with Drop-Down List (3 Ways)
2. Use of Excel Table
Our next method is basically using the Excel table. Here, we create a table using our dataset. After that, create a chart that will be dynamic. If you add more value to your Excel table. It will automatically show in the chart. It makes the whole chart dynamic. To show this method, we take a dataset that includes several countries and their product sales amounts.
Steps
- At first, select the range of cells B4 to E10.
- Then, go to the Insert tab in the ribbon.
- From the Tables group, select the Table option.
- A Create Table dialog box will appear.
- Then, select the range of cells.
- Finally, click on OK.
- It will convert our dataset into a table.
- Then, select any cell in the table.
- After that, go to the Insert tab in the ribbon.
- From the Charts group, select Recommended Charts option.
- After that, the Insert Chart dialog box will appear.
- From there, select the Clustered Column option.
- Finally, click on OK.
- It will give us a dynamic chart. See the screenshot.
- Then, select the Brush sign to change the Chart Style.
- After that, select any preferred style.
- As a result, we find out the following result. See the screenshot.
- As it is a dynamic chart, if you any data in your dataset or include or exclude any data, it will be updated in the dynamic chart automatically.
- Now, include a new country China and their product sales amounts in your previous dataset.
- Then, if you look at the dynamic chart we created previously, new data will be updated at the same time you enter them in the table.
Read More: How to Create Dynamic Charts in Excel Using Data Filters (3 Easy Ways)
3. Applying Name Manager
Our final method is based on using a name manager. In this method, we take a dataset. After that, define a name for each column using the name manager. Then, create the chart which would be a dynamic chart. To explain the process, we take a dataset that includes several countries and their product sales amounts.
Follow the steps carefully.
Steps
- At first, we create a dynamic named range using the OFFSET and COUNTA functions.
- We have four columns in our dataset. We want to define them specifically.
- Go to the Formulas tab in the ribbon.
- Then, select Name Manager from the Defined Names group.
- Then, the Name Manager dialog box will appear.
- Click on New.
- Then, the Edit Name dialog will pop up.
- Set any preferred name in the Name section.
- In the Refers to section, write down the following formula.
=OFFSET('Using Named Manager'!$C$5,0,0,COUNTA('Using Named Manager'!$C:$C)-1,1)- Then, click on OK.
- It will appear in the Name Manager dialog box.
- Then, click on New in the Name Manager dialog box.
- As a result, the Edit Name dialog will pop up.
- Set any preferred name in the Name section.
- In the Refers to section, write down the following formula.
=OFFSET('Using Named Manager'!$D$5,0,0,COUNTA('Using Named Manager'!$D:$D)-1,1)- Then, click on OK.
- It will appear in the Name Manager dialog box.
- Then, click on New in the Name Manager dialog box.
- As a result, the Edit Name dialog will pop up.
- Set any preferred name in the Name section.
- In the Refers to section, write down the following formula.
=OFFSET('Using Named Manager'!$E$5,0,0,COUNTA('Using Named Manager'!$E:$E)-1,1)- Then, click on OK.
- It will appear in the Name Manager dialog box.
- Then, click on New in the Name Manager section.
- As a result, the Edit Name dialog will pop up.
- Set any preferred name in the Name section.
- In the Refers to section, write down the following formula.
=OFFSET('Using Named Manager'!$B$5,0,0,COUNTA('Using Named Manager'!$B:$B)-1,1)- Then, click on OK.
- It will appear in the Name Manager section.
🔎 Breakdown of the Formula
OFFSET(‘Using Named Manager’!$B$5,0,0,COUNTA(‘Using Named Manager’!$B:$B)-1,1)
At first, the COUNTA function counts the total number of cells with a value in column B. Here, -1 denotes the total number of cells excluding the heading. This count value will then use in the OFFSET function as height to refer to a range. The OFFSET function returns a specified number of rows and columns from a range of cells.
- Next, we need to focus on creating a dynamic chart.
- At first, go to the Insert tab in the ribbon.
- From the Charts group, select the Column chart option.
- Then, from the 2-D Column, select the first chart option.
- A blank sheet will appear.
- Then, right-click on it. A Context Menu will appear.
- From there, select the Select Data option.
- A Select Data Source will appear.
- Then, in the Legend Entries (Series) section, click on Add to take new series.
- an Edit Series will occur.
- In the Series values Section, write down the following
='Using Named Manager'!Product1- Finally, click on OK.
- It will create Series1 with the Product1 named range.
- Do the same for Product2 and Product3 named ranges and create Series2 and Series3.
- Then, In the Horizontal Axis Labels section, click on Edit.
- As we want countries’ names in the horizontal axis labels, we use the Country named range.
- Then, write down the following in the Axis label range.
='Using Named Manager'!Country- Finally, click on OK.
- In the Select Data Source dialog box, click on OK to finally create the dynamic chart.
- There we have our required dynamic chart.
- Then, select the Brush sign on the right side of the chart to change the Chart Style.
- Select any of your preferences.
- This is our final modified version of the dynamic chart. See the screenshot.
- As it is a dynamic chart, if you any data in your dataset or include or exclude any data, it will be updated in the dynamic chart automatically.
- Now, include a new country China and their product sales amounts in your previous dataset.
- Then, if you look at the dynamic chart we created previously, new data will be updated at the same time you enter them in the table.
Read More: How to Use Dynamic Named Range in an Excel Chart (A Complete Guide)
Things to Remember
- While using the Name Manager method, you need to add a worksheet name along with it.
- Need to be careful while using the OFFSET function in the named range.
- While using the INDEX function, you need to handle the cell link carefully.
Conclusion
We have shown three different methods to dynamically change Excel chart data. Here, we discussed step-by-step procedures of how to create this and utilize the dynamic charts perfectly. We want to create a proper overview of how to dynamically change Excel chart data. I hope we have done this perfectly and provide you with an informative article. If you have any questions, feel free to ask in the comment box. Don’t forget to visit our ExcelDemy page.
