Posts tagged as:

Excel Charts

Animation, Interaction and Dynamic Excel Charts

by Jorge

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 from my dynamic Excel dashboard:

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 and, 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)?

{ 3 comments }

Better Color Palettes for Your Excel Charts [Video Tutorial]

by Jorge

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:

  1. 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;
  2. 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).
  3. Convert color codes to RGB. Copy the color codes to Excel and use the HEX2DEC() function to get the RGB values.
  4. 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

{ 5 comments }

Are Excel Charts Hurting Your Business? 10 Mistakes You Should Avoid.

by Jorge

Are Excel charts, and Excel in general, a commodity, with no competitive advantage? Only if you want it to. Why? Because a vast majority of users:

  • Have the data analysis skills of a toddler (or less);
  • Can’t go beyond chart defaults;
  • Functions? They know how to click the SUM() button;
  • Don’t know what a dynamic chart is;
  • Think pivot tables are too complex;
  • VBA? No way!

I believe you can squeeze some competitive advantage out from Excel if you avoid some basic mistakes everyone else makes. Here is a very short list (some generic, some chart-specific).

#1. Assuming that Excel Can Do It.

Because a spreadsheet is such a loose environment, people often assume that all things numeric can be done using Excel and that no specific skills are needed. Using Excel as a database tool is an obvious example. If you can’t stop singing Ode to Joy every time you see one million rows in Excel 2007 that’s a clear sign that you are on the wrong track.

#2. Assuming that Excel Can’t Do It.

Most people aren’t aware of how powerful Excel is and use it almost as a pocket calculator. If you routinely have to manage quantitative data, learning a little more always pays off. As an example, you can use it to create complex executive dashboards or, at least, as a dashboard prototyping tool.

#3. Not Having a Go-To Person.

Not everyone needs to be an expert, but having someone that understands the business and proactively tries  to find better ways to perform common (or not so common) tasks should be a requirement.

#4. The Excel Islands.

If you have a data source you can connect your sheet to, do it. Don’t make the mistake of copy / pasting the data into your sheet. That’s a bad practice and it can undermine your organization’s entire information system.

If you connect your spreadsheet to the data source (via ODBC, for example) you are ensuring the integrity of your data and minimizing maintenance costs. If you are creating a new table, use a simple database tool like Access. And if you really, really, need to use Excel, make sure you create a table that can easily be exported and used by a different tool.

#5. The Uncharted Archipelago of Excel Workbooks.

Search for “book*.xls” in your hard drive. How many do you find? Get rid of all those useless files now.

#6. Bypassing IT.

We all have our little fights with IT, but we must understand their role and the reasons behind their actions sometimes make sense… Excel users often lack knowledge to understand data structures, data access, documentation, security (IT 101, basically). If your IT don’t want to conquer the world and actually helps users, you should work hand in hand with them.

#7. Not Using VBA.

I’m sorry to break this to you, but if you spend your time analyzing data and Excel is your primary tool, then you must use VBA. I’m not telling you should be a programmer, far from it. But you should be able to record a macro and make some simple tweaks. There are many repetitive tasks that can easily done using three lines of code.

#8. Excel is the only charting tool.

There are no neutral tools. They force upon you a certain way of doing things that you may not be comfortable with (or you shouldn’t). The Chart Wizard is one of the stupidest wizards I’ve ever met. If you need to spend time removing defaults there is something wrong with the defaults. Excel charts emphasize:

  • Stupid Defaults versus Cleaner Formatting Options
  • Static versus Dynamic
  • Marketing versus Insights
  • Disposable versus Reusable
  • One versus Multiple
  • Large versus Small
  • Island versus Continent
  • Presentation versus Discovery

I’ll detail this in a future post but, like PowerPoint, Excel charts do have their own cognitive style. If you want to do things differently you can, but you are choosing a path of endless pain. So, give some users the option to use a different charting tool or, as a bare minimum, use a better wizard.

#9. Forgetting the 3R’s.

Reduce, reuse, recycle. Yes, I’m talking about Excel charts. How many disposable charts are cluttering your hard disk? Go green when creating your charts:

  • Charts are typically much larger than needed, so you can safely reduce their size;
  • When you create a chart make sure that it can be reused when the data updates;
  • A chart for Market A is probably very similar to a chart for Market B. By recycling the chart from market to market you don’t need do create new charts and pollute your hard drive.

#10. Not Making Things To Work Together

Lists like this one are useless, unless you know how the basic system works. Try to see the big picture and it will be much easier to understand where each piece belongs to:

  • If a function needs a range address, you will know that another function can provide the address (first step to create dynamic charts);
  • If you are repeating the same stupid task, you’ll find a way to automate it;
  • etc.

Bottom line: make sure you know what you should use Excel for, find inefficiencies and give power users the option to use specialized tools.

{ 11 comments }

Excel Charts: If It Isn’t Broken…

by Jorge

I humbly accept our business visualization reality: 90% of all business charts are created in Excel and most business users are unwilling to learn yet another application and go through all sorts of hassles (data management, compatibility issues, file sharing) just because a different product offers a bigger chart gallery and some obscure defaults no one cares about (except the experts).

You don’t take driving lessons in a Ferrari, specially if you don’t know the difference between a Ferrari and a Fiat. Why would you want Tableau or Spotfire if all you want and need is a pie chart?

If we want to make users more curious about the way they use their data, if we want to persuade them that 3D charts are useless, if we want them to adopt what we believe to be information visualization best practices, we must start with the application the users are familiar with.

It would be great to have a better tool in every computer, but we don’t have it. What we can do is to undermine it from inside, by educating the users and showing them how mediocre the tool is.

If done right, this should be visible in market surveys, sooner or later. And Microsoft will react to that, hopefully. There is no other way.

Leaving the Excel Flatland

That said, we can agree that Excel’s chart gallery is notoriously poor, defaults and options a nightmare, and the implementation of advanced visualization techniques a pain in the ass. And while you can argue that most new chart formats are outperformed by traditional ones (and a large majority is) you cannot assume that the plain ol’ bar chart will be here forever. We are using XVIII century visualization tools to display XXI century data. Things change very slowly indeed.

We cannot understand information visualization if our worldview is framed by the Excel chart gallery. Yes, we can recreate in Excel some formats that we see in other applications, but something is always lost in translation. Like in this song:

Brilha, brilha lá no céu
a estrelinha que nasceu.

You may know it as:

Twinkle, twinkle little star
how I wonder what you are.

The music is the same, but the user experience is much different (specially if the user doesn’t know Portuguese…).

R

Kelly O’Day over at ProcessTrends if offering the Learn R Toolkit, target at Excel users. I’ve just bought it. Why?

  • Because I like the idea;
  • Because I’m curious about creating charts using a programming language;
  • Because it’s not easy to create this in Excel, no matter how hard Jon tries :) .

Now, let me be frank. I don’t believe that regular Excel users would want to use R. If recording simple macros are outside their comfort zone, an application that requires programming even to create a basic chart is clearly out of limits.

R seems to require a lot of programming to create more advanced charts. Surely you can create a bar chart with three lines of code, but that’s the equivalent of

print "Hello World!"

Not very useful, but I’m sure Kelly’s Toolkit will encourage me to explore and break some handcuffs.

I don’t want to write a blog about Excel charts. I just want to write about making sense of business data. The tool shouldn’t matter (but it does).

__________________

{ 11 comments }

The consultant's chart: a dilemma

by Jorge

Consulting chartHow do you sell your outrageously expensive consulting services? Simple, just add a chart…

Not every chart will do, of course. Let me outline some basic design rules of what I call a “consulting chart”:

  • It shouldn’t be recognizable as a standard chart that you could create in Excel;
  • It shouldn’t use popular eye-catching design elements, like 3D or textures (hire a designer if you really want those);
  • It should convey an impression of complexity but make everyone feel smart because they could actually understand it;

Conventional visualization rules do not apply to this kind of charts. It lays between a proper chart and an icon or a logo. Always remember that your report may be 300 hundred pages thick, but only this chart will be shared in internal presentations, and the managers (your clients) should be proud of presenting it (money well spent).

If you don’t feel creative today, just use the chart above. Every consultant likes it. A circular chart or a radar variant is always a good bet. Each color represents a dimension, and there is a set of five indicators for each dimension. A seven-point scale is used to measure each indicator.

As an example, a dimension could be “Human Resources”, and “turnover” one of the indicators. Display two of these charts side by side to compare “We” vs. “Them” or “Today” vs “Tomorrow”.

Here is a dilemma…

Strictly speaking, a simple bar chart would do a much more efficient job at displaying the data and letting the users compare multiple series.

But let’s be completely honest here. You know your client, and you know he will happily spend 12,000 for a report with these charts. If you use a regular bar chart you can’t ask for more than 10,000. What would you do? Share your thoughts in the comments.

{ 15 comments }

Excel chart gallery: a difficult equilibrium

by Jorge

Excel Chart Galley

{ 17 comments }

Excel, Crystal Xcelsius, dashboards and domain names

by Jorge

Funny story… I was playing with domain names today, trying to register names for future projects like “exceldashboards.com”. Unfortunately, this is already taken. And who owns it? Business Objects, the makers of Crystal Xcelsius (remember my Xcelsius dashboard series?). Type exceldashboards.com (I refuse to add a link) and you’ll be redirected to the xcelsius home page where there’s a tag line that reads “steal the show”…

After mumbling things unpublishable, I typed “xcelsiusdashboards.com” and lo and behold, it was available! Seems that BO don’t believe in their own dashboard products…

Steal the show, they say…

{ 2 comments }

10 x 10 Tips to improve your (Excel) charts: formatting

by Jorge

This is the second of 10 posts where I’m listing tips for better charts. Please take a look at the first post where the project is discussed. These are my chart formatting tips:

  1. Use the right chart type for the data and the problem;
  2. Apply sound design principles;
  3. Use color strategically: mute axis and grid lines by graying them out; gray out some contextual data also; use soft colors; use saturated colors sparingly and with a clear purpose of emphasis;
  4. What the users see is not what you see in your monitor: if needed, test for other monitors and output formats (b&w print, color print, PDF, overhead projector);
  5. There is no rational justification to use pseudo-3D charts and other dubious effects (gradients, glow…), so never use them if you what to be rational;
  6. Use a clear font;
  7. Don’t emphasize everything (for obvious reasons);
  8. The y axis scale should start at zero; this is particularly important if you are using bar charts; make sure you have a good reason to break this rule;
  9. A chart is not a table: by labeling every single data point you make it harder for the user to search for trends or patterns; if you have to, place the labels where they can do no harm;
  10. Annotate: Add labels for the last, the lowest, the highest or any other relevant data point; add data or comments where appropriate;
  11. Bonus tips: Use smaller charts and never accept the Excel defaults;

What are your best chart formatting tips? Please share them in the comments!

{ 5 comments }

10 x 10 Tips to improve your (Excel) charts: general tips

by Jorge

This is the first in a series of 10 posts where I’ll suggest a (hopefully) coherent set of tips to improve our charts and, more important, to improve the way we make sense of the data. These are the planned posts:

  1. General charting;
  2. Formating;
  3. Column/Bar charts;
  4. Line charts;
  5. Scatterplots (XY charts);
  6. Pie charts;
  7. Other chart formats;
  8. Dynamic charts;
  9. Dashboards;
  10. Miscellaneous tips;
  11. Bonus post: online resources.

These will be very short tips, and I’m sure some of them will require further explanation. In time, all of them will be properly linked to a more detailed post (that will keep me off the streets for a while…). This is a kind of road map for the next few months and, when finished, a (partial) table of contents for the blog.

So, general tips on charting:

  1. A chart shows trends, patterns, outliers; if you already strive to make them apparent, you don’t need to read the next 99 tips…;
  2. Do you really need a chart? Sometimes the task and the data suggest another method of data analysis;
  3. Know your audience. If your audience is uncomfortable with some formats your message will be lost;
  4. Make sure you have enough data to create a pattern (two data points are not enough to create a trend line);
  5. Make sure you don’t have more than enough data: just because you have it, you don’t have to show it…; keep removing interesting data until only relevant data for your problem remains;
  6. A chart should be able to answer elementary, intermediate and global questions regarding the data;
  7. Don’t assume that the charts you see in the media are the ones you need to run your business;
  8. Learn how to lie with charts and, of course, avoid those lies;
  9. Let the reader see related charts simultaneously;
  10. Chart overload is as bad as information overload.

It’s your turn, now. What are your best tips in this category? Please share them in the comments (if you have specific tips please save them for the next posts). I believe that Tufte’s principles (avoid chart junk, maximize data/ink ratio, high data density…) are already implicit in some of these tips.

{ 7 comments }

The Excel Demographic Dashboard in StarOffice/OpenOffice? Not yet.

by Jorge

I tried to create a Crystal XCelsius version of my Demographic Dashboard and failed miserably. But what about a new spreadsheet version? That should work, right? Wrong. I downloaded StarOffice 8 and just linked the pivot table (“datapilot”) to the Access population database via ODBC and waited… and waited… and waited… and finally was able to design the pivot table structure and then I waited… and waited… and waited… and then I selected a new country and I waited… and waited… and waited…

I know I must upgrade my system, but this thing is ridiculously slow. So, I’ll wait a little longer… for version 9.

{ 0 comments }

← Previous Entries