All articles in 'Cool Infographics & Data Visualizations' Category

This is part two of our three part series on how to analyze half a million customer complaints. Read part 1 here.

Analyzing Regional Trends

As introduced in part 1, our complaints dataset has geographical information too. We know the state & zip code for each complaint. Please note that zip codes are partial or missing for a 10% of the data.

In this article, let’s explore three ways to analyze regional trends.

1. Regional trends by state, product & issue
2. Complaints per million by state
3. Complaints by zip code

Hello all, prepare to be amazed! Here are 43 creative, fun & informative ways to visualize KPI data.

About a month ago, I asked you to visualize KPI data. We received 65 entries for this contest. After carefully reviewing the entries, our panel of judges have discarded 22 of them due to poor charting choices, errors or just plain data dumps. We are left with 43 amazing entries, each creatively analyzed the data and presented results in a powerful way.

How to read this post?

This is a fairly large post. If you are reading this in email or news-reader, it may not look properly. Click here to read it on chandoo.org.

1. Each entry is shown in a box with the contestant’s name on top. Entries are shown in alphabetical order of contestant’s name.
2. You can see a snapshot of the entry and more thumbnails below.
3. The thumb-nails are click-able, so that you can enlarge and see the details.
4. You can download the contest entry workbook, see & play with the files.
5. You can read my comments at the bottom.
6. At the bottom of this post, you can find a list of key charting & dashboard design techniques. Go thru them to learn how to create similar reports at work.

Thank you

Thank you very much for all the participants in this contest. I have thoroughly enjoyed exploring your work & learned a lot from them. I am sure you had fun creating these too.

So go ahead and enjoy the entries.

Whenever you have a dashboard that is quite long or spans across multiple worksheet tabs, it can be hard to use. Here is a simple trick to make your dashboards user friendly. If your dashboard has form controls, create duplicate sets of them and place them in locations where users are looking. For example, If your […]

Here are three questions you often hear from your boss:

1. What changes are happening in our business and how do they look?
2. Do you know how to operate this new coffee machine?
3. Why does every list has 3 items?

Jokes aside, our urge to find change in environment predates cave drawing, slice bread and Tommy Lee Jones. So, today let’s examine a very effective chart that tells the story of change and re-create it in Excel.

Friends and readers of Chandoo.org, my newest download, Excel Dashboard Templates are available for your consideration. Click here if you are ready to get them. Read on to know more.

What are Excel Dashboard Templates?

As the name suggests, these are ready to use Excel templates for creating awesome, informative, easy to understand & quick dashboards with your data. You just enter your data, set up few calculation options, decide how your dashboard should look & bingo, a beautiful & insightful dashboard will be created for you. These dashboards are optimized to look great, convey key information & prompt correct action.

Who should buy these templates?

Analysts, managers, reporting professionals, business owners & executives. These templates are designed to help anyone preparing Excel based dashboards, scorecards or KPI reports.

Benefits for you

• Design awesome dashboards in minutes, not months: Don’t waste hours trying to figure out formulas, chart settings and layouts. Instead focus on what your users want, how your business works and use those insights to tell the ‘Excel Dashboard Template’ what you want. Save time and look awesome.
• Accommodate change elegantly: People change. Businesses change. What your boss wanted last month is no longer what she wants today. So when someone asks you, “hmm, can you show me YTD data instead of last 13 months”, or “Can I see top 10 KPIs instead of all”, you just smile and tap a few clicks and bingo, the new dashboard will be ready. These templates are designed to handle change (data, requirements, design preferences and insights).
• Beautiful & Informative: These templates are designed with a delicate balance of beauty & insight. Everything is seamlessly designed, perfectly aligned and precisely calculated so that you & your boss can focus on the insights & decisions. Time tested and well honored visualization principles & guidelines are observed thru-out the reports.
• Built for starters, built for pros: Whether you are just starting with Excel or a seasoned pro, you can find the templates right up your alley. For advanced users, we have many customizations to whet your appetite. Want to build a custom calculation or include another type of sorting, you can do it by tinkering with ‘calculations’ sheet.

Recently, I saw this chart on Economist website.

It is trying to depict how various cities rank on livability index and how they compare to previous ranking (2014 vs 2009).

As you can see, this chart is not the best way to visualize “Best places to live”.

Few reasons why,

• The segregated views (blue, gray & red) make it hard to look for a specific city or region
• The zig-zag lines look good, but incredibly hard to understand % changes (or absolute changes)
• Labels are all over the place, thus making data interpretation hard.
• Some points have no labels (or ambiguous labels) leading to further confusion.

After examining the chart long & hard, I got thinking.

Its no fun criticizing someones work. Creating a better chart from this data, now thats awesome.

This is a guest post by Krishna, a football lover & one of our readers.

The wait for lifting the most valued priced in football for Germans was finally over. For a football fan, world cup is best time that is scheduled every four years and that if your favorite team lifting the trophy is like your crush is going on a date with you. 🙂

A sneak-peek at the final dashboard

Here is the final dashboard (it has more functionality than depicted). Click on it to enlarge.

Ok people. Let me tell you that this post is almost not about Excel. It is about how one Excel blogger’s (yours truly) dream of long distance cycling came true. So sit back, grab your favorite drink and read between sips.

So what is this all about?

Last Sunday (27th July) & Monday (28th), I finished my first ever 200KM bicycle ride. I rode for a little more than 12 hours, burned 5,179 calories & rode 206 kilometers.

It is definitely one of the most memorable, tiresome & uplifting experiences in my life. So naturally, I want to share the story with you.

Hello everyone. Stop reading further and go fetch your helmet. Because what lies ahead is mind-blowingly awesome.

About a month and half ago, we held our annual dashboard contest. This time the theme is to visualize state to state migration in USA. You can find the contest data-set & details here.

We received 49 outstanding entries for this. Most of the entries are truly inspiring. They are loaded with powerful analysis, stunning visualizations, amazing display of Excel skill and design finesse. It took me almost 2 weeks to process the results and present them here.

Click on the image to see the entries.

Today lets talk about how to build world-class dashboards.

What is a dashboard?

Dashboard reports allow managers to get high-level overview of the business and help them make quick decisions. A dashboard is usually a one page report that contains critical information for decision support.

How to make dashboards?

Excel is an excellent tool to make powerful dashboards that can provide analysis, insight and alert managers in timely manner. But creating a dashboard takes a lot of skill & practice. That is where a resource like Chandoo.org comes handy. In this site you can find more than 200 different dashboard examples, tutorials, samples and downloads – all designed to make you awesome.

Read on to learn more…

Few days ago, we learned how to create a pie+donut combination chart to visualize polls around the world in 2014. It generated quite a bit of interesting discussion (47 comments so far). One of the comments was from Roberto, who along with Kris & Gábor runs The FrankensTeam an online library of advanced Excel tricks, charts and other mind-boggling spreadsheet wizardry.

I really liked Roberto’s comments on the original post and a charting solution he presented. So I asked him if he can do a guest post explaining the technique to our audience. He obliged and here we go.

Over to FrankensTeam.

How do you segment customers by revenue easily in Excel, so that you can see which customer groups to focus on. Find out, and download this handy template.

In this charting example, Jeff, our guest author, occasional charter and Excel wizard shows us how to segment customers based on revenue and depict the result using Excel charts.

Lets talk about people who inspire us. People who show us that anything is possible. People who prove that commitment, hard work and perseverance are true ingredients of a genius.

I am talking about Sachin Tendulkar. Those of you who never heard his name, he is the most prolific cricketer in the world. He is the leading scorer in both tests (15,921 runs) and one day matches (18,426 runs). Read more about him here.

Tendulkar has been an inspiration for me (and millions of others around the world) since I was a kid. The amount of dedication & excellence he has shown constantly motivates me. It is a pity that the great man is retiring from test cricket. He is playing his last test match (200th, most by any person) as I am writing this.

So as a small tribute, I have decided do something for him. Of course, I have never been a cricketer in my life. Once in college I was reluctantly asked to be a stand-by player in a game with seniors. I did not get a chance to pad up though. That is the closest I have been to a cricketer. So I did what I do best. Create an Excel workbook celebrating Sachin’s test career.

Thank you Sachin – his test career in a dashboard

Here is a dashboard I made visualizing his test cricket statistics. It is dynamic, fun & awesome (just like Sachin).

(click on the image to enlarge)

Finally the wait is over. Eager to know who won our Excel Salary Survey Dashboard contest? Read on.

During London 2012 Olympics, Usain Bolt reached the 100mts finish line faster than anyone in just 9.63 seconds. Most of us would be still reading this paragraph before Mr. Bolt finished the race.

To put this in perspective, NY Times created a highly entertaining interactive visualization. Go ahead and check it out. I am sure you will love it.

So I wanted to create something similar in Excel. And here is what I came up with.