Excel Animation Chart (Table of Contents)
Introduction to Animation Chart in Excel
The charts that illustrate the data in an animated and dynamic way to better understand 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 chart for which we will add little coding to make it an animated chart. So, one should have knowledge of VBA coding to understand and create the animated charts. It is not mandatory to use coding for animated charts 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 percentage completion of race by a car. Follow the below steps to create the animated chart. First, create a small table layout for race details with racer name and percentage of Race 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 input as 0.6 and then cntrl+shift+% symbol on the keyboard to convert to a percentage.
Now add another two columns as below and input the same data as column B. I know it may create confusion for you; it will 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, which are highlighted in the red color box.
Select the Stacked Bar Chart below.
Now we will remove all the percentages, chart title and Legends by clicking on the plus symbol.
4.8 (9,616 ratings)
View Course
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 a car picture that you have in your desktop images and paste it on the orange part; then, the map looks like below.
The design part of the chart is completed. Now, we need to complete the VBA coding to create movements to the cars when we ever change the percentage of completion of the race in the data table.
Click on the Developer tab and then click on the visual basic in the left-hand corner.
Click on the sheet in which you created the chart. Here my chart is in 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 the particular set of data. For that, write the below coding.
Define a sheet “sh” and assign Sheet2 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 about our target rows and columns, we will write the below coding. Check if the change is in column 2.
If it is column 2, then we need to check which row it is changing. The row should be 2 to 5, so write the below code.
If both the 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 “I” value will divide by 100, and it will store in the same row but in column C, from where our chart is taking 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 completion of 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 is visible on the sheet. If you hide columns C and D, the chart also will disappear as below.
The solution for this is to click, which will enable 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 chart, circular charts, etc., in excel with the combination of our normal charts and VBA coding. All you need to do is creative ideas to design; there is no fixed way to create these animated charts.
Things to Remember
- It is advisable to learn and understand the VBA coding to create a well-animated chart. But VBA coding is not that easy to write at the same to it does not require a computer science degree to learn.
- Whenever creating an animated chart, try to keep it 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 apt for your data representation because every chart is not suitable for all situations. A wrong selection of chart 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 excel animation chart along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
