Animated Business Chart

| 45 Comments | 0 TrackBacks

Recently I've shared several Excel animated charts that have been well received. These have been largely entertainment focused applications. Today I will show you how to apply animation to normal business charts.


animated_business_chart.gif

This animated GIF shows the business chart in question which is available for download at the end of this post. Last week one of Excel Hero's readers, Tom Quist, sent me a spreadsheet where he had made an outstanding first effort at the animation of a typical column chart. He wanted to animate between several records - let's call them divisions.

 
His technique worked, but was a little slow. So I showed him how I would do it and I believe he was enthusiastic with the results.

There are some points to note. If you add a shadow to the chart or (God forbid) make the series 3D, the animation will slow down significantly. Ditto for a background image to either the Plot Area or the Chart Area. The animation speed is heavily dependent on the number of data points in the chart. After a few dozen points, animation becomes dicey in Excel 2007. Making the series partially transparent will also slow it down, but only slightly (maybe not even perceptibly given the number of points in the chart). In some circumstances the transparency effect may be worth the tiny penalty.

The technique that I shared with Tom that day was to do a straight linear interpolation between the point values of one division to the next. This works well. I shared with him that it was also possible to implement EASING so the animation has a soft start and end. I did not have time to give him an example, but I hinted at how to do it. The animated business chart that I'm sharing with you today includes the option for easing, and you can see it demonstrated in the GIF loop above.



The example also moves the calculations for the animation off of the sheet and into named formulas. To me this makes sense as there is no reason to clutter the sheet with the calculations required to morph one division into another.

The chart allows you to switch between four divisions, change chart type, enable animation, and enable easing. You can also set the number of discreet steps to include in the morphing transition between divisions, which will be useful if you run the workbook on older versions of Excel. The older charting engine is so much quicker than Excel 2007's that you will want to increase the number of animation steps just to see it!

I've heard that Excel 2010 is supposed to improve charting speed. I'd love to hear how this performs on that platform.

This workbook demonstrates interactive dynamic charting, animated transitions including easing, and charting from named formulas. VBA is used only to loop through the number of animation steps that you specify, and to change the chart type.

An animated transition can add a certain amount of flare to your dashboard, but be careful not to overdo it. If used sparingly it can add a Flash-like level of interest, but remember that in and of itself, it does not add to the information the dashboard conveys.

I recommend studying the named formulas (CTRL-F3) and the VBA (ALT-F11) in the workbook. Nothing is protected and there are no hidden sheets. The knowledge gained can be used for much more than animation.

Here's the animated business chart.




And here's a list of other animated charts on Excel Hero:

 - Excel, A Presentation Platform (Number Spiral)
 - Lilac Chaser (Optical Illusion)
 - Stereokinetic (Optical Illusion)
 - Illusory Contours (Optical Illusion)
 - Breathing Square (Optical Illusion)
 - Enigma (Optical Illusion)
 - Two Sinusoids (Optical Illusion)
 - Perpetual Collisions (Optical Illusion)
 - Freezing Rotation (Optical Illusion)
 - Reverse Spoke Illusion (Optical Illusion)
 - Stepping Feet Radial Illusion (Optical Illusion)
 - Swimming Fish (Optical Illusion)
 - Mutually Interfering Shapes (Optical Illusion)
 - Kaleidoscope Motion (Optical Illusion)
 - Curry's Paradox (Optical Illusion)
 - The Haze Illusion (Optical Illusion)
 - The Koffka Ring (Optical Illusion) (Bottom of post)
Enhanced by Zemanta
If you liked this article, please share it!



Your Ad Here

No TrackBacks

TrackBack URL: http://www.excelhero.com/cgi-bin/mt/mt-tb.cgi/47

45 Comments

Daniel,

Excellent! I'm looking forward to getting back to the office to explore the Excel file. I haven't had time to fool with the easing yet, so I'll definitely be paying close attention to that feature. I really appreciate you responding to my initial question with personal assistance and by dedicating a follow up post where you've thoroughly explored and explained the "magic" behind this cool capability. You are truly an Excel Hero!

Tom

P.S. Good advice about not getting carried away with animations. I think this can be used tastefully in many situations.

Daniel,

Wow, the simplicity of this example is amazing.
I've learnt several small, but powerful tips from looking this over (named ranges in []! so simple, but its the first time I've seen it used). and there is yet more for me to get my head round later on... (SIN & PI range... what?!)

the way you combined the simple elements into a complex pattern is inspired.
Keep it up, I will definatley be looking forward.

Kudos too to Tom Quist for his original input.

Matt Cloves,

Welcome to Excel Hero!

What I try to do here is show how Excel can do much more than most people realize. I try to do so as simply as possible to maximize the learning potential.

Most of the techniques that I showcase have widespread applicability. In other words they are not just for making the sample where they are found. I like to make interesting examples to catch attention.

In my real job I'm a full-time freelance Excel developer, primarily for business applications. I have found great enjoyment by showing how the techniques I use everyday (and have learned over the past 25 years through trial and error) can be used not only in straight business type scenarios, but to craft beautiful creations in Excel.

The [] is a shorthand way to refer to a range. It is potent. It works for normal as well as named ranges. In fact, I think the topic deserves its own post - so I'll write one soon.

Regarding SIN and PI, please know that I've always felt that Microsoft's choice to call a defined name a Named Range was short sighted. In one of my first blog posts here at Excel Hero, I argued that they should be called Named Formulas:

http://www.excelhero.com/blog/2010/01/live-calendar-musings.html

This is because all defined namess are formulas and they are NOT all ranges. So if they are formulas, then they can use any of the built-in worksheet functions, plus user defined functions. One caveat to note is that all Named Formulas are in fact Array Formulas, and not the standard variety. That's right - a named range is an array formula! If you take some time to get your head around that, you quickly realize that Named Formulas are one of the most powerful and useful weapons Excel offers.

Regards,

Daniel Ferry
excelhero.com

Hi Daniel,

I am arun from India, I working as MIS. I saw your work and downloaded the same, It is mind blowing.. I also Replicate the same in new file as you did. But I did all thing but stuck in one task.. I made drop down list and graph but it is not selecting division wise data, if I select south or west it not working.. Can you help me how should I do this..? Please revert on it..

@Arun -

It's difficult to say without seeing your workbook (send it to my if you like), but make sure that you have the Cell Link set on the Control tab of the Format Control dialog for the drop list. It should be set to your record_index (mine was at "B37").

All of the named formulas are very important. Check that you have each defined properly (CTRL-F3).

Regards,

Daniel Ferry
excelhero.com

@Matt; thanks for the comment on using [...], I had no idea either that you could use that to refer to ranges in VBA. I always use Range("..."), up until now anyway :-)

@Daniel; thanks for the example. I don't think I'll use animation in reports very often but I'll definitely start using [...] in VBA for ranges. I'm looking forward to your post on the topic.

Just got back to the office and downloaded the file. Fantastic! Works very smoothly, and I'm impressed with your easing formulas. I should have paid better attention in my math classes! Thanks again for all of your help, and I look forward to my continuing education through this site.

B.A.U. = Beautiful as usual!

looking forward to apply it in my daily charts!

Using named ranges in vba seems quite powerfull indeed!

Congrats again!

This is really terrific - thank you for sharing it.

I am trying to modify yours to accommodate some of my data, and I have historically had a blank row in my column table to distinguish between FY data on the left and monthly data on the right. Using Tools->Options->Charts, I am able to not plot empty cells, giving me a firebreak. Your chart does not seem to be subject to that command, so that my empty cells are plotted as zeros. Any suggestions on how I might get around this issue, please?

@Ran -

I'll take a look at it, if you send me your file.

Regards,

Daniel Ferry
excelhero.com

@Ran, Have you tried putting a =na() in the blank column instead of leaving it blank

Very impressive - I am going to start incorporating more of these alternative visuals into my college lectures in the future because I feel that this is indeed the future of Excel.

This is great! It's going to mean I can cut down the number of sheets hugely so thank you.

My only query is how do I get it to chart off a pivot table which has a named range? I have tried a number of different changes to your file but can't seem to get it working right.

Is it possible?

@mark -

It should work fine with a named range from a pivot table.

Try to get it working on a normal named range first, then just substitute the pivot table named range.

Or, send me a copy, and I'll take a look.

Regards,

Daniel Ferry
excelhero.com

Daniel, ever thought about adding a few more features and sell it as a dashboard template?

Daniel - I apologize for not replying sooner; I seem to have missed the notification that you replied.

=NA() does the trick for the data at the end of my series, and for the chart in column view. When it is in line view, the line is drawn between the third and fifth column of data, when I desire no line. I tried stifling interpolation under options, but that was greyed out.

Thanks for any further suggestions. You are kind to help, and I am sorry to be slow in following up.

Hi Daniel,

This is really a fatabulous piece of work.. ...

I have tried to study this in detail and most of it is understood.. Please can you guide me on the SIN and PI thing?Since I did not quite understand the "easing" part. I am completely out of my trignometry.... maybe a link pointing me to the direction would help ? Thanks in advance... :)

This is perfect! Exactly what I've been trying to figure out how to do. Thank you so much!

How did you create a GIF from this? I want to include a GIF of my chart in a powerpoint presentation.

Thanks!

Hi, ScottT.

Glad you like it.

I use Camtasia Studio for screen recording. It allows you to save the recording in many formats. One of them is animated GIF.

Thats how I do it.

Regards,

Daniel Ferry
Excel Hero Academy

Hi Daniel,

Awesome work, I'm trying to get bubble chart or XY chart to work in a similar manner but had no success.

I tried to declare newX, newY and oldX, oldY because the value is in pairs. Am I on the right track?

Hi Daniel

Great piece of work - really inspiring!

Couple of questions - how do I expand the graph to take more than 12 points along the x axis. I've fiddled with the OFFSET attached to 'new' to go to column Q but the data isn't being displayed (and it listed as #N/A in 'old').

Is it possible to have different chart types working together? I don't mind removing the Change Chart Type controls / VB.

Thanks in advance :)

Hey Daniel,

This looks fantastic! Very inspiring! I do have a question for you on re-creating your results.

Although it seems that most people here are just modifying your sheet, I attempted to recreate it to understand the working parts more clearly. I designed mine to replicate yours exactly, using the same cells for the same data.

My problem is the chart! I am unsure what data you selected to get it going. My form controls do not have any affect on the chart. In your excel file, the chart advises the slected data range is too complex to show.

I've tried using no chart (to see if the code would generate one), a chart linked to B43:M46 and one using A43:M43.

Any help would be appreciated! Thanks!

First off, I figured out the answer to the problem I was having - I had not assigned the code to the controls, so clicking buttons didn't actually activate anything. *duh*

Secondly, I've been hard at work expanding your work into my own project. I came up with an ugly way to animate data bars, which I'd like to share. Maybe someone much smarter than I can figure out a way to consolidate it into something more elegant.

Data bars are the little conditional formatting option that ranks a set of values, and shades in a portion of the cell to illustrate how close it is to the highest in the range.

Strictly speaking, the data bars do *not* animate like the chart does, because they do not adjust until the values pass one another. For example:

A1: 1
A2: 5

Changing A1 to 5 and A2 to 1, the bars do not slowly trickle down; they jump to their new positions at 2.5, when they pass each other.

HOWEVER.

This is only super-apparent when you're only comparing two cells. If you're comparing 9, for example, then it looks more animated, as figures jump around.

The only way I could figure to create this effect was to create several copies of certain named ranges. This is because I was unable to get [chart_values] to display more than just the first point in its set. **(So if someone knows how, please please tell me!)**

So I added [chart_values02] = old02-(old02-new02), etc... In total you create new iterations of [old], [new], [chart_values] and [switch_to_record].

I created a column of numbers for each data bar I was going to create. The OFFSET in each [new] is the cell right above each column; for example:

[OFFSET links here]
1
5
6
9
Etc.

My implementation of your code utilizes several Command Buttons instead of a drop down (aesthetic choice purely). I simply added the following lines after their dopplegangers:

ThisWorkbook.Names.Add "old", [chart_values]
ThisWorkbook.Names.Add "old02", [chart_values02]
Et cetera
..........................
ThisWorkbook.Names.Add "switch_to_record", [record_index].Value
ThisWorkbook.Names.Add "switch_to_record02", [record_index].Value
Et cetera

Then to wrap it all up, in the worksheet itself:

A10 =chart_values
A11 =chart_values02
A12 =chart_values03
A13 Et cetera

Last word: I changed the format of the data bars to Number with no decimals, so you can see the data bars moving more quickly.

Thoughts?

Hi Dany

Please tell me how to transfer these animations to powerpoint

AWw I'm new to all this but very excited by the prospects - and I know the boss will be too! I cant try to alter the data in your chart, but I want to be able to replicate the chart to cover many data aspects in a "dashboard" style. However, the chart data source is referred to as "chart_values". How can I see these values so that all I need to do is replicate all other aspects for a new chart and then point that new chart at another set of data..... or am I being far too simplistic!? Any help, much appreciated!

Steve

Hi Daniel,

How did you use the animation, you say animated gif....what do you mean by that.
Can you explain how the animation is done please.....

Mustafa

Just found this site.

Daniel, truly amazing work! Like art. Beyond elegant.

I was playing with this example and it's too much fun -- the challenge: play with the EASING formula (say, make it 8 * SIN() instead of 2 * SIN() ) then you get four "pauses" in the animation.

Could be used to make something that "stops/pauses" at each quarter. Heck, you could even change the graph color at each transition...

Thank you Daniel for the enlightenment.

What a fantastic use of VBA, this will really add a lot to dashboards.

I'm doing some testing on this workbook, to control the row count, its just a matter of adjusting the drop down & title control, but I'm having a difficult time adjusting the number of columns to be displayed in the graph.

Any suggestions?

Brian

Lots of fun figuring this out.

But I'm stumped as to the purpose of the private module-level bFinished boolean. Commenting it out doesn't seem to have an impact. Do you use it for debugging purposes to halt the loop if the graph is taking too long to animate?

Hi:
Just found your blog - the animated charts are a great visual. One quick question; can the chart be pulled into Power Point?

Many thanks,
C. Beringer

Hi, Bob.

The bFinished boolean is there as a global semaphore. It is like a road sign to tell the code if the animation is running currently or not.

This is important when the user attempts to change the to a new record (North, South, East or West) while the animation is already in process.

Hi, C. Beringer.

You raise an excellent question.

I'm certain that there must be a way to use this type of animated charting in PowerPoint. However, I know next to nothing about that program.

It would be an interesting exercise!

Hi,

This is wonderful to present to management for reporting as dashboard.

I dont know VBA. But i want to do this, Is it possible for you to give steps to me.

Appreciate your help.

My Days at job search is always challenging and so with good to fine this blog. Not like the other sites out there with information on them with a ton of junk it's nice to find a blog whose admin takes the time to create good materiale.

I work at gold coast builders site and I would like to thank you for sharing this one. It would helps a lot in my reporting jobs. This way I could give them a different way of reporting which they won't find so boring.

Hi Daniel,

As somebody who delivers training on how to create Excel Dashboards I don't often find anybody who inspires me. Much of what I read online and in books I already understand, but you truly inspire me and I'm finding so much I never even contemplated.

Your examples are amazing and each time I learn something new.

Thank you.

Great Work indeed. Can you plz help me how can i use this chart with same dynamic function in my Outlook 2007 message body to send it to anothenr outlook user. Thanks

Awesome work, I'm trying to get bubble chart or XY chart to work in a similar manner but had no success for my free job postings website.

Dear,
Superb. Very nice. Mind blowing. What I have to do to send to my manager with the same effect. Pls guide me. I have a query If I am moving the chart to separate chart window the animation is not working what I have to do. Pls help me.

Hey all,

So I have been reworking this a number of times now but I still come up against the same problem.. I can not extend the data range past 12 months.
I can not find the [chart_values]information anywhere? All i get with the edit of the Offset in #NA for however many new months I offset by.
This has me stumped, any one able to help?

cheers

Hang on I got it, I edited the Old named data as it appears on ctrl +f3, I deleted all the named values and replaced with the required number of intergers, i.e. and extra 12 months, so an extra 12 randon values. Saved then refreshed the graph and its now plots 2 years. This is such a good website keep up the excellent work Daniel

Amazing new features from Index(). I have been using index(match()) more often lately but I still resort to vlookup for large data sets as the I-M combo tends to slow processing speed down to a crawl. Strange. Nevertheless, excited about this new knowledge and more that awaits in EH4

Hi

Just found this blog and post. Downloaded the file into Excel 2013 and upped the animation steps to 200 and its a very smooth transition between the region data. Very Impressive!

Did you ever write your post regarding using [...]. As a VBA coder and trainer myself I dislike it but I would be interested in your point of view. Your use of names in Excel for arrays and formula I really like and will be playing with.

Thanks for a fun educational post

Laura GB

Hi Daniel,

Thanks for the very creative and simple Excel animation technique. I downloaded this and modified it within the shell you created to make a visualization similar to 'Gapminder' (http://www.gapminder.org/).

I'm trying to have my chart use three different series now to show different categories over time, but the named formula 'old' keeps replacing the arbitrary values with errors. I've only encountered this issue once I tried to add new series to the chart within this tool.

Do you have any ideas as to why the VBA code and or named formulas would not accommodate new series being added to the chart?

Thank you!

Gavin

Leave a comment

About this Entry

This page contains a single entry by Daniel Ferry published on May 26, 2010 12:52 PM.

Interactive Advanced Excel Chart was the previous entry in this blog.

Excel Optical Illusions #15 is the next entry in this blog.

Find recent content on the main index or look in the archives to find all content.