Data Visualization News

Animation, Interaction and Dynamic Excel Charts

admin — Fri, 20 Nov 2009 16:18:12 +0000

If you want to add animation to your charts that’s a clear sign that you have too much free time. Go out and play with the kids instead.

Yes, animation is a powerful attention-grabber, even more powerful than a glossy 3D pie chart in Crystal Xcelsius. And yes, it can actually be helpful (from time to time). But.

A good example of animation in data visualization is the famous Hans Rosling’s TED presentation, where a long-term pattern is clearly seen (at min 4:00):

This works well because the trend is easily identifiable and you don’t care much about the details. It would be much more difficult to make sense of this data if there were multiple trends and short-term variability. (More on the use of animated charts.)

After watching this presentation people often ask me: “Wow! Can we do that in Excel?” Wrong question. The right question would be “Wow! Can our data do that?”.

You can always create a simple animation in Excel but it’s hard for a non-programmer to get a smooth transition effect. Here is an example:

Although you can see a pattern emerging, you would need to add a complex interpolation routine to make it look better (read Jon’s post to see how a simple interpolation can be used).

Animation is better used if there is a pattern to be discovered, but you need something more: the ability to interact with the data. You must be able to stop, go back, get the details. While you can create a simple animation effect with a for/next loop in VBA, interaction with the chart is much easier using a Google spreadsheet with a motion chart:

(This is just an image. Click here to play with the chart.)

Dynamic Charts in Excel

In Excel, instead of creating a VBA routine, consider using a scroll bar linked to the value you want to change (year, for example):

Using a scroll bar adds some level of interaction because you can scroll back and forth, pause and examine the details for a specific year. Obviously, you need to change the chart data source dynamically.

Let’s recap how to create a dynamic chart in Excel. You can do it by changing values or by changing the data source itself (using a different range).

Option #1: Copying the Data

The range A1:E11 is our data set and we are comparing regions. Moving the scroll bar at the bottom changes the year. Column F is our data source. You can enter this formula there:

=OFFSET($A$1,ROW()-1,MATCH($A$13,$B$1:$E$1,0),1,1)

ROW() gets the current row and MATCH() returns the position of value 2002 (A13) in the range B1:E1. So, the formula in cell F2 reads something like this: start at A1, go down one row, go right three columns and get the data in that cell (range of width 1 and height 1). When the user selects a different year the data is copied to column F.

Option #2: Using a Dynamic Named Range

The second option is to create a dynamic named range. Create a named range an instead of entering a fixed range enter this formula:

=OFFSET(Sheet1!$A$1,1,MATCH(Sheet1!$A$13,Sheet1!$B$1:$E$1,0),10,1)

As you can see, it is very similar to the one above, but now the number of rows down is fixed (1) and it returns a range of 10×1 cells (because we have 10 regions, but if the number of regions varies you may use COUNTA(A:A)-1 to count the number of regions, excluding header – and don’t forget to move the year to a different cell…).

When you verify your range this is what you get:

You just need to use this range in your chart (when entering the range you must add the workbook name: “=Book1!SourceData”). You’ll also need a range for the category axis labels:

=OFFSET(Sheet1!$A$1,1,0,10,1)

You can download the spreadsheet here.

Take Aways

If you want to animate your charts make sure you do it because it adds value, not because you want to show off your skills. You can do it in Excel but it looks much better in Rosling’s Trendalyzer or in the Google spreadsheet gadget.

To create animations your data source must change dynamically, and that requires some work (and skills). I advise you to shift your focus from animation to interaction and use all that work to design a better user experience. Do you prefer a “wow!” (animation) or a “wow, thank you!” (interaction that actually helps the user)?

(If you are interested in dynamic charts consider getting my dashboard tutorial. You’ll find many examples there in the context of dashboard design and implementation.)

______________________

Want to create better dashboards? Try the Excel Dashboard Tutorial.

Post from: Jorge Camoes’ Charts.

Animation, Interaction and Dynamic Excel Charts

No related posts.

Related posts brought to you by Yet Another Related Posts Plugin.

Better Color Palettes for Your Excel Charts [Video Tutorial]

admin — Fri, 20 Nov 2009 16:17:31 +0000

You need a better color palette for your Excel charts, but you are a mere mortal and your artistic skills are less than stellar. Hell, you can’t even choose the right tie for a suit! So, what do you do? (hint: watch the video below)

Maybe we could ask Edward Tufte for advice. In Envisioning Information, he writes:

What palette of colors should we choose to represent and illuminate information? A grand strategy is to use colors found in nature (…). Nature’s colors are familiar and coherent, possessing a widely accepted harmony to the human eye (…). A palette of nature’s colors helps suppress production of garish and content-empty chartjunk.

Better said than done, right? Well, let me tell you a secret: it’s easier than it seems.

How to create a new Excel color palette

I’m going to show you how to create a palette of colors found in nature ( you can create a palette of colors not found in nature, if that suits you better). Here are the steps:

Select a photo you like. If you happen to live in a big city, finding nature can be challenging. Google for “[your location]+park” and you may get lucky. Go there, take some photos and download them to your computer. If that looks like a lot of work, you can try Flickr. Search for “autumn colors” and you’ll get some useful results. I’ve picked the one on the right;
Upload it to an online palette generator. There are many, so choose whichever you like. I like Genopal because it is clean (no ads) and extracts exactly the number of colors we need (eight).
Convert color codes to RGB. Copy the color codes to Excel and use the HEX2DEC() function to get the RGB values.
Create the Excel palette. In Excel 2003, replace the existing colors with the new ones. In Excel 2007 create a new color theme.

This is it. You can try it in your next chart.

A new Excel palette: the making of

If you need the details, here is a step by step tutorial:

Is this good enough?

As you can see, the results are heavily dependent on the photo you choose, so you should try different photos and test the resulting palette in a chart. If you find it hard to come up with a good color scheme, you can use this method to create a basic color palette that you can tweak to meet your needs.

Other Excel and Chart Color Resources

Jon Peltier wrote two interesting posts about managing the Excel color palette (here and here) and Stephen Few shares some Practical Rules for Using Color in Charts (PDF). Bonavista’s Chart Tamer includes a professionally designed palette.

Photo credit: Xavier Fargas

______________________

Want to create better dashboards? Try the Excel Dashboard Tutorial.

Post from: Jorge Camoes’ Charts.

Better Color Palettes for Your Excel Charts [Video Tutorial]

No related posts.

Related posts brought to you by Yet Another Related Posts Plugin.

Better Color Palettes for Your Excel Charts [Video Tutorial]

admin — Fri, 20 Nov 2009 16:17:00 +0000

Maybe we could ask Edward Tufte for advice. In Envisioning Information, he writes:

What palette of colors should we choose to represent and illuminate information? A grand strategy is to use colors found in nature (…). Nature’s colors are familiar and coherent, possessing a widely accepted harmony to the human eye (…). A palette of nature’s colors helps suppress production of garish and content-empty chartjunk.

Better said than done, right? Well, let me tell you a secret: it’s easier than it seems.

How to create a new Excel color palette

I’m going to show you how to create a palette of colors found in nature ( you can create a palette of colors not found in nature, if that suits you better). Here are the steps:

Select a photo you like. If you happen to live in a big city, finding nature can be challenging. Google for “[your location]+park” and you may get lucky. Go there, take some photos and download them to your computer. If that looks like a lot of work, you can try Flickr. Search for “autumn colors” and you’ll get some useful results. I’ve picked the one on the right;
Upload it to an online palette generator. There are many, so choose whichever you like. I like Genopal because it is clean (no ads) and extracts exactly the number of colors we need (eight).
Convert color codes to RGB. Copy the color codes to Excel and use the HEX2DEC() function to get the RGB values.
Create the Excel palette. In Excel 2003, replace the existing colors with the new ones. In Excel 2007 create a new color theme.

This is it. You can try it in your next chart.

A new Excel palette: the making of

If you need the details, here is a step by step tutorial:

Is this good enough?

Other Excel and Chart Color Resources

Photo credit: Xavier Fargas

______________________

Want to create better dashboards? Try the Excel Dashboard Tutorial.

Post from: Jorge Camoes’ Charts.

Better Color Palettes for Your Excel Charts [Video Tutorial]

No related posts.

Related posts brought to you by Yet Another Related Posts Plugin.

Cooking Better Charts

admin — Fri, 20 Nov 2009 16:16:44 +0000

I don’t know how to cook and, frankly, I don’t want to learn. But my wife is coming late from work, I’m tired of take-away food and we must feed the kids. So I guess I have to add cooking to my daily routine.

Here in Europe, Thermomix (also known as “Bimby”) is a very popular kitchen appliance (specially among men, so I’m told). You just add ingredients, press three buttons (temperature, speed and time) et voilà, diner is ready (sort of). Follow the recipe and what you get what you expect. That’s good enough for me.

This is a gadget most people love or hate. A friend of mine hates it and tried to persuade me that I should learn how to cook using the traditional pots and pans. Only ill-informed people buys it, he says.

Yes, but he’s single. No kids, no blog, loves to cook. How can he possibly understand my motives? I just want to get this thing done with minimal fuss, no random results, small learning curve. Some good templates, that’s all I want.

I had to buy one. I just did.

OK, but what about making better charts?

Apparently, most people love making charts as much as I love cooking. Even if they need them on a daily basis, there are so many things fighting for their attention that the need for a better data visualization can easily be overlooked and pushed to the bottom of their list.

Junk food, junk charts. Some times it’s great fun to use them. And it’s very easy to take them for real food, real charts. But they are unhealthy, and if you use them often you and your business will pay a price, sooner or later.

It’s not about food or charts, it’s about business and personal health (or wellness). You don’t have to know the details, you don’t have to know how to use the tools. But you have to have some kind of framework to guide you. Fat is bad, sugar is bad, 3D effects are bad. Then you need to find some templates that implement that framework and tools that use them by default.

We played with the machine over the weekend and I’ve very pleased with the results. We eat better than we usually do and saved money and time. Not bad. Now, do yourself a favor and find your own charting machine. If you’ll excuse me, mine is calling me. Dinner is ready.

______________________

Want to create better dashboards? Try the Excel Dashboard Tutorial.

Post from: Jorge Camoes’ Charts.

Cooking Better Charts

No related posts.

Related posts brought to you by Yet Another Related Posts Plugin.