All articles with 'Learn Excel' Tag

Celebrating India’s Worldcup Cricket Victory – In Excel Dashboard Style!

Celebrating India’s Worldcup Cricket Victory – In Excel Dashboard Style!

I know I am late to the party, but better late than…, uh! forget it.

As the keen readers of our blog knew, I like cricket and I show my enthusiasm by making an excel dashboard (or info-graphic) whenever Indian team reaches a major milestone. So naturally, I was super excited when we won the ICC World cup 2011. Last time Indian won the event was in 1983 and my idea of a dashboard at that time was a bottle of milk and jingo-bell, my favorite shake-to-make-annoying-noise toy. I think our latest world-cup victory deserves something more than that. So here we go.

Continue »

How to make a 5 Star Chart (Similar to Amazon)

How to make a 5 Star Chart (Similar to Amazon)

How to make a 5 Star Chart (Similar to Amazon)

Last week Chandoo presented Give more details by showing average and distribution

At the top of the post was a small screen capture from Amazon.com showing a 5 Star chart showing that Twilight had a 3.5 Star Rating (way over-rated if you ask me).

I received an email shortly afterwards from Rajiv, “How can I make one of those charts ? ” with the Stars Circled

It’s actually very simple and this post will show you how.

Continue »

Auditing Spreadsheets? – Disable Direct Editing Mode to save time [quick tip]

Published on Apr 11, 2011 in Excel Howtos
Auditing Spreadsheets? – Disable Direct Editing Mode to save time [quick tip]

For most of us, the prospect of inheriting a large, undisclosed sum of money is bleak. But we have high probability of inheriting a complex Excel workbook with 19 worksheets and 2300 rows of data and 195 formulas. The kind where entire rainbow colors are used to color code accounts receivable statuses. Then what do we do? We spend a whole afternoon (and then the rest of the month) breaking our head trying to figure out why the total revenues are only $ 41.2 million when profits are $ 99.23 million.

So how do we deal with our inheritance?

Here is a quick tip to help you get started. Disable “Direct editing mode“.

Continue »

Show Details On-demand in Excel [Tutorial + Training Program]

Published on Apr 7, 2011 in Charts and Graphs, products, VBA Macros
Show Details On-demand in Excel [Tutorial + Training Program]

Yesterday, we have seen a beautiful example of how showing details (like distribution) on-demand can increase the effectiveness of your reports. Today, we will learn how to do the same in Excel.

Continue »

Give more details by showing average and distribution [Charting Tips]

Published on Apr 6, 2011 in Charts and Graphs
Give more details by showing average and distribution [Charting Tips]

When we have lots of data, we try to summarize it by calculating the average. As they say, averages are mean, they do not give away much.

I want to share with you an interesting example from Amazon.com on how they give more details by combining average with distribution.

As you might know, Amazon shows the rating of each of the products they sell. Customers & users rate the products from 1 to 5 stars. When you visit the product page you will see the average rating. But there is a small down-arrow next to it. When you click on it, Amazon shows you the break-up of that rating so you have a better idea of how the ratings are split.

Continue »

Excel Links – Congratulations Team India Edition

Published on Apr 4, 2011 in excel links
Excel Links – Congratulations Team India Edition

Wow, what a weekend it has been. Indian cricket team has won Cricket world cup 2011. It has been a highly entertaining tournament and the finals were just incredible.

Congratulations to Team India for winning Cricket World-cup 2011.

I am tempted to make a dashboard of sorts to show the journey of Team India in this world cup. But I could not get much time to work on anything. I will post something this week to celebrate the victory.

Read the rest of this post to get 5 recommended Excel resources to learn new things.

Continue »

Learning Dashboards? – Go thru these 33 Recommended Resources

Published on Mar 25, 2011 in Charts and Graphs, Learn Excel
Learning Dashboards? – Go thru these 33 Recommended Resources

During last one week, we had a gala time with Dashboard Week on chandoo.org. To wrap-up the week, I am sharing a list of recommended resources, websites, tutorials & ideas for making dashboards.

Recommended Resources on Making Dashboards:
I have broken down this post in to various sections. Click on the links to quickly access the part you want to know or just keep scrolling to get the whole thing.

  1. Books on Dashboards
  2. Websites for Learning about Dashboards
  3. Dashboard Training Programs
  4. Add-ins & Software to Make Dashboards
  5. Dashboard Tutorials & Downloads on Chandoo.org
Continue »

KPI Dashboard – Revisited

Published on Mar 24, 2011 in Charts and Graphs
KPI Dashboard – Revisited

In 2008, I received an email from Robert Mundigl, which was the start of a life-long friendship. Robert asked me if he can teach us how to make KPI dashboards using Excel. I gladly said yes because I am always looking for new ways to use Excel.

The original KPI dashboards using Excel article was so popular. They still help around 12,000 people around the globe every month. Many of our regular readers and members have once started their journey on Chandoo.org from these articles.

In this article, we will revisit the dashboard and give it a fresh new spin using Excel 2007.

Continue »

Executive Review Dashboard in Excel [Dashboard Week]

Published on Mar 23, 2011 in Charts and Graphs
Executive Review Dashboard in Excel [Dashboard Week]

Purpose of the dashboard:

This is a guest article written by John for our Excel Dashboard Week.

This Dashboard was constructed for a number of reasons, one of which was to reduce the number of reports produced with the same data ( up to 6 separate files ). As we all know, when it comes to senior management and reports / files the more information they can get on one report / file the better for them. So, with this in mind I created the Dashboard to show the data they need to see “quickly” each week.

Continue »

Customer Service Dashboard using Excel [Dashword Week]

Published on Mar 22, 2011 in Charts and Graphs
Customer Service Dashboard using Excel [Dashword Week]

Early in Jan, I got this mail from Mara, a student in Excel School first batch.

Hi Chandoo,

I took your first Excel batch class and loved it. I created a dynamic and interactive dashboard for my work. My boss thinks it’s an excellent tool and I have you to thank for and also Francis Chin who shared his travel dynamic dashboard. I integrated things you taught so thanks so much!

I felt very proud reading her email, so I asked her if she can share the dashboard with some dummy data so that we all can learn from her example.

Being a lovely person Mara is, she gladly emailed me the workbook and I am thrilled to include it in Dashboard Week.

Continue »

Health-care Dashboard in Excel [Dashboard Week]

Published on Mar 22, 2011 in Charts and Graphs
Health-care Dashboard in Excel [Dashboard Week]

As part of Dashboard Week, in this post, we will take a look at Health-care Dashboard prepared and shared by Alberto. He put together an excellent dashboard to visualize hospital performance and understand what is going on. Read this post to understand how this dashboard is made, watch a tutorial video & download example workbook.

Thank you Alberto for sharing the file & helping us learn.

Continue »

Announcing Dashboard Week – Submit your entries now!

Published on Mar 21, 2011 in blogging, Charts and Graphs
Announcing Dashboard Week – Submit your entries now!

Hello there,

I am glad to announce this week (21-25 March, 2011) as Dashboard Week on Chandoo.org
What happens in Dashboard Week?
As you can guess, during this week, I will be posting exclusively about Excel Dashboards. This is the tentative agenda … []

Continue »

Use Analytical Charts to Make your Boss Love You

Published on Mar 16, 2011 in Charts and Graphs
Use Analytical Charts to Make your Boss Love You

There are beautiful, powerful & awesome charting examples all around us. Today, I want to show you how we can harness the power of Excel to create Analytical Charts.

Analytical What?!?

To be frank, I do not know what to call these charts, so I choose the term Analytical Charts. But this is what I have in mind when I say Analytical charts:

A chart is analytical chart,
(1) If it is interactive
(2) It it can answer different questions by re-structuring same data differently

Continue »

Convert ISERROR formulas to IFERROR formulas [macro]

Published on Mar 14, 2011 in VBA Macros
Convert ISERROR formulas to IFERROR formulas [macro]

Last Friday, we have learned about an interesting formula – IFERROR Formula using which you can easily handle errors in Excel workbooks.

Quite a few people reading that page asked, “Wow, this is good. But how can I take a sheet full of =IF(ISERROR(…)….) formulas and convert them to =IFERROR()”

There is a different set of folks who asked “Wow, this is good. But quite a few of my colleagues use Excel 2003 and they see a bunch of #NAME errors when I send them an excel workbook with IFERROR formulas. Any help?!?”

I am pleased to announce that I wrote 2 simple macros, iferror2iserror() and iserror2iferror() that would scan formulas in a bunch of selected cells and convert them from IFERROR to ISERROR and vice-a-versa.

Continue »

IFERROR Excel Formula – What is it, syntax, examples and howto

Published on Mar 11, 2011 in Learn Excel
IFERROR Excel Formula – What is it, syntax, examples and howto

If IFERROR() were to be a person, I would hug her so hard that Jo (my wife) would get in to a cat fight with her. I know many a woman (and man) who get in to a fight with Excel formulas often. But thankfully, we avoid that as IFERROR is not a real person. It is, however a darned useful formula.

Since I cannot hug a formula anymore than I can get my son to sit tight, I will go ahead and sing an ode to her, in my style – by writing about how useful and powerful IFERROR formula is.

Continue »