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

Latest Tips & Tutorials

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.

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?

Read This »

May 5, 2014

What is your passion? [weekend poll]

This weekend, lets get passionate 😉

Few days ago, Cheryl, one of our forum members asked this question:

How do you know Excel is your passion? Or is it?

I am searching for my passion, you know that thing that makes my heart sing. I mean I am listening for the pitter-patter in my ticker. So how do I know if Excel is it? Or anything for that matter. I am looking for that thing that will make me turn my tv off. (TV is my crack, I am truly addicted). I thought it was database development and honestly I am not altogether sure that it isn’t. Excel may be a substitute. A more attainable passion.
Give me some insight peeps. Some thoughts, musings, ideas.

As usual, many of our forum members chipped in with words of wisdom. Hopefully Cheryl saw their replies, if she ever managed to turn off that tv.

That gave me an idea for this week’s poll.

What are you passionate about?

Read This »

May 2, 2014

CP007: aweSUM() – Overview of SUM functions in Excel

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 7th session of Chandoo.org podcast, lets make you aweSUM().

Imagine for a second that Excel cannot add up numbers. And no it cant subtract them either. What would that look like?

A glorified Notepad. That’s right. Excel’s ability to add up numbers, along with features like formulas, charts, pivot tables & BHATTEXT() are what make it such a lovely software. May be not the BHATTEXT(), but we all agree that Excel is so versatile and useful because it can add up numbers (and perform other calculations) with ease.

But how well do you know the SUM formulas of Excel?

In this podcast, you will learn,

Special personal fruit announcement 😛
+ operator
Status bar & total rows in tables
Auto Sum feature
SUM() function
SUMIFS function
Special cases of SUMIFS function
SUBTOTAL & AGGREGATE functions
Other summing functions – SUMPRODUCT etc.

Read This »

May 1, 2014

Calculate CAGR (Compounded Annual Growth Rate) using Excel [Formulas]

Lets talk about how we can use Excel to calculate Compounded Annual Growth Rate (CAGR for short).

What is CAGR? What does it signify?

Let us say you are the CEO of ACME Inc. You have been selling various widgets since 2009. In your latest annual report you want to tell your shareholder at what rate you have been growing ACME Inc. sales. The figure are,