Chandoo.org - Learn Excel, Power BI, Charting, Dashboards and VBA online

Latest Tips & Tutorials

I struggle doing __________ with Excel. Fill in the blanks…,

As I am doing a road-trip across parts of southern India (we are in the beautiful temple town of Madurai as you read this post), here is something to keep you busy.

Fill in the blanks & post in comments.

I struggle doing _________________ with Excel.

Read This »

May 27, 2014

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

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

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?

Read This »

May 22, 2014

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.

Read This »

May 21, 2014

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.

Read This »

May 20, 2014

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

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

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

Read This »

May 15, 2014

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.

Read This »

May 13, 2014

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.

Read This »

May 7, 2014

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.