Celebrate 'The VLOOKUP Book' birthday with us. Last day to get 50% discount on the e-book (31 October only).

Click here for details

Excel Charts, Graphs and Templates

Tutorials, Examples and explanations on Excel charting. You can learn how to create almost any chart type in this section. Also learn how to create effective charts, make them interactive and add automation thru VBA

A better chart to visualize “Best places to live” – Top 100 cities comparison Excel chart

A better chart to visualize “Best places to live” – Top 100 cities comparison Excel chart

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).

Best cities to live - Chart from Economist.com

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.



ABC Inventory Analysis using Excel

ABC Inventory Analysis using Excel

ABC analysis is a popular technique to understand and categorize inventories. Imagine you are handling inventory at a plant that manufactures high-end super expensive cars. Each car requires several parts (4,693 to be exact) to assemble. Some of these parts are very costly (say few thousand dollars per part), while others are cheap (50 cents per part). So how do you make sure that your inventory tracking efforts are optimized so that you waste less time on 50 cent parts & spend more time on costly ones?

This is where ABC analysis helps.

We group the parts in to 3 classes.

  • Class A: High cost items. Very tight control & tracking.
  • Class B: Medium cost items. Tight control & moderate tracking.
  • Class C: Low cost items. No or little control & tracking.

Given a list of items (part numbers, unit costs & number of units needed for assembly), how do we automatically figure which class each item belongs to?

And how do we generate above ABC analysis chart from it?



Mapping relationships between people using interactive network chart

Mapping relationships between people using interactive network chart

Today, lets learn how to create an interesting chart. This, called as network chart helps us visualize relationships between various people.

Demo of interactive network chart in Excel

First take a look at what we are trying to build.

Looks interesting? Then read on to learn how to create this.



A dashboard to visualize this FIFA worldcup [guest post]

A dashboard to visualize this FIFA worldcup [guest post]

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.

FIFA 2014 Worldcup Excel dashboard



Story of my first ever 200KM bike ride (plus an Excel dashboard with ride stats)

Story of my first ever 200KM bike ride (plus an Excel dashboard with ride stats)

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.



How fireworks animated chart is made [video tutorial]

How fireworks animated chart is made [video tutorial]

On July 4th this year, I published an animated fireworks chart for you. Many of you liked it. Quite a few wanted to know how its made.

So here is a video explaining the construction of fireworks.

(You can see this video on our YouTube Channel too)



CP014: How to create awesome dashboards – 10 step process for you

CP014: How to create awesome dashboards – 10 step process for you

In the 14th session of Chandoo.org podcast, lets figure out how to make awesome dashboards.

What is in this session?

Excel based dashboards are much in demand these days, thanks to advancements in Excel & growing pressure on costs. Now a days, analysts & managers are expected to quickly put together a dashboard using Excel. But how do you make a dashboard? What process you should follow? These are the questions we address in this podcast.

In this podcast, you will learn,

  • Announcements about upcoming dashboard classes
  • Ten step process for creating awesome dashboards
  • 1. Talk to your end users
  • 2. Make a sketch of the dashboard
  • 3. Validate your understanding
  • 4. Collect data
  • 5. Structure the data


Winners of state migration dashboard contest

Winners of state migration dashboard contest

Finally the wait is over. Here are the winners of our 2014 dashboard contest. Check them out.



4th of July Fireworks – an Excel animation for you

4th of July Fireworks – an Excel animation for you

To all our readers & friends from USA,

I wish you a happy, fun & safe 4th of July.

For the last 4th of July (2013), we (Jo, kids & I) were in USA. We went to Washington DC to meet up a few friends for that weekend. And we had one of the most memorable evenings of our lives when we went to national mall area in the evening to watch beautifully choreographed fireworks. Kids really loved the amazing display of fire-crackers and enthusiasm.

While we all are back in India this time, it doesn’t mean we cant celebrate 4th of July. So I made some fireworks. In Excel of course.

Here is a little Excel animation I made for all of us.

4th of July Fireworks – Excel animation

First watch this quick demo (<15 secs)



CP013: Is this a FIFA worldcup of late goals, lets ask Excel [How to analyze data to answer questions like these...]

CP013: Is this a FIFA worldcup of late goals, lets ask Excel [How to analyze data to answer questions like these...]

In the 13th session of Chandoo.org podcast, lets turn our attention to on-going FIFA worldcup and ask an important question.

What is in this session?
A week ago, we discussed “Has it been a late goal FIFA worldcup?” and used various charts & analysis techniques to answer the question. In podcast, lets tackle the same problem, understand various approaches to answer questions like these & shares some lessons for all the analysts.



A quick Excel tip for you while on a long bike trip…,

A quick Excel tip for you while on a long bike trip…,

Hi friends,

I have a surprise for you. Between the late night world-cup matches & my reinvigorated thirst for biking, I have difficulty finding time to write a long & detailed article for you. So I thought why not say hello to you and share an Excel tip while I am on a biking trip.

Go ahead and check it out. Its just 4 minutes.

Watch it below or on our YouTube channel.



Is this a FIFA worldcup of late goals? Lets ask Excel

Is this a FIFA worldcup of late goals? Lets ask Excel

Just like millions of viewers around the world, I too have been spending hours watching FIFA world cup football matches on TV. I don’t like spending hours watching TV. But when its FIFA world-cup time (which is once every 4 years), I am glued to the idiot box. Blame it on PaWaRa, my school teacher in 8th grade who instilled this passion.

So while watching the match day before yesterday (it was Holland vs. Chile), the commentator said, “This has been a world-cup of late goals” as both teams maintained 0-0 until 77 minute mark when Leroy Fer scored a goal for Holland.

That got me thinking,

Is this really a world-cup of late goals?

But I quickly brushed away the thought to focus on the match.

Later yesterday, I went looking and downloaded all the goal data for 2006, 2010 & 2014 FIFA world-cup matches (2014 data for first 36 matches).

Lets examine the hypothesis “2014 has been a world-cup of late goals”.



Top 10 things we struggle to do in Excel & awesome remedies for them

Top 10 things we struggle to do in Excel & awesome remedies for them

Recently we asked you, what do you struggle doing in Excel? 170 people responded to this survey and shared their struggles. In this post, lets examine the top 10 struggles according to you and awesome remedies for them.



Excel Dashboards – 49 dashboards to visualize US State to State migration trends

Excel Dashboards – 49 dashboards to visualize US State to State migration trends

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.

49 Dashboards to visualize State to State Migration - Chandoo.orgExcel Dashboard Examples - Visualizing state to state migration trends - Chandoo.org

Click on the image to see the entries.



Create a line chart with bands [tutorial]

Create a line chart with bands [tutorial]

Here is an interesting scenario.

Imagine you are responsible for customer satisfaction at ACME Inc. Every month you track customer satisfaction rate for the 3 products you sell which are conveniently named Product A, B & C.

You also have bands for the satisfaction rating.

  • Rating of 85% or below is Average
  • Rating between 85% & 95% is OK
  • Rating above 95% is good

At the end of the year, you want to visualize the ratings for last 12 months for 3 products along with bands.

Something like above.

Unfortunately, there is no “Insert Banded line chart” button in Excel. So what to do?

That is what we will learn today. Ready?



Modeling tiles in a room using Excel Conditional Formatting

Modeling tiles in a room using Excel Conditional Formatting

Last week we learned how to answer questions like, “How many tiles in a room?” using Excel. We learned about CONVERT function and fraction number format settings in Excel.

But why stop at calculation? We can even model a room full of tiles, thanks to Excel’s grid nature.

So today, we will learn how to create a room layout as shown above, using Excel.



CP004: Can I Pie Chart in Public? Discussion about Pie charts, their merits and drawbacks, when to use & when to avoid them

CP004: Can I Pie Chart in Public? Discussion about Pie charts, their merits and drawbacks, when to use & when to avoid them

In the 4th session of Chandoo.org podcast, lets talk about Pie charts.

Pie charts evoke strong opinions among analysts & managers. Some people love them and can’t have enough of them in reports. Others despise them and go to any lengths to avoid them. And that is why we are going to talk about them in this session.

You will learn,

  • Special, secret transmission from guest stars
  • What is a pie chart?
  • Why they work? 2 reasons
  • Why they don’t work ? 4 reasons
  • Cousins & siblings of Pie charts
    • Donut charts
    • Gauge charts (speedometer)
    • 3D pies
    • Area charts
    • Bubble charts
  • 4 Situations when making a pie chart is ok
  • Alternatives to Pie charts
  • Mistakes you should avoid
  • About the resources
  • Conclusions


6 Best charts to show % progress against goal

6 Best charts to show % progress against goal

Back when I was working as a project lead, everyday my project manager would ask me the same question.

“Chandoo, whats the progress?”

He was so punctual about it, even on days when our coffee machine wasn’t working.

As you can see, tracking progress is an obsession we all have. At this very moment, if you pay close attention, you can hear mouse clicks of thousands of analysts and managers all over the world making project progress charts.

So today, lets talk about best charts to show % progress against a goal.



I won’t eat donut with a thread inside, but lets make one anyway!

I won’t eat donut with a thread inside, but lets make one anyway!

Today lets take a stroll outside what Excel can do and make something fancy, fun and may be useful.

Nowadays, many newspapers, websites and magazines are featuring info-graphics. An info-graphic is a collection of shiny, colorful & data-full charts (or often pieces of text.) In many of these info-graphics, you can see threaded-donut charts. Not sure what that is..? It is not same as the blasphemy of spoiling a soft, sweet, supple donut with a piece of string. No one should be excused for an offense like that.

What I am talking about is shown above



Quick and easy Gantt chart using Excel [templates]

Quick and easy Gantt chart using Excel [templates]

Gantt charts are a very popular way to visually depict project plans. Today, let us learn how to use Excel to make quick & easy Project Plan Gantt Chart.

This is what we will be creating.