We already learned how to create an animated chart in Excel. And we are going a step forward here. We will add transitions to our animation so the movement is smoother and gives the feeling of continuity.

The differences between the two methods can be seen in the following example:

In this article, we are going to start from the previously created file and we will just enhance it with the required logic.

Overview

Recapping the different sections that our resulting file will contain:

1. Historical Data: The information that will be shown. Each line corresponds to one day.
2. Parameters: To manually change the speed of the animation.
3. Intervals: Calculated automatically from the Parameters. Indicates how long a frame should be displayed.
4. Current Day Data: Data that is currently shown in the chart.
5. Chart: Where the animation happens.
6. Macro Button: Generates the animation refreshing the Chart multiple times.

Enhancing the file for including the transitions

When we say that the animation doesn't have transitions, it means that each frame belongs to a different day. And from one day, the bars "jump" into the next value.

To add the transitions, we are going to create intermediate frames within the same day. So, if we have an emotion with a value of 1 one day and a value of 2 the next day, we are going to create a frame with a value of 1.5, for example. We will add parameters to define:

1. The number of transition frames that we will have between 2 days.
2. The percentage of the time that the first frame should be shown (we call it "static frame").

Historical Data

In this section, we will include a new line at the end of the table. This line should contain the same values as the last day but without any Day number.

The reason: The transition reads the emotion from the current and next days. So we are faking a "Next day" after the last one. You will see it more clearly in the next sections.

As this is a "Helper" row, we will paint it with a different color.

Parameters

In the Parameters section we will need two more rows:

Frames Per Day. If we set it to 1, then we will have an animation without transitions. The higher this parameter the smoother the animation will be. The limit is the refresh rate of the Excel on your computer.

Static Frame (%) is the percentage of time that the first frame will be shown before starting the transition. This parameter is useful to keep showing the exact value before starting to move to the next one.

With these 2 parameters in place, we can define the intervals.

Intervals

This section will require a bit of extra logic.

First, we need to change the way in which the static frame interval is calculated. In the "without transition" version, it was simply the 1000 ms of a second divided by the Days Per Second.
But now, if we have more Frames Per Day, then we need to also consider the Static Frame (%). So the formula, when Frames Per Day > 1, is:
1000 ms / Frames Per Day * Static Frame (%)

This would be (for copy/paste purposes): =1000/J2*IF(J3=1;1;J4)

In the example, having 1 Day Per Second, 3 Frames Per Day, and a Static Frame (%) of 50%, the Static Frame (ms) will be 500. This means that the first frame will be frozen for 500 ms.

Second, we need to add an interval for the Transition Frames. So we need to divide the remaining time of the day among the rest of the frames. In the same example as above, we still need to fit 2 frames in the remaining 500 ms. So the time for the Transition Frames would be 250 ms each.

So when the Frames Per Day is 1, this interval will be 0. In other cases, it will be:

[(1000 ms / Days Per Second) - Static Frame (ms)] / Remaining Frames to get the ms for each of the remaining frames.

To Copy/Paste: =IF(J3=1;0;(1000/J2-J7)/(J3-1))

As a result, we see the value 250 ms in cell J8.

With these parameters and intervals in place, it´s time to change the Current Day Data.

Current Day Data

In this section we need to do 2 things:

1. Include a new column specifying the Frame. This column will show the current frame within the current day (remember that each day will have more than one frame for the transition).
2. Change the formula in the cells, so the Frame is considered and the value stays somewhere between the Current Day and the Next Day values.

The new column Frame will also be changed only by the Macro. So we paint it with a different color to identify it.

The formula for the different emotions should be changed in the following way:

Instead of showing the exact value for the Current Day, we need to create an intermediate value between the Current and the Next days, depending on the Current Frame.

If we are in the first frame of the day, then the value will be the exact value of the Current Day. But if we are in a different frame, we need to calculate it according to:

• The Delta value (difference between current and next).
• The number of frames.
• The Current Frame.

So in Cell B3, for example, we will replace the logic:

=INDEX(B7:B36;$A3)

With

=INDEX(B7:B36;$A3)+(INDEX(B7:B37;$A3+1)-INDEX(B7:B36;$A3))/$J$3*($G3-1)

This would be: Current Day value + Delta Value / Frames * Current Frame

Now, you can manually change the Current Day and the Current Frame and the values will be updated according to the Historical Data.

Chart

There are no changes required to perform in the chart. It's the same as in the version without the transition.

Macro

The macro should include a new LOOP. We had a single loop for the days, but now we need to loop the frames within each day. So here we can see the lines that need to be inserted.

And the complete routine text so you can copy it into your file.

Sub Animate()
    '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

    Dim intervalTransitionFrame As Double
    intervalTransitionFrame = Range("J8").Value / 1000

    'Timer variables
    Dim timerStop As Double

    'Helpers
    Dim currentRow As Long
    Dim currentFrame 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
        Range(COL_FRAME & ROW_CURRENT_DAY).Value = 1

        '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

        'Static frame time is over. If there are transition frames, show them
        If framesPerDay > 1 Then
            For currentFrame = 2 To framesPerDay
                Range(COL_FRAME & ROW_CURRENT_DAY).Value = currentFrame
                timerStop = Timer + intervalTransitionFrame
                Do While Timer < timerStop
                    DoEvents: DoEvents: DoEvents: DoEvents
                Loop
            Next currentFrame
        End If  'If framesPerDay > 1 Then
    Next currentRow
End Sub

Now you can assign the macro to the shape (Button) and test it.

Keep in mind that the refresh rate of Excel on your computer will limit the animation speed. In my case, the lowest time a frame can be shown is about 70 ms. So after that threshold, incrementing the number of Days Per Second or Frames Per Day will make the film take longer.

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.

Conclusion

By using this technique, you can add some transitions to your animations, play with the parameters and take your charts and presentations to the next level. I hope you enjoyed it!!