Let's say that you want to create an animation to show the progress of some data over time. It could be anything from the weight you lost and your training routine to the results of your company or favorite team.

It doesn't matter what it is, but you want to show it differently and more funnily.

I used this technique recently to show my emotions during my first 30 days of writing articles for this website and Twitter posts.

There are several ways to achieve this, but in this article, we will see how it can be easily done in Excel.

Overview

The resulting Excel file will contain the following sections:

1. Historical Data: The information that will be shown. Each line corresponds to one day.
2. Current Day Data: This is what is currently shown in the chart.
3. Parameters: To manually change the speed of the animation.
4. Intervals: Calculated by the sheet. Indicates how long a frame should be displayed.
5. Chart: This is where the animation happens.
6. Macro Button: Contains a timer to update the chart.

Logic explained in 1 line (ok... Maybe in 3)

The Chart is refreshed from the Current Day Data, which reads the Historical Data considering the Day (Cell A3). That cell is incremented by 1 by the Macro according to the Intervals that are derived from the Parameters.

Transition

There are 2 possibilities for creating this file:

1. Without transitions: This is the initial version. The bars "jump" from value to value.
2. With transitions:  After creating the first version, the file can be enhanced so the bars move smoother.

Here is a comparison between both options:

In this article, we are going to create the version without transition. It's better for beginning with and addresses every functionality that needs to be covered. We are going to enhance this file in a future article.

Creating the File without transitions

Historical data

This is the base for our animation. In this case, we have 30 snapshots (corresponding to 30 days) and 5 bars (corresponding to 5 emotions). Of course, you can change these things so they match what you want to show.

Current Day Data

In this section, the first column (Day) represents the current day of the animation. The rest of the columns read the respective values of that day from the Historical Data.

So we need to include one column for the day and one column for each emotion.

In order to read the historical data, we will use the formula INDEX. This formula receives a range and a position and retrieves the value that appears in that position of the range.

In this case, for example, we have:

INDEX(B7:B36; A3) -> INDEX(B7:B36; 3) -> B9 -> 3

The same code should be copied to the rest of the columns. And now you can manually update the Day, and see how it is going to work.

Parameters

In this section, we include the parameters that may be manually changed in order to adjust the animation. For the version without transition, the only one that we need is the Days Per Second. It will define the speed of the animation.

Intervals

The intervals are the number of milliseconds that each frame should be displayed. Again, in the version without transition, we will have only 1 frame per day. So the formula here is: 1000 / Days Per Second.

The interval will be read in the macro, and the Current Day will be incremented once every Static Frame (ms) milliseconds.

Chart

For creating the chart:

• Select the Current Day Data (only the emotions and headers, but not the Day).
• Go to Insert > Insert Column or Bar Chart > 3-D Bar.
• Move the chart to the bottom right corner of the screen.

You can give your chart the "Look and feel" that you prefer. Here is how I formatted mine:

• Chart Area

  • Effects
    • 3D Rotation
      • X Rotation 0
      • Y Rotation 0
  • Size & Properties
    • Size
      • Height 11 cm
      • Width 14 cm

• Horizontal (Value) Axis

  • Axis Options
    • Axis Options
      • Bounds Maximum 10
      • Units Major 1

• Vertical (Category) Axis

  • Axis Options
    • Axis Options
      • Horizontal axis crosses At maximum category
      • Check Axis position "Categories in reverse order" flag
    • Labels
      • Distance from axis 400

• Series 1

  • Fill & Lines
    • Fill
      • Check "Vary colors by point" flag
  • Effects
    • 3-D Format
      • Top bevel Width 10pt
      • Top bevel Height 10pt
      • Material Matte

• Chart Elements

  • Uncheck the Title

For showing the current day as a title, merge cells K14 with K15 and L14 with L15. In L14 get the Day from cell A3. Increase the font size to 24.

Insert the emojis:

To remove the grids around the chart area, fill the cells with blank and also add a thick border. Leave some space above for the Animate button.

Now, you can manually update the Day, and you will see that the chart is refreshed.

Macro

The macro is used to update the Day automatically, so we get the animation.

Go to Developer tab and create a macro called Animate.

If the developer tab is not present, you will need to activate it in the options:

Write the following code and save the macro. When saving, you will be prompted to change the format of the file, as the XLSX doesn't allow macros. So you need to save it as XLSM.

'Column for each feeling
Const COL_CREATIVE As String = "B"
Const COL_TOO_BUSY As String = "C"
Const COL_EXCITED As String = "D"
Const COL_OVERWHELMED As String = "E"
Const COL_SATISFIED As String = "F"

'Columns for day and frame
Const COL_DAY As String = "A"
Const COL_FRAME As String = "G"

'Rows for Current day data (source for chart) and historical data
Const ROW_CURRENT_DAY As Long = 3
Const ROW_HIST_DATA_BEGIN As Long = 7
Dim rowHistDataEnd As Long
rowHistDataEnd = Range(COL_DAY & ROW_HIST_DATA_BEGIN).End(xlDown).Row

'Read the parameters
Dim framesPerDay As Long
framesPerDay = Range("J3").Value

'Read the intervals
Dim intervalStaticFrame As Double
intervalStaticFrame = Range("J7").Value / 1000

'Timer variables
Dim timerStop As Double

'Helpers
Dim currentRow As Long

'Iterate the historical data into the current day data according to intervals
For currentRow = ROW_HIST_DATA_BEGIN To rowHistDataEnd
    Range(COL_DAY & ROW_CURRENT_DAY).Value = Range(COL_DAY & currentRow).Value
    
    'Keep the static frame refreshing for the corresponding milliseconds
    timerStop = Timer + intervalStaticFrame
    Do While Timer < timerStop
        DoEvents: DoEvents: DoEvents: DoEvents 'Sometimes it doesnt refresh
    Loop
Next currentRow

To run the macro, include a shape with the caption "Animate" and Assign the newly created macro to it. This shape will work as a button.

Now you can specify the Days Per Second that you want (e.g. 3), and run the animation by clicking on the button.

Final File

You can download the file from here. But for security reasons, I'm not including the macro on it. So please follow the above step to create the macro and assign it to the button.

Congratulations

You have created an Animated Chart in Excel. I hope you enjoyed it! Unleash your creativity so you can build other funny things by using this technique.

Emojis

• Creative Image by Pete Linforth from Pixabay
• Too Busy Image by Pete Linforth from Pixabay
• Satisfied Image by Pete Linforth from Pixabay
• Overwhelmed Image by Sergei Tokmakov from Pixabay
• Excited Image by Pete Linforth from Pixabay