How To Create Dynamic Charts In Excel In 2024

In a data-driven world where decisions are made based on data, the ability to present data in an easy-to-read and intuitive way plays an essential role.

Dynamic charts are a tool in MS Excel that makes it easier for you to represent complex data in a fun and interactive way.

In this article, I will teach you how to master the art of creating dynamic charts to help you take your presentations to the next level.

Let’s see how you can transform raw data into compelling stories that are easy to read and understand.

With no further delay, let’s explore the benefits and the concept of dynamic charts and how to create them in Excel to make your data visually appealing and intuitive.

What Are Dynamic Charts?

Dynamic Charts in Excel are advanced charts that update automatically when the range of the chart is changed or updated.

In dynamic charts, you don’t have to update data in charts manually as dynamic charts can auto-update the data in dynamic charts.

These charts are best for data visualization and represent data of different ranges in real-time without having to manually update the data.

Tutorial For Creating Dynamic Charts In Excel

To make it easier for you to understand how to create dynamic charts in Excel, I am going to use demo data on two different sheets, Sheet 1 will be a datasheet, and Sheet 2 will be a Dynamic Chart sheet.

As you can see in the first sheet, I have maintained 5 columns of data including SNO, Name of the product, City Name, Country Name, and Sold QTY.

Now in the new sheet, we will create a dynamic chart and the reason for creating a dynamic chart in the new sheet is to keep the original data clean.

Now let’s see how to use this data in the new sheet to create a dynamic chart.

In the new sheet, we will be creating a dynamic chart to get country-based data; to find out which product got sold in which country and in what quantity.

The first thing we need to create a dynamic chart in Excel is to create a list in the new sheet.

To create the list, simply follow these steps:

Select the cell where you want to create the list.

Go to the Data Tab and look for the Data Tools and right above the Data Tools, you will find the Data Validation option.

Now click on the Data Validation option and a new dialog box will appear.

In the new dialog box, select the option list from the drop-down menu.

In the next step, it will ask you to select the source for the list you want to create.

The data for the name of the country is in Sheet 1 which we named Datasheet.

Now I will select India from the country column because we are creating a dynamic chart to find out which country sold which product and in what Quantity.

To select the full range of the County column we will simply use a shortcut key (CTRL+SHIFT+DOWN ARROW).

Now click on the OK button and it’s done.

As you can see, we have successfully created the list. But as you can see our list also contains duplicates. This is because I am not using Microsoft 365’s paid version.

But there’s still a way to solve this problem.

To remove duplicates from our list, we will go back to the Data Sheet and copy the data from the country column, and paste it separately.

Now select the new range of data we just copied, go to Data Tab, and select the option Remove Duplicates, a new dialog box will appear, click on the OK button.

As you can see, all the duplicates have now been removed from the country list. Now we can use this data to create our dynamic chart.

Now we will do the same with the Name of the product column.

Now we will copy-paste this data on Sheet 2 which we named Dynamic Chart.

The next thing we require is the formula to get the exact and correct data for the sold quantity of products in different countries.

Now we want to know according to the country which products are sold and in what quantity. As our data contain more than one condition we will be using the SUMIFS formula.

As you can see the syntax contains certain conditions that we need to follow to make our formula work.

The first condition is to select the sum range that we want to know. Simply go to the Data Sheet and select the sum range which is Sold Qty (Column E).

The second condition is to select the range of criteria 1 according to which you want to see data.

In our case, we want to see the data according to the country so we will simply select the country column which is column D.

The next condition (criteria1) is to give the name of a single country you want to see the data of.

The data is in the Dynamic Chart Sheet, so we will go to our Dynamic Chart sheet and select India.

Next is criteria range 2 which is asking along with India what total you want to see. In our case, we want to see the total Name of the product so we will select column B.

Criteria 2 is to give the single name of the product in the data. For this, we will go back to our Dynamic Chart Sheet and select Mouse.

Now our formula is complete and you can see the sold quantity of mouse in India in the data.

Now we will simply copy and paste the formula into the rest of the cells in the Sold Qty column.

To copy-paste the formula, simply drag the cursor to the range you want to copy the formula.

Even though we copied the formula to the rest of the cells, it is still showing 0 for the rest of the cells. This is because there’s an error in the formula.

To fix the formula and to find out the error we simply need to double-click on the last cell of column C of Sold Qty.

As you can see criteria 1 is selected on a blank cell and this is why it is showing an error. To fix that we will just fix the criteria 1 range.

To do that, we will have to edit our formula and fix the range. To fix the range for Criteria 1, click in between A2 and press the F4 key and it will fix the criteria range.

As you can see the error has gone and our data is working accurately. You can now change the country to check if the formula works and show accurate data.

To make the data more interesting we can add some other info in it as well. For example, I want to give this table a heading that changes automatically.

I want the data to automatically show the heading “Total Sold Qty of the selected country.”

You can see how the heading changes automatically every time you select a different country.

Now our data is ready and we can create our Dynamic chart.

Creating Dynamic Chart In Excel

To create Dynamic chart in Excel follow these simple and easy steps:

Click on an empty cell on your datasheet, go to Insert Tab, select the option Chart, and select 2D Charts.

You will see a blank plain chart on your screen.

Now to add data to the chart, click on the border of the chart, and go to chart design, and select the option, Select Data.

After selecting the data range Click on the OK button and our Dynamic Chart is ready.

Our Dynamic Chart is ready but it doesn’t look that interactive. To make our chart visually more appealing and interactive, we need to make some edits.

To change the theme of your chart, go to Design and select any design theme you like.

As you can see I have removed the lines and numbers from the chart. I will show you the better way to add the number to the column.

To add numbers to the columns in the chart simply click on the columns and right-click the mouse. A new dialog box will appear, select Add Data Labels and Click on Add Data Labels.

Now if you want your chart to automatically change the heading of the total sold quantity, simply click on “Sold Qty” on the chart then go to the formula bar type” =”, and select the Cell “Total Sold Qty”

As you can see the heading in the charts now automatically changes every time you select the data of a different country.

There are various themes, colors, and highlights that you can use to format your dynamic charts to make them visually appealing and easy to read.

Detailed video tutorial on how to create Dynamic Charts in Excel

Bottom Line

Dynamic Charts is a great tool for data visualization, easy data reading, and understanding of complex data.

Dynamic charts offer a wide range of features and options that you can use to create interactive and intuitive charts in Excel.

It doesn’t matter whether you are a beginner or an experienced Excel user, learning how to create Dynamic Charts can help you stand out while preparing for your data presentation.

SkillCourse

SkillCourse

What Are Dynamic Charts?

Tutorial For Creating Dynamic Charts In Excel

Bottom Line

Also Read

Leave a Comment Cancel reply

Support

About Skill Course

Imp. Links

Company

Other Links

Send your doubt