Dynamic Chart in Excel (Table of Contents)
Dynamic Chart in Excel
Dynamic Chart in excel automatically gets updated whenever we insert a new value in the selected table. To create a dynamic chart, first, we need to create a dynamic range in excel. For this, we need to change the data into Table format from the Insert menu tab in the first step. By that created table will automatically change to the format we need, and the created chart using that table will be a Dynamic chart, and the chart created by using such type of data will be updated with the format that we need. We need to delete every previously used data if we want to keep fix set of data into Charts.
The two main methods used to prepare a dynamic chart are as follows:
- By Using Excel Table
- Using Named Range
How to Create a Dynamic Chart in Excel?
Dynamic Chart in Excel is very simple and easy to create. Let’s understand the working of Dynamic Chart in Excel by Some Examples.
Example #1 – By Using Excel Table
This is one of the easiest methods to make the dynamic chart in excel, which is available in the excel versions of 2007 and beyond. The basic steps to be followed are:
- Create a table in Excel by selecting the table option from the Insert
- A Dialog box will appear to give the Range for the Table and Select option ‘ My Table has Headers ‘
- Enter the data in the selected table.
- Select the table and insert a suitable chart for it.
- A stacked Line with the Markers Chart is inserted. Your chart will look like as given below:
- Change the data in the table and which in turn will change the chart.
In step 4, it can be observed that after changing the dynamic input range of the column, the graph is automatically updated. There is no need to change the graph, which turns out to be an efficient way to analyze the data.
Example #2 – By Using Named Range
This method is used in the scenario where the user is using an older version of Excel, such as Excel 2003. It is easy to use, but it is not as flexible as the table method used in excels for the dynamic chart. There are 2 steps in implementing this method:
- Creating a dynamic named range for the dynamic chart.
- Creating a chart by using named ranges.
Creating a dynamic named range for the Dynamic Chart
In this step, an OFFSET function (Formula) is used for creating a dynamic named range for the particular dynamic chart to be prepared. This OFFSET function will return a cell or range of cells that are specified to the referred rows and columns. The basic steps to be followed are:
- Make a table of data as shown in the previous method.
- From the ‘Formulas’ tab, click on ‘Name Manager’.
- A Name Manager Dialog box will appear in that Click on “New”.
- In the appeared dialogue box from the ‘Name Manager’ option, assign the name in the tab for the name option, and enter the OFFSET formula in the ‘ Refers To ‘ tab.
As two names have been taken in the example (Player and Runs), two name ranges will be defined, and the OFFSET formula for both the names will be as:
4.8 (9,181 ratings)
View Course
- Player =OFFSET($A$4,0,0,COUNTA($A$4:$A$100),1)
- Runs =OFFSET($B$4,0,0,COUNTA($B$4:$B$100),1)
The formula used here makes use of the COUNTA function as well. It gets the count of a number of non-blank cells in the target column, and the counts go to the height argument of the OFFSET function, which instructs the number of rows to return. In Step 1 of method 2, defining a named range for the dynamic chart is demonstrated, where a table is made for two names and the OFFSET formula is used to define the range and create name ranges in order to make the chart dynamic.
Creating a chart by using named ranges
In this step, a chart is selected and inserted, and the created name ranges are used to represent the data and make the chart a dynamic chart. The steps to be followed are:
- From the ‘Insert’ tab, select the ‘Line’ chart option.
- A Stacked Line chart is inserted.
- Select the entire chart, either right-click or go to the ‘select data’ option or from the ‘Design’ tab go to ‘select data’ option.
- After the select data option is being selected, the ‘Select Data Source dialogue box will appear. In that, click on the ‘Add’ button.
- From the Add option, another dialogue box will appear. In that, in the series name tab, select the name given for the range and in the series value, enter the worksheet name before the named range (Method2! Runs). Click OK
- Click on the Edit button from the horizontal category axis label.
- In the axis labels, the dialogue box enters the worksheet name and then named range (Method2! Player). Click OK.
- Give a Chart Title as Match Analysis.
After following these steps, a dynamic chart is created by using the formula method and also it will be updated automatically after inserting or deleting the data.
Pros of Dynamic Chart in Excel
- A dynamic chart is a time-efficient tool. Saves time in updating the chart automatically whenever new data is added to the existing data.
- Quick visualization of data is provided in case of customization is done to the existing data.
- The OFFSET formula used overcomes the limitations seen with VLOOKUP in Excel.
- A dynamic chart is extremely helpful for a financial analyst who tracks the data of companies. It helps them understand the trend in a company’s ratios and financial stability by just inserting the updated result.
Cons of Dynamic Chart in Excel
- In the case of a few, hundreds of formulas used in the excel workbook can affect the Microsoft Excel performance with respect to its recalculation needed whenever the data is changed.
- Dynamic charts represent information in an easier way, but also it makes more complicated aspects of the information less apparent.
- For a user who is not used to use excel, the user may find it difficult to understand the functionality of the process.
- In comparison with the normal chart, dynamic chart processing is tedious and time-consuming.
Things to Remember About Dynamic Chart in Excel
- When creating name ranges for charts, there should not be any blank space in the table data or datasheet.
- The naming convention should be followed, especially while creating a chart using name ranges.
- In the case of the first method, i.e., using an excel table, the chart will update automatically whenever the data is being deleted, but there would be blank space in on the right side of the chart. So, in this case, drag the blue mark at the bottom of the excel table.
Recommended Articles
This has been a guide to Dynamic chart in Excel. Here we discuss its uses and how to create a Dynamic Chart in Excel with excel examples and downloadable excel templates. You may also look at these useful functions in excel –
