When the chart itself can convey the message so beautifully, how about adding animation to the chart! Animation? Yes, we can add animation to the charts that we create.
Chart Animation requires complete VBA knowledge, and master that advanced VBA skills are not a walk in the park; it requires special dedication in learning those coding skills. Feeding your VBA animation chart is too much to digest at the moment, so we will show you how to create animated charts in excel without VBA coding. Read on.
Charts are like a beautiful garden in the city, and adding animation to the chart is the lovely flower in the garden. Telling the story behind the data through animation goes a long way in holding on to the user’s attention for a long time. Static charts are just enough to convey the message without any suspense but adding animation to the chart just good enough to play with the emotions of the audience.
How to do Animation in Excel Chart?
Let’s build the Animation Chart with excel without the help of VBA coding.
For this animation, I have created the below data.
With this data, we will build an animated chart in excel to show each quarter.
The first thing we need to do is to insert a column chart in excel for this data.
After inserting the chart, we need to insert “Option Button” from the Developer tab Excel.
Draw this option button on the chart.
Edit the text and enter the text as “All Quarter.”
Right-click on the Options Button and choose “Format Control” or you can press Ctrl + 1 to open the “Format Control.”
Select “Unchecked” and give “Cell Link” to A14 cell.
Press Ok. We will have our first Option Button ready.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
As you can see in the above image, “Option Button” is unchecked, and cell link A14 shows 0. If I check, the Option Button value in A14 becomes 1.
Like this, insert four more option buttons and name them as “Q1”, “Q2”, “Q3”, and “Q4” respectively.
Note: Give a cell link to the same cell, i.e., A14 cell.
If the first Option Button is checked, then cell A14 value will change to 1; if the second Option Button checked, then cell A14 value will be 2; for the third Option Button, it will change to 3 and so on…
Now we need to set up the data to show each quarter sales separately. Create a replica of the data table to the right and add a new column as “Quarter.”
Now in the Quarter column, we need to enter some formula to capture the data based on the “Option Button” selection we make.
Jan, Feb, and Mar will be the first quarter, so we need to insert the below formula.
I have mentioned the IF formula in excel; based on the “Option Button” selection, it will reflect the data. If the Q1 option button is selected, it will reflect Jan, Feb, and Mar month numbers, or else it will reflect the #NA value.
Now for Q2 from Apr to Jun, we need to change the logical condition from 2 to 3.
If the Q2 Option Button clicked, this formula shows numbers for this quarter. Similarly, now for Q3 & Q4, we need to enter a formula.
For Q3 enter the formula as =IF($A$14=4,E8,NA())
And for Q4 enter the formula as =IF($A$14=5,E9,NA())
Now with this rearranged data, we need to add data series to the existing chart.
Right-click on the chart and choose “Select Data.”
In the below window, press on the “Add” button.
In the Edit Series window, choose F1 for Series Name, and for Series Values, choose Quarter column values.
Click on Ok, and we will have a chart like the below.
Now select the column bars and press Ctrl + 1 to format the data series option.
In the “Format Data Series” window, make series overlap as 100%.
Now select the bar and make the color as “Light Green” for all the bars.
Now for Quarter Series, fill with orange. We cannot see the impact immediately.
Ok, we are done with the formatting, now select the Q1 Option Button and see the magic.
As I have selected the Q1 Option button and Q1 months data has been highlighted with orange.
Like this, without using VBA, we can create animation charts in excel.
Things to Remember
- Using VBA code is the advanced way of creating an animated chart but requires extensive coding language.
- This is a simple example we can create a wide variety of animation charts without the help of VBA.
- Using form controls in excel like checkbox, spin button, option button, we can still create an animate chart without the help of VBA.
This has been a guide to Animation Chart in Excel. Here we discuss how to animate an excel chart without VBA coding with the help of an example. You can learn more about excel from the following articles –