Excel Charts Blog

A week ago, my father underwent a risky surgery. The doctors weren’t sure if they should do it, given his old age, but we all agreed that letting the disease take its course was not an option.

After a week, he seems to be recovering well. He was in the ICU for three days, but that seems to be a normal procedure in these cases. He may be home early next week. The doctors are impressed.

He was healthy his entire life (this was his first serious surgery). Genetics play a major role here for sure, but there is something else: he likes to walk.

He walked his entire life, never bought a car, never took a bus to/from work. It’s easy to estimate how much he walked over a 27-year period, when we were living in the same house and he was working in the same factory: around 52,000 kilometers (32,000 miles)! That’s more than five round trips from New York to San Francisco. Walking. And he kept walking after retiring.

In an era of instant gratification and no-time-to-exercise, my father is reaping the rewards of a lifestyle he chose many, many years ago.

Funny thing: he was unaware of this, and neither did I. I was just trying to cheer him up before surgery and came up with this estimate. Now we are playing with the data, converting 52,000 km to a more manageable unit (130 round trips to his home town), finding his longest walk… This is something that he’s proud of and likes to talk about.

And you know what? I didn’t have to make a chart, I didn’t have to add junk, I didn’t have to sell my findings. Just a perfect match between data and audience. I will never have that smile from my audience again.

Now, if you’ll excuse me, I have to follow my father’s steps (literally). I’m already 49,000 kilometers behind him.

{ 7 comments }

Dmitry asks:

I face an issue that has nothing to do with Excel itself: human resistance. I showed a dashboard to my manager and he answered to me that my dashboard is too difficult for him and for top management and insisted to use simple XL-tables with lots of data. I’m a bit confused, why such innovative approach is not accepted by the management? Have you faced with such issues in your professional life?

I know how you feel, and I’m sure many readers do, too. It’s not about the dashboard,  its about change. Everyone resists to change. Often change threatens our believes or, more selfishly, our status and lifestyle. It is a complex issue, and there are no simple and actionable answers. Let’s see what can go wrong.

The Pygmalion Syndrome

If people resist changes you are promoting, you may need to look in the mirror and yourself “what am I doing wrong?”. This one is hard to swallow, I know. You spend weeks or months working hard and you fall in love with your creation. Then nothing happens. People fail to realize how beautiful the creature is.

Breaking news: they actually couldn’t care less. Let me be brutally honest: if you are an Excel expert, chances are you are at the bottom of the food chain and you know much less about the business than you think you know.

What you should do:

• Ask the right people the right questions;
• Study internal presentations and find how people look at the data;
• If you are using charts and other people are using tables, you should find patterns they overlooked (if you don’t, try harder);
• Come up with a vision and share it; show how a dashboard improves current processes;
• Make a draft and ask for feedback;
• Make sure you know the difference between what users say they need and what they really need;
• Find common needs and translate them into a dashboard that improves what they already have and gives them something more (don’t promise the moon);
• Work with the users, make sure you deliver what they need but, at the end of the day, it’s your dashboard, not theirs (you’re the expert, and you’ll be harshly reminded of that case something goes wrong).

You are allowed to fall in love with your creature if, and only if, you understand that the creature is the whole process, not the worksheet.

Inertia vs. Innovation

We often have to manage more than we can handle. We can offload some of the burden by delegating and/or by switching to auto-mode. Routines are great time and energy savers but also inertia-inducers. Sure, many users are open to innovation, provided they don’t have to move their asses off the couch. Don’t underestimate inertia.

To overcome inertia, you need at least an initial force. If you can’t sell the benefits of your dashboard the users will not move an inch.

Many users fail to understand that a better chart is much more than a design thing, a prettified chart with no inherent added value. That’s why you must teach them. A before/after comparison can be very useful, but it must be a guided discovery:

• If they are using tables, show them how they can process much more data using charts;
• If they are using Excel chart defaults and 3D charts, show them how simple formatting changes can help them get insights faster;
• If they are using a single chart, show them how multiple charts (small multiples, dashboards…) can improve their analysis;
• If they are using static charts, show them how they can take advantage of their business knowledge to explore the data dynamically.

Always remember: if they have no compelling reason to move, they will always find a perfectly acceptable reason to keep things as they are. Provide a clear path, and make sure nothing important is left behind.

Office Politics

Well, you can’t avoid it, can you? Office politics (all kinds of politics, actually…) have a deservedly bad reputation, usually because of some rotten apples.

If you are un obscure geek with a great idea you are doomed. If a product manager that knows how to grow her connections has the same idea, then she will probably succeed whereas the poor geek doesn’t even know where to begin…

If your network is weak, you should plan way ahead. Remember: most people don’t even know that charts and dashboard can actually be useful, and not just a (hopefully) pretty way to illustrate some numbers. So, you should start raising awareness and curiosity.

Target some standard reports that could be vastly improved and create alternative designs (Excel is a great prototyping tool). Tell users something like this: “Try to forget everything you know about the market and tell me what you can learn from [current report]. Now, let’s try again: what can you learn from [your design]?”. You’ll get great feedback and win people’s attention.

Find a sponsor. Share your vision and focus your message on the benefits for the organization. Quantify them, if possible. Avoid technicalities, no one cares.

Charts Are Not Serious Enough

Many managers believe that charts belong to PowerPoint presentations (there are many misconceptions about charts), and they will not use them for a more serious work. Hard figures with lots of decimal places feel a lot more credible, and who can blame them after three hours of flying pie charts?

Let me reiterate this: descriptive statistics, tables and charts are examples of tools we use to make sense of our data. They all have their strengths and they should all be taken seriously.You don’t miscalculate an average and you don’t design a misaligned table just for fun. Applying a 3D effect (or anything else) that hides or destroys a pattern in a chart do not add credibility and trust. You may get the eyeballs, but the brain is somewhere else.

They Are Not Visual

Old-school managers like tables and it’s hard to change the way they approach the decision making process. And there are non-visual people. People that love numbers, sound, motion, whatever. You can’t force charts upon them. But you can use a table.

Designing a good table is a lost art. Learn again how to sort rows and tables, how to label them, how to add meaningful calculations. And even if a table is the primary object in your report, you can always add graphical objects to help the reader. Design a table-on-steroids with sparklines, conditional formatting and links.

Change is Slow

Changing other people’s behavior is a slow and hard process. They’ll keep falling back into the old routines, even if they agree that the new ones are better. Make sure people understand the benefits and keep asking and giving feedback. If they don’t feel threatened change happens much faster.

I’m not a sales person, and trying to sell something is clearly outside my comfort zone, but I recognize that, at the end of the day, what really matters is not the product itself but how people’s lives are improved by the product. And you have to tell them…

So, what do you think? How do you create a market for your dashboard? What should Dmitry do?

{ 12 comments }

There are so many bad charts published every single day that redesigning some of them would be a full time job. Many are involuntarily funny. And there are some dangerous and stupid ones.

I live in Portugal, a small country that only gets international press coverage when something goes wrong. Like now. Public debt is a serious problem, and, judging from this chart, we are doomed:

What can we do when debt starts skyrocketing by 2015? Apparently not much. Unless…

Unless there is something wrong with the chart. Let’s take a closer look at the x axis:

The scale is not proportional, and that explains the exponential growth. Now, let’s correct the scale and compare the trend:

While it still looks bad, the message both chart send is quite different: “doom and gloom” on the left, “maybe we can clean up this mess” on the right.

Apparently, the data was too boring, and the graphic designer decided to spice up the story a bit. But the chart was published in Expresso, a very popular Portuguese weekly newspaper, and given its high standards, I want to believe that’s not the case. So, only unforgivable ignorance and/or negligence can explain this.

Your thoughts?

{ 6 comments }

A new data visualization research paper finds that chart junk does not harm accuracy and actually improves recall. The paper is an interesting read but, unfortunately, not for the right reasons.

I’ll discuss the paper in an upcoming post. Today I just want to comment a sentence from the introduction:

“This minimalist [Tufte's] perspective advocates plain and simple charts that maximize the proportion of data-ink – or the ink in the chart used to represent data.”

Now, let’s see what the authors believe to be a minimalist chart:

This is not a minimalist chart. It may be a plain and simple chart, but it’s also an ugly one. No wonder no one remembers it.

You see, when you strip a chart down to its basic features, a real minimalist chart also plays with variables like color to emphasize / de-emphasize the remaining features (series, grid lines, labels) to create an aesthetically pleasing experience that drives an emotional response. And that’s what the authors themselves do when reporting the results:

This is a fairly Tufte-compliant chart…

Problem is, if you misrepresent a concept it undermines the entire research. Instead of “minimalist” charts, the authors should call them what they really are: placebo charts. So, they should say: “In our comparison between junk charts with a low number of data points and placebo charts, the results suggest that…”

A note about data-ink ratio. I believe that the deep meaning of the data-ink ratio is not to remove junk, but to create more complex and insightful charts. Once you remove useless “features” you get more chart  real estate and you can add more data (Tufte says: “to clarify, add detail”). And that’s how you really maximize the data-ink ratio, not just because you remove junk.

So, what do you thing? Are minimalist chart just some boring plain & simple charts or there is more about them than these authors are willing to accept?

{ 12 comments }

What if you love Excel for all the wrong reasons? What if its flexibility creates nothing more than a glorified mess? What if you start neglecting the skills that truly matter?

Every formula in Excel is an accident waiting to happen. Sometimes, a parameter is missing, sometimes it points to the wrong cell, sometimes you use absolute references instead of relative references…

Structure Is Your Friend

What can you do? First of all, learn how to structure your workbook. Each sheet should be assigned to a specific role: create a control/parameters sheet, a data sheet, a report sheet (use different tab colors for each role).

You’ll probably know  how to structure a workbook if you know how to structure your data. Unfortunately, most Excel users are not aware of this (because of that “flexibility” thing).

That’s why you should learn Access (or a similar database tool). You don’t have to be an Access power user. You just need to learn the basics: what a relational database is, how to create a table, what is and how to design simple queries. And, of course, how to connect Excel to an Access table.

Example: How to Make Excel and Access Work Together

Let me give you an example of how you could use Access and Excel for, say, a sales dashboard.

1. Create a new Access file;
2. Sales data is stored in an Oracle database. Ask your IT to create a view (a query) with the data you need;
3. Create a link between your Access file and the query (via ODBC – your IT should be glad to do it for you, right?);
4. Create a “Parameters” Excel file. Make is available somewhere so that product managers can open and edit it.
5. Connect your Access table to each of the sheets in the “Parameters” Excel file;
6. Now create an Output table where you will store all the data you need for your dashboard;
7. Design and run all the queries to calculate market share, growth and other KPI. Use those queries to populate the Output table;
8. Create a macro to run the queries in the right sequence;
9. Create a new Excel file;
10. Connect the Excel file to the Output table;
11. Start designing your Excel dashboard!

(By the way, if you want to see how to connect Excel to Access and use an Access table as the data source for a pivot table, go to the members area, create a free account and watch the video in the Demographic Dashboard Sample.)

I use this approach in many of my projects. While queries are not error-free, those errors are often much easier to spot than using Excel formulas.  The dashboard is easier to maintain and, since I don’t have to manage hundreds of formulas, I feel safer. Performance also improves, because Excel is not recalculating everything every time the user selects a different option.

Want to Know More? Buy the Book!

I was writing this post when I came across Michael Alexander’s new book, The Excel Analyst’s Guide to Access. I fell in love with the book the moment I read Michaels’s post. I’ve been doing this for years now, but I’m sure I will learn a lot, and you will too. I’ll write a review here as soon as the book arrives.

You must always check your Excel reports or dashboards for errors, and formulas are the first place you should look for them. Better yet: avoid them. Use VBA, use pivot tables and make Excel and Access work together. Do it, and I’m sure you’ll find more time to play with the kids.

Photo credits: Humberto Terenziani

{ 2 comments }

Yes, I’m leaving in May.

Things (economy-wise) don’t look pretty, and leaving a steady job may seem unbearably self-indulgent. So, why I’m I doing this? Yes, I was unhappy, and yes, money is important too. But there’s something else.

Tom says I have a mission. Well, I don’t take myself that seriously, but I do want to find and share my own data visualization voice. I already hear something. It may be:

• My own voice, still babbling;
• Other people’s voices inside my head (hope there is a medical treatment available);
• My very own misinterpretation of Stephen Few’s words (again).

This blog has 15k subscribers and I constantly get great feedback from the dashboard tutorial. I must be doing something right, and I must give myself the chance of finding how far the rabbit hole goes:

• How much am I able to learn and apply?
• How good am I at sharing that knowledge?
• Can I make a living out of my data visualization projects? (No, I don’t want to be rich).

So, as soon as I leave the company, I’ll:

• Try to be a better and more active member of the data visualization community;
• Read, digest and write a review of all the books on my shelf;
• Add more content to this blog;
• Create a real data visualization learning center out of the members area.

Over the last two years, I was frequently asked about my consulting services. I was also invited by some start-ups to join them. Unfortunately, I was unable to accept. With a full-time job and four year old twins at home (another full-time job), while trying to build a blog, I’d be spreading myself too thin. That will change in the near future, but slowly.

There are many “I” in this post, but it’s really about you. You made this post possible. I will never forget that. Thank you!

Photo: My son Lourenço, ready to conquer the world.

{ 17 comments }

OK, I’m very please to announce that ExcelCharts.com members area is now open and a new online Excel 2007 dashboard tutorial is available. Go ahead an open a free account: the first two modules are available there and more free stuff is coming soon. As promised, current users will get free access to this course.

Know more about the dashboard tutorial.

Get the tutorial.

Open a free account.

{ 4 comments }

Regular readers know that I’m updating my Excel dashboard tutorial and making it available online in a protected area of excelcharts.com. In exchange for their feedback, current users will get free access to the updated tutorial for Excel 2003 and also to the new Excel 2007 version.

This is something I’ll be working on throughout the year of 2010 (and beyond) and I’d like you to help me. That’s why I’m extending the free subscription period to December 31 (instead of a  fixed three month access). You will also be eligible for a 50% discount when buying other courses. These are the good news I’d like to share.

I’m opening the members area this month. I actually wanted to open it today (Feb. 1). Unfortunately, some issues remain to be solved (I wasn’t happy with WordPress as an e-learning environment so I had to switch, and the IE6 completely messes up the current template). I think I’ll be able to solve these issues shortly. I’ll let you know when everything is ready.

Why don’t you take advantage of this delay and become a charter member while I fight the goddamn browser?  To become a charter member you just have to get the current dashboard tutorial. Click here to get it now (you may want to read Chandoo’s review first – read the comments also).

{ 0 comments }