Do you ever wanted to show devel­op­ment of some process over time?

Well I did, but unfor­tu­nately there is no built in func­tion­al­ity in Excel that would enable us to do so in ele­gant way… How­ever what can­not be achieved by a lit­tle bit of VBA code? Today I am gonna present the basics behind two sim­ple ways to ani­mate a chart in Excel.

First one com­bine for­mu­las and VBA. The sec­ond rely on VBA alone. In both I assume that chart was already cre­ated and the maximum, minimum and major units were set to con­stant for all axes. In other words options in ‘For­mat Axis’ menu were set as shown below.

            Y Axis Options                   X Axis Options

These con­di­tions guar­an­tee that the chart will not start look­ing funny when ani­mated (after you test the pre­sented cases and you want to exper­i­ment set max­i­mum scale val­ues to ‘Auto­matic’ and see what happens).

The chart I use to present both solu­tions is a sim­ple scat­ter plot depict­ing val­ues of log­a­rithm func­tion from 1 to 100. This is pretty didac­tic exam­ple, but the meth­ods can be used with other chart types as well.

For­mu­las and VBA Solution

Ani­mated chart in Excel can be cre­ated using for­mu­las thanks to sim­ple, yet pow­er­ful, chart options set known as ‘Hid­den and Empty Cell Set­tings’. This menu can be accessed when select­ing a data source and allows to not ren­der data points that are either empty or not applic­a­ble. All you need to to is to select ‘Show empty cells as: Gaps’.

Hidden and Empty Cells Settings

Next you need to cre­ate new data series which equals to:

- NA() when your time axis is greater than ‘counter’

- the orig­i­nal when your time axis is less than ‘counter’

The exact for­mula for that is shown on the screen­shot below. In this exam­ple the ‘counter’ rep­re­sents cur­rent time and is used to deter­mine which data points should be visible.

Formula used to generate 'Not Applicable' data points

The last thing to do is to insert a very sim­ple VBA snip­pet in the work­book that con­tain the chart. The pur­pose behind these four lines of code is to auto­mate chang­ing the counter and force the Excel Appli­ca­tion to fire ren­der­ing. This actu­ally ani­mate the chart.

Sub Animate1()

For i = 1 To 100

   Cells(3, 2).Value = i

   DoEvents

Next i

End Sub

Please note that ‘DoEvents’ line is very impor­tant. With­out it Excel won’t repaint the chart, so you will not see the effect until the pro­ce­dure fin­ish. In other words the result won’t be an ani­ma­tion but an appear­ance of the chart.

Pros: sim­ple

Cons: rely on work­book structure

When the cons over­weight the pros it’s time to move to a lit­tle bit more advanced methods.

VBA Solu­tion

Below you can see another short VBA sub­rou­tine that achieves the same results as pre­vi­ously described solu­tion. How­ever the method is a lit­tle bit dif­fer­ent. This time we rely on a loop that assign one more value to chart serie with each iteration.

Sub Animate2()

Dim rng As Range
Dim ser As Series

Set ser = ActiveSheet.ChartObjects("Chart 2").Chart.SeriesCollection(1)

For i = 1 To 100

    Set rng = ActiveSheet.Range(Cells(2, 3), Cells(2, 3 + i))

    ser.Values = rng

    DoEvents

Next i

End Sub

In this way there is no counter chang­ing on the work­sheet and the user is not both­ered with  vis­i­ble recal­cu­la­tion of data series used in the chart.

Pros: does not rely on work­book struc­ture (so much)

Cons: some­what more complicated

Scene. Lights. Cam­era. Action!

Well that’s more or less every­thing for today. You can see for your­self effects of both meth­ods in Excel Ani­mated Chart Tem­plate or watch the video below to get a quick grasp how it works.

Next time I am going to dis­cuss the promised method for instal­la­tion and update man­age­ment of Excel Add Ins. Unitl then stay tuned!

Tagged with:
 

6 Responses to Animated Charts in Excel

  1. Sansan Tagarino says:

    Wow! Look­ing for­ward to the next one!

  2. Cornelia Shike says:

    I just want to tell you that I’m begin­ner to blog­ging and site-building and hon­estly liked this web-site. Very likely I’m plan­ning to book­mark your blog . You amaz­ingly come with ter­rific posts. Thanks for shar­ing with us your website.

  3. Jill says:

    The blog is cool.

  4. A-C says:

    Hi!

    Thanks for shar­ing the great Excel tricks!

    Do you know how to include the ani­ma­tion in a Pow­er­Point presentation?

    Thank you!
    A-C

  5. Maxime Manuel says:

    Exactly what I was look­ing for. Thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>