Updated June 13, 2023
Excel Animation Chart (Table of Contents)
Introduction to Animation Chart in Excel
The charts that illustrate the data animatedly and dynamically to understand better the charts are called animation charts. The animation charts require a little additional effort and knowledge of macro to create dynamic changes and movements in the chart.
To create an animation chart, we first need to create a normal one, for which we will add little coding to make it an animated one. So, one should know VBA coding to understand and create animated charts. Using coding for animated charts is not mandatory if you are good at creative thinking; you can also make it without VBA coding.
Example of Animation Chart in Excel
Here I am covering an example of the percentage completion of a race by a car. Follow the below steps to create the animated chart. First, create a small table layout for race details with the racer name and percentage of Races completed, as shown in the below picture.
Enter the data related to the percentage of the race completed in “column B” if you want to input 60 percent as 0.6 and then use the cntrl+shift+% symbol on the keyboard to convert it to a percentage.
Now add another two columns as below and input the same data as column B. I know it may confuse you; it will be clear once we create the chart.
Hide column B.
Select only visible data by clicking on Alt+; click on the Insert option, then choose 2D columns highlighted in the red color box.
Select the Stacked Bar Chart below.
Now we will remove all the percentages, chart title,s and Legends by clicking on the plus symbol.
Deselect all the checkboxes below.
Click on right-hide on a chart, then select Format Data Series.
Now double-click on the percentage completion part and choose Gradient fill on the right-hand side, as shown below. We can change the color as per our requirements.
Now choose the car percentage that is orange part of the chart as below.
Choose a car picture from Google or copy it into your desktop images and paste it on the orange part; the map looks like below.
The design part of the chart is completed. We need to complete the VBA coding to create movements for the cars when we change the race completion percentage in the data table.
Click on the Developer tab and the visual basic in the left-hand corner.
Click on the sheet in which you created the chart. Here my chart is on sheet 2.
Select “Change” on the right top then we will get the “Private Sub Worksheet_Change(ByVal Target As Range)” in the coding area. The meaning of the code is the program will run whenever we change the data. But we should write the program to run only when we change a particular data set. For that, write the below coding.
Define a sheet “sh” and assign Sheet 2 to sh as below.
Define an integer as below.
If we observe the data table, the “percentage race completed” column occupies column B and rows 2 to 5. So, our target is COLUMN B and ROWS 2 to 5.
Once we clear our target rows and columns, we will write the coding below. Check if the change is in column 2.
If it is column 2, we must check which row it is changing. The row should be 2 to 5, so write the below code.
If both conditions satisfy, we need to perform an event as below.
The meaning of the code is “i” value iterates from zero to a changed percentage value. That value will multiply by 100 because the value in the column is in percentage. After every value of “I”, VBA will perform an event that is the “I” value divided by 100, and it will store in the same row but in column C, from where our chart takes values with which appearance will change every time.
That means whenever we change the value in the percentage completion column, the value in column C starts from Zero and goes until the changed value(new value). After completing the program, try to change the value in column B and observe the chart. You can observe the car movement from bottom to top depending on the percentage.
Everything looks fine, but we have multiple columns of data visible on the sheet. If you hide columns C and D, the chart also will disappear as below.
The solution is to click, enabling the “Design” menu on the top as below.
Select the option “Select Data”, then a window will open as below.
Click on the “Hidden and Empty cells” button.
Check the option “Show data in hidden rows and columns”, which will allow hiding the data related to charts. Select Ok here.
Now hide the columns “C” and “D” and check.
This is how we will make animated charts in Excel. Similarly, we can make different kinds of animated charts, like speedometer charts, circular charts, etc., in Excel with a combination of our normal charts and VBA coding. All you need to do is create ideas to design; there is no fixed way to create these animated charts.
Things to Remember
- Learning and understanding the VBA coding is advisable for creating a well-animated chart. But VBA coding is not that easy to write because it does not require a computer science degree to learn.
- Keep an animated chart simple and clear because too much animation on unimportant data items also requires more coding, or working may spoil the actual meaning.
- Create the chart that is apt for your data representation because every chart is unsuitable for all situations. A wrong selection of charts may restrict you to represent a limited amount of data.
- Don’t forget to save Excel with Macro enabled workbook when you use VBA coding.
Recommended Articles
This is a guide to Excel Animation Chart. Here we discuss how to create an Excel animation chart, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –
