When 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 exclusive 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 beautiful flower in the garden. Telling the story behind the data through animation goes a long way in holding on to the user 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 column chart for this data.
After inserting the chart we need to insert “Option Button” from the Developer tab.
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.
As you can see in the above image “Option Button” is unchecked and cell link A14 shows 0. If I check the Option the button it becomes 1.
Like this insert 4 more option buttons and name them as “Q1”, “Q2”, “Q3”, and “Q4” respectively.
Note: Give cell link to the same cell i.e. A14 cell.
Now 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 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 first Quarter so for this we need insert below formula.
I have mentioned IF formula, 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 #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 “Add” button.
In Edit Series window choose F1 for Series Name and for Series Values choose Quarter column values.
Click on Ok, we will have a chart like the below.
Now select the column bars and press Ctrl + 1 to format 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 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 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 –