Welcome to Chandoo.org. New here?

Thank you so much for visiting us & taking first step to become awesome in Excel. Please use below links to navigate our site.

CP009: Averages are Mean – Know these things before you make any more AVERAGE()s

CP009: Averages are Mean – Know these things before you make any more AVERAGE()s

In the 9th session of Chandoo.org podcast, lets raise above AVERAGEs.

AVERAGEs are a very popular and universal way to summarize data. But do you know they are mean? Mean as in, AVERAGEs do not reveal much about your data or business. In episode 9 of Chandoo.org podcast, we tackle this problem and present solutions.

In this podcast, you will learn,

  • What is AVERAGE?
  • Pitfalls of averages
  • 5 statistic concepts you must understand
    • Standard Deviation
    • Median
    • Quartiles
    • Outliers
    • Distribution of data
  • What next?
Continue »

Implementing Modular Spreadsheet Development – a walkthrough

Implementing Modular Spreadsheet Development – a walkthrough

This article is written by Michael Hutchens from Best Practice Modelling.

In the first article on Modular Spreadsheet Development, we got a high level overview of Modular Spreadsheet Development principles. This article discusses the practical implementation of these principles in Excel.

From my experience using Modular Spreadsheet Development over the past decade, there are three increasingly-efficient methods of implementation in Microsoft Excel:

1. Manual implementation;
2. VBA automated implementation; and
3. Commercial add-in implementation.

This article provides a comprehensive overview of each of these methods and a summary of their advantages and disadvantages.

Continue »

Excel Links – Delay in State migration visualization results edition

Hello friends,

First a quick update about state migration visualization contest.

We got more than 50 wonderful entries for this contest. I have compiled 50% of the entries. Due to personal reasons (its annual vacation time at our household), I had to delay the rest and resume it after first week of June. So the results will be announced in 2nd week of June. Thanks for understanding and your patience. Watch this space for mind-boggling dashboards in 3 more weeks.

Click “Read more” to get a sneak-peek at 3 random entries & 4 useful Excel links.

Continue »

CP008: 6 Tips to handle workbooks made by someone else, #4 is something I struggle with too!

CP008: 6 Tips to handle workbooks made by someone else, #4 is something I struggle with too!

Here is a problem we all face once in a while. We inherit this bulky, bloated, leaking at the edges workbook from a colleague. Now the onus of maintaining it is on us. The person who made this workbook is nowhere to be found. May be she is vacationing in Hawaii sipping pineapple juice. May be he became a vice president and roaming the country in your company’s private jet.

So what do we do? How do we handle this inheritance?

That is the topic of our podcast, episode 8.

In this podcast, you will learn,

  • An overview of the inheritance problem
  • 6 Tips to understand workbooks made by someone else
  • Tip 0: Talk to the creator
  • Tip 1: Model the workbook on paper
  • Tip 2: Locate the engine, ie the formulas
  • Tip 3: See what else is under the hood – hidden sheets, names, VBA code
  • Tip 4: Annotate (add comments) as you learn
  • Tip 5: Locate the controls – inputs, assumptions, scenarios
  • Tip 6: Re-construct from scratch
  • Deep dive in to understanding the formulas
  • Deep dive in to understanding VBA code
  • Conclusions
Continue »

Building a simple timer using Excel VBA to track my Rubik’s cube solving speed [case study]

Building a simple timer using Excel VBA to track my Rubik’s cube solving speed [case study]

Today, lets learn how to make a simple timer app using Excel. First some background…,

Recently, I learned how to solve Rubik’s cube from my nephew. As a budding cuber, I wanted to track my progress. Initially I used the stopwatch in my iPhone. But it wont let me track previous times. So I thought, “Well, I can use Excel for this”.

So I made a small timer app using Excel. Its quite minimalistic. It has a single button. I press it and it tracks the start time (date & time stamp). If I press the button again, it records the duration.

This way, I can see my progress over next few weeks and may be plot the trend.

Continue »

Modular Spreadsheet Development – A Thought Revolution

Modular Spreadsheet Development – A Thought Revolution

This article is written by Michael Hutchens from Best Practice Modelling.

This article provides a high level overview of Modular Spreadsheet Development principles. In next part the implementation of these concepts will be discussed.

Modular Spreadsheet Development – An awesome concept

I want to share a concept with you so awesome that once you understand it you may never use Excel the same way again.

This concept, called Modular Spreadsheet Development, makes it possible to build spreadsheets exponentially faster while reducing the risk of errors and making spreadsheets much easier to understand.

This concept is not completely new, but I’m writing this article because the spreadsheet modelling world would be a much better place if it was more commonly adopted.

Continue »

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?

Continue »