WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Dynamic Chart in Excel

Dynamic Chart in Excel

By Harsh KataraHarsh Katara | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

What are Dynamic Charts in Excel?

A dynamic chart in excel is a special type of chart in excel which updates itself when the range of the chart is updated, in static charts when the range is updated the chart doesn’t update itself so in order to make a chart dynamic we need to make a range dynamic  or the source of the data, it can be done by naming the ranges from excel table or using formulas like offset function.

There are 2 methods to create a dynamic chart in excel

  1. Create a Dynamic Chart Using Name Range
  2. Create a Dynamic Chart Using Excel Tables

Now let us explain each of the methods in detail with an example

#1 How to Create a Dynamic Chart Using Name Range?

All right, let me explain you with an example. Below is the survey data for the need of the excel knowledge across professions. The below data holds the record of excel knowledge required and the salary range for those professions.

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

Dynamic Chart Example 1.

Now insert one simple column chart show the salary ranges.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Dynamic Chart Example 1-2

Now, if the profession list increases, this chart cannot take that range automatically.

Dynamic Chart Example 1-3

I have updated the other two professions with their salary range. This chart is 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.

Dynamic Chart Example 1-4

  • Step 2: Click on Name Manager in Excel and apply the formula, as shown in the below image. This will create a dynamic range in excel for the Salary column.

Dynamic Chart Example 1-5

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

Dynamic Chart Example 1-6

Now we have created two dynamic ranges 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 the Insert tab and select the column chart.

go to Insert tab and select the column chart

  • Step 5: Click on the column chart in excel and select a 2D clustered column chart. At this point in time, it will insert a blank chart.

Dynamic Chart Example 1-8

  • Step 5: Right-click on the data and select “Select Data.”

Right-click on the data and select “Select Data

  • Step 6: Click on the Select Data option, and it will open up the below box and click the Add button.

Click on Select Data option

  • Step 7: once you click on the Add button, it will ask you to select the series name and series values.

edit 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 the salary column, i.e., Salary_Range.

Note: We cannot simply mention the range name; rather, we need to mention this along with the sheet name too, i.e., =’Chart Sheet’!Salary_Range

edit series

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

select data source

  • Step 9: Once you click on the Edit option, it will open up the below box. It will ask you to mention the Axis Label Range.

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 to mention this along with the sheet name too, i.e., =’Chart Sheet’!Profession_Range

Axis labels

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

Dynamic Chart Example 1-16

  • Step 12: Now again, add those two professional data. The chart should extend automatically.

Dynamic Chart Example 1-17

Wow! It is working. Sit back and relax. Gone are the days where we need to update the data as well as the chart range. Now we have a dynamic chart range to update the dynamic chart ranges automatically.

#2 How to Create Dynamic Chart Using Excel Tables

Follow the below steps to explore this option.

  • Step 1: Select the data and press CTRL + T. This will create the table for you.

create table

  • Step 2: Once the table is created > select the data from A1:B6 > go to insert tab > insert column chart.

insert column chart

  • Step 3: Now add those two professions to the list.

Dynamic Chart Example 1-20

Things to Remember

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

Recommended Articles

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

  • Surface Chart in Excel
  • Dynamic Tables in Excel
  • Dynamic Named Range in Excel
  • Pareto Chart in Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

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

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

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

WallStreetMojo

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

Login

Forgot Password?

WallStreetMojo

Download Dynamic Chart Excel Template

Special Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More