Excel Hero and Animated Charts

Daniel Ferry is the purveyor of one of the more interesting Excel Blogs out there (ExcelHero.com). His blog has the curious distinction of being the only one with an unexplicable rooster on the banner:

If you’ve never visted, you must. Daniel has a knack for passing on advanced Excel techniques through

beautifully assembled Excel charts and examples.


For a guy like me (deep in the dark dank heart of the corporate world) it’s difficult to apply many of the wonderful things Daniel does on a daily basis. However, Daniel has come up with a very cool technique to animate business charts.


In Daniel’s original workbook, he relies heavily on named ranges and does require a good bit of setup. I took the liberty of taking his idea and creating a my own method for animating charts.

Too lazy to read on? I’ve got a working example you can download here.

Given that I like to have something more turnkey than his original example, I created this code cartridge, which anyone can plug into a workbook and it just goes.

Simply copy this code and paste it into a new module:

  1. Function AnimateChart(OldDataSet As String, NewDataSet As String)
  2. Dim NewData As Variant
  3. Dim OldData As Variant
  4. Dim AnimationArray As Variant
  5. Dim OldPoint As Long
  6. Dim NewPoint As Long
  7. Dim x As Integer
  8. Dim i As Integer
  9. Dim p As Double
  11. NewData = ActiveSheet.Range(NewDataSet).Value
  12. OldData = ActiveSheet.Range(OldDataSet).Value
  13. AnimationArray = ActiveSheet.Range(NewDataSet).Value
  15. For i = 1 To 15
  16. p = 100 / 100 / 15 * i
  17. For x = 1 To WorksheetFunction.Count(NewData)
  18. OldPoint = OldData(1, x)
  19. NewPoint = NewData(1, x)
  20. AnimationArray(1, x) = OldPoint - (OldPoint - NewPoint) * p
  21. Next x
  22. Range(OldDataSet).Value = AnimationArray
  23. DoEvents
  24. Next i
  25. End Function

Then just create a macro (which you can assign to a combobox) to call the function as needed:

Sub Chart1Change()
Const OldData As String = “B27:M27” ‘<Excel Hero Academy

Only two days left to register!!

10 thoughts on “Excel Hero and Animated Charts

  1. Gregory

    Nice piece of code. I liked the part about putting the data label position inside the bar if greater than 90% of the max scale. Sweet!

  2. Erin

    Daniel’s rooster makes sense to me. He’s looking for the beautiful and delicious seeds buried in Excel so he can give us these wondrous graphics and the tools to grow our own.

  3. Steve

    Its good to see this original work broken down for those of us who are new to programming in VBA/Excel. However, in the original file the bar transitions were slower – I think referred to as “Easing” – is there a way to slow the transitions here in your file ?

  4. Jezza

    What codes do I add to make the graph animation show decimal numbers, rather then whole integers? Thanks.

  5. Shayam

    I have everything down except for the “data for chart” (Row 27 on your spreadsheet). How did you get these numbers to change without any formulas?? Any help would be greatly apperciated.

  6. best seo plugin

    I have noticed that credit restoration activity ought to be
    conducted with techniques. If not, chances are you’ll find yourself causing harm to your rating. In order to reach your goals in fixing your credit score you have to always make sure that from this time you pay your entire monthly costs promptly prior to their slated date. It is significant given that by never accomplishing this, all other measures that you will take to improve your credit ranking will not be useful. Thanks for expressing your thoughts.

  7. chirayu

    How do I make it work on a separate sheet. As in if my data is on a hidden sheet and my chart is on a visible sheet.

Leave a Reply

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