EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Charts Excel Dynamic Chart
Secondary Sidebar
Excel Functions
  • Excel Charts
    • Excel Data Visualization
    • Dashboard in Excel
    • Bullet Chart in Excel
    • Excel Animation Chart
    • Plots in Excel  
    • Interactive Chart in Excel
    • Surface Charts in Excel
    • Excel Calendar Template
    • Thermometer Chart in Excel
    • Box Plot in Excel
    • S CURVE in Excel
    • Venn Diagram in Excel
    • Project Management with Gantt Chart
    • Change Chart Style in Excel
    • 3D Scatter Plot in Excel
    • Box and Whisker Plot in Excel
    • Radar Chart in Excel
    • Control Charts in Excel
    • Dot Plots in Excel
    • Marimekko Chart Excel
    • Combo Chart in Excel
    • Contour Plots in Excel
    • Candlestick Chart in Excel
    • Map Chart in Excel
    • Pie Chart Examples
    • Excel Stacked Bar Chart
    • Excel Flowchart
    • Excel Types of Graphs
    • Excel Normal Distribution Graph
    • Excel Bell Curve
    • Legends in Excel Chart
    • Excel Chart Templates
    • Excel Organization Chart
    • Excel Doughnut chart
    • Excel 3D Plot
    • Excel Scatter Chart
    • Excel Line Chart
    • Excel Combination Charts
    • Excel Gauge Chart
    • Excel Pivot Chart
    • Excel Clustered Column Chart
    • Excel Dynamic Chart
    • Excel Stacked Column Chart
    • Excel Bubble Chart
    • Histogram Chart Excel
    • Excel Gantt Chart
    • Excel Stacked Area Chart
    • Excel Waterfall Chart
    • Excel Area Chart
    • Excel Clustered Bar Chart
    • Excel Column Chart
    • Excel Pie Chart
    • Comparison Chart in Excel
    • Pareto Analysis in Excel
    • Add a Secondary Axis in Excel
    • Grouped Bar Chart
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED COURSE
  • Excel Data Analysis Course
  • Excel for Marketing Course

Excel Dynamic Chart

By Madhuri ThakurMadhuri Thakur

Dynamic Chart in Excel

Dynamic Chart in Excel (Table of Contents)

  • Dynamic Chart in Excel
  • How to Create a Dynamic Chart in Excel?

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:

  1. By Using Excel Table
  2. 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.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

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

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:

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,813 ratings)
  • Create a table in Excel by selecting the table option from the Insert

Dynamic Chart Example 1

  • A Dialog box will appear to give the Range for the Table and Select option ‘ My Table has Headers ‘

Dynamic Chart Example 1-1

  • Enter the data in the selected table.

Dynamic Chart Example 1-2

  • Select the table and insert a suitable chart for it.

Dynamic Chart Example 1-3

  • A stacked Line with the Markers Chart is inserted. Your chart will look like as given below:

Dynamic Chart Example 1-4

  • Change the data in the table and which in turn will change the chart.

Dynamic Chart Example 1-5

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:

  1. Creating a dynamic named range for the dynamic chart.
  2. 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.

Dynamic Chart Example 2

  • From the ‘Formulas’ tab, click on ‘Name Manager’.

Dynamic Chart Example 2-1

  • A Name Manager Dialog box will appear in that Click on “New”.

Dynamic Chart Example 2-2

  • 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)

Dynamic Chart Example 2-3

  • Runs =OFFSET($B$4,0,0,COUNTA($B$4:$B$100),1)

Dynamic Chart Example 2-4

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.

Dynamic Chart Example 2-5

  • A Stacked Line chart is inserted.

Dynamic Chart Example 2-6

  • Select the entire chart, either right-click or go to the ‘select data’ option or from the ‘Design’ tab go to ‘select data’ option.

Dynamic Chart Example 2-7

  • After the select data option is being selected, the ‘Select Data Source dialogue box will appear. In that, click on the ‘Add’ button.

Dynamic Chart Example 2-8

  • 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

Dynamic Chart Example 2-9

  • Click on the Edit button from the horizontal category axis label.

Dynamic Chart Example 2-10

  • In the axis labels, the dialogue box enters the worksheet name and then named range (Method2! Player). Click OK.

Dynamic Chart Example 2-11

  • Give a Chart Title as Match Analysis.

Dynamic Chart Example 2-12

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.

Dynamic Chart Example 2-13

Pros of Dynamic Chart in Excel

  1. A dynamic chart is a time-efficient tool. Saves time in updating the chart automatically whenever new data is added to the existing data.
  2. 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.
  1. 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

  1. 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.
  2. 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.
  1. 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 –

  1. Marimekko Chart Excel
  2. Interactive Chart in Excel
  3. Surface Charts in Excel
  4. Comparison Chart in Excel
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel Data Analysis Training (17 Courses, 8+ Projects)4.9
Excel for Marketing Training (8 Courses, 13+ Projects)4.8
1 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Dynamic Chart Excel Template

EDUCBA

Download Dynamic Chart Excel Template

EDUCBA

डाउनलोड Dynamic Chart Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more