Dynamic Chart in Excel (Table of Contents)
Dynamic Chart in Excel
Dynamic charts are used in operations, where there is a need for an automatic update to select a dynamic range by changing the data which in turn updates the chart or graph. Similarly, dynamic charts in excel are updated or resized when the dynamic data range used as source data is changed or deleted.
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 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:
- 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 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 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 ‘Line’ chart option.
- A Stacked Line chart is inserted.
- Select the entire chart, either right-click or go to ‘select data’ option or from the ‘Design’ tab go to ‘select data’ option.
- After the select data option is being selected, ‘Select Data Source’ dialogue box will appear. In that, click on ‘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 series value enter the worksheet name before the named range (Method2! Runs). Click OK
- Click on 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, 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 data of companies. It helps them understand the trend in the ratios and financial stability of a company by just inserting the updated result.
Cons of Dynamic Chart in Excel
- In case of few hundreds of formulas used in 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 case of the first method i.e. by using excel table, whenever the data is being deleted, the chart will update automatically but there would be blank space in the 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 Dynamic Chart in Excel with excel examples and downloadable excel templates. You may also look at these useful functions in excel –
