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

Format charts quickly with chart styles & color themes [quick tip]

Format charts quickly with chart styles & color themes [quick tip]

Here is a quick tip to reduce the time you spend on chart formatting – use chart styles & color themes.

Excel offers various pre-defined color schemes and chart styles. Using them is very simple.

  1. Select your chart
  2. Go to Chart Design ribbon
  3. Click on the style or color scheme you want.
  4. Your chart changes instantly.


Don’t make your charts heavier than they should be – The weight of the world chart [case study]

Don’t make your charts heavier than they should be – The weight of the world chart [case study]

Here is an interesting chart from Economist, ironically titled The weight of the worldCan you tell what is wrong with it?



Generate a snow flake pattern Excel [holiday fun]

Generate a snow flake pattern Excel [holiday fun]

Yesterday I saw a tweet from @JanWillemTulp with random snow flake generator.

That got me thinking…? Why can’t we make a snow flake pattern in Excel?

This is what I came up with.

Read on to know more about this snow flake and download the example workbook.



Color changing line chart [tutorial]

Color changing line chart [tutorial]

Let’s learn how to create a color changing line chart using Excel. This is what we will create.

Looks interesting? Read on.



KPI performance charts & dashboards – 43 alternatives (contest entries)

KPI performance charts & dashboards – 43 alternatives (contest entries)

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.



CP049: Don’t do data dumps!!!

CP049: Don’t do data dumps!!!

In the 49th session of Chandoo.org podcast, let’s talk about data dumps!

What is in this session?

In this podcast,

  • What is a data dump
  • Examples of data dump
  • Why we dump
  • Ways to avoid data dumps
    • Go for information dumps
    • Sort the dump
    • Filter the dump
    • Give a table
  • Resources for you


CP048: How to create animated charts in Excel?

CP048: How to create animated charts in Excel?

In the 48th session of Chandoo.org podcast, let’s make some animated charts!!!

What is in this session?

In this podcast,

  • Announcements
  • Why animate your charts?
  • Non-VBA methods to animate charts
    • Excel 2013’s built-in animation effects
    • Iterative formula approach
  • VBA based animation
    • Cartoon film analogy
    • Understanding the VBA part
  • Example animated chart – Sales of a new product
  • Resources and downloads for you


Happy Diwali for all our readers – Animated Flower Pot Cracker in Excel for you…

Happy Diwali for all our readers – Animated Flower Pot Cracker in Excel for you…

Today is Diwali, the festival of lights. I wish you and your family a happy, bright and prosperous festive time. May your house shower with lots of light, laughter and love.

Diwali is one of my favorite festivals. It is a time when all family members get together, eat delicious food, laugh to hearts content and light up diyas (small oil lamps) to celebrate the victory of good over evil. This year, my kids (who are 6 yrs btw) are very excited about the festival. They are looking forward to lighting up diyas and crackers (fire works).

To celebrate the holiday, I made something for you.

An animated flower pot firework in Excel.



Employee training tracker & calendar – tutorial & download

Employee training tracker & calendar – tutorial & download

Imagine you are the head of training department at ACME Inc. You arrange training programs round the year to empower your team. It is hard work, coordinating between employees, trainers, department heads, venues and coffee machines. What if there is something to help you keep track of all this? I am not talking about getting you a shiny new iPad, you silly. I am talking about a tracker & calendar built in Excel that ties everything together (well, almost everything, you still have to fill the coffee machine.)

We are going to build a training program tracker & calendar using Excel.



Contest: Visualize KPI data

Contest: Visualize KPI data

Here is a quick visualization challenge for you.

There are 25 KPIs and each has a base value, current month, corresponding month values. The KPIs also have targets.

The data looks like this:
kpi-contest-data

Download the data:

Click here to download the data for this contest.

Rules for this contest:

  • Visualize this data using one Excel chart (one chart per KPI is ok too).
  • You may omit any any data
  • You can use interactive charts.
  • Do not use add-ins or VBA to make your charts

Go ahead and make your chart(s).



CP046: Gantt charts & project planning using Excel

CP046: Gantt charts & project planning using Excel

In the 46th session of Chandoo.org podcast, let’s talk about gantt charts and project plans.

What is in this session?

In this podcast,

  • A brief intro to Excel 2016
  • What is a Gantt chart?
  • How Gantt charts can help us?
  • How to create Gantt charts in Excel
    • Using bar charts with invisible series
    • Using conditional formatting and formulas
    • Using ready-made templates
  • Resources on Gantt charts & project planning
  • Conclusions


How countries spend their money – chart alternatives

How countries spend their money – chart alternatives

Econimist’s daily chart is a one of my daily data porn stops. They take interesting data sets and visualize in compelling ways. While the daily chart page is insightful, sometimes they make poor charting choices. For example, this recent chart visualizing how countries spend their money uses a variation of notorious bubble chart. Click on the chart to enlarge.

20150912_woc650_1

What is wrong with this chart?

Bubble charts force us to measure and compare areas of circles. Unless you have a measuring tape somehow embedded in your eyes and you are a walking human scientific calculator, you would find this task impossible.

So when you look at the chart and want to find out what percentage Japanese spend on restaurants or how much Americans pay for housing, your guesses will have large error margins.

Not only bubble charts are difficult to read, they are very hard to align. So when you have a bunch of bubbles, no matter how hard you try, your chart looks clumsy (see how the Russian food bubble eats in to Mexico’s bubble, as if it is too hungry 😉 )

Let’s check out a few alternatives to this chart. Read on…



Show forecast values in a different color with this simple trick [charting]

Show forecast values in a different color with this simple trick [charting]

Let’s say you made a chart to show actual and forecast values. By default, both values look in same color. But we would like to separate forecast values by showing them in another color.

If you are a seasoned Excel user, you may be thinking, “Oh, that’s easy. I will just create 2 sets of data (one for actual and one for forecast), make a chart from them and apply separate colors.”

But here is a really simple way to get the same effect.

Use a semi-transparent box to mask the forecast values, as shown above. Read on to learn how to do this.



Cropped chart: when some values are too big to fit

Cropped chart: when some values are too big to fit

We know that column charts are excellent for presenting information. But what if some of the columns are too tall and hijacking the rest. In a previous article, we discussed few of the approaches. Today let’s learn how to build a cropped chart (broken y-axis chart) using Excel, something like above.

Looks interesting? Read on.



Book Announcement: Dashboards for Excel is launching on 15th September

Book Announcement: Dashboards for Excel is launching on 15th September

I have an exciting announcement to share with you. My first ever print book – Dashboards for Excel, co-authored with Jordan Goldmeier is releasing on 15th September.

Dashboards for Excel – what is it?

As the name suggests this book is a handy guide for anyone preparing dashboards, management reports and business intelligence workbooks using Excel. The key benefits are,

  • Learn the visualization tools, charts, tables, and graphs important to management.
  • Understand what management doesn’t want to see in a report
  • Turn around dashboards faster and cheaper than ever before
  • Understand the key role dashboards play in an organization
  • Analyze real-world dashboards to apply important features to your own organization
  • Utilize understated, but powerful, Excel formulas and VBA code
  • Avoid common pitfalls in Excel development and dashboard creation
  • Get started with the Excel data model, PowerPivot, and Power Query

As you can see, this is an advanced Excel book with lots of examples, case studies and practical tips.



Make a quick funnel chart in Excel to track sales performance [video]

Make a quick funnel chart in Excel to track sales performance [video]

Funnel charts are useful to visualize sales & marketing performance. In this brief video, let’s understand how to make a quick funnel chart in Excel.

Read on to learn how to make funnel charts & to download a template for your funnel analysis needs.



Dashboard best practice: Highlight user selection [video]

Dashboard best practice: Highlight user selection [video]

Here is a best practice to improve your dashboard usability. If you have an interactive dashboard, highlight user selections thru conditional formatting.

Check out below quick video to understand what this means.



Give descriptive titles to your charts for best results

Give descriptive titles to your charts for best results

Here is a simple & effective tip on charting.

Give your charts descriptive & bold titles.

How to set up title that are smart & descriptive?

Simple, follow below steps.

  1. Create the title you want in a cell
  2. Select the chart title
  3. Go to formula bar, press = and point to the cell with title
  4. Press enter.


In-cell 5 star chart – tutorial & template

In-cell 5 star chart – tutorial & template

Whenever we talk about product ratings & customer satisfaction, 5 star ratings come to our mind. Today, let’s learn how to create a simple & elegant 5 star in-cell chart in Excel. Something like above.

Read on to learn how to create the above chart.



Work with charts faster using selection pane & select object tools [quick video tip]

Work with charts faster using selection pane & select object tools [quick video tip]

Working with multiple charts (or drawing shapes / images) can be a very slow process. But here is a secret to boost your productivity.

Use selection pane & select object tools

Selection Pane & Select Objects?

If you have never heard of these, don’t worry. These are 2 very powerful features hidden in Excel. Once you know how to unlock them, you will never look back.

How to use selection pane & select object tools to work with charts faster – Video

In this video, understand how to use these powerful features to work with charts faster.