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

Latest Tips & Tutorials

Revenue vs. Commission growth – Getting the message across [BYOD]

Situation: Our commissions are growing way faster than revenues
Let’s say you are looking revenues & sales commissions of your company for last few years. The data looks like this:

And you want to highlight the fact that commissions are growing faster than revenues.

So you plot YoY growth rates for revenues & commissions.

Problem: The chart of YoY growth rates is not convincing

Take a look at the chart. It doesn’t convey the message that we want. At best it says “revenue growth is less than commission growth”

How to convey the message “Commission growth is a problem for us”?

Read This »

February 17, 2015

How to consolidate data that is different shapes [BYOD]

Last week, I asked my email newsletter readers to submit “one data analysis problem you are struggling with”. We called it BYOD – Bring your own data. More than 100 people have emailed various interesting (and often very difficult) problems. This week (between 16th of February to 20th of February), let’s take a look at some of these problems and solve them.

Consolidating data in different shapes

We can use either VBA or Excel’s consolidation features to combine data that has same shape (ie same number & type of columns). Here is one way to do it.

But what if we need to consolidate data that is in different shapes?

Something like above.

In such cases, we can use 3 powerful tools.

Multiple Consolidation Ranges – Pivot Tables
VBA
Power Query

So let’s examine how to use these approaches to consolidate data in different shapes.

Read This »

February 16, 2015

CP029: Impress your boss with Excel charts – 6 step road map for you

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 29th session of Chandoo.org podcast, let’s impress the boss with Excel charts.

What is in this session?

Many Excel charts live a short life. They spawn in an ambitious analyst’s spreadsheet. They go to boss with literally flying colors. The boss frowns, they disappear in to recycle bin.

Don’t curse your Excel charts with short life span.

Here is a 6 step road map to help you create awesome Excel charts, everytime.

That is our topic for this podcast session.

In this podcast, you will learn

Quick announcements about 50 ways & Einstein
6 step road map for charting success
ONE: Dig your data
TWO: Validate insights
THREE: Pick charts that go well
FOUR: Add title & message
FIVE: Remove clutter
SIX: Prompt action
A real life example with road map in action
Resources for creating awesome charts

Read This »

February 5, 2015

Custom Number Formats – Colors

Did you know you can use Excel 95-03 Color Names or Color Numbers in your Custom Number Formats in all Excel versions up to an including Excel 2013?

This post will show you how.

Read This »

February 3, 2015

2 Must watch Excel webinars for you

Just a quick announcement.

As you may know, I will be speaking at PASS Business Analytics conference in Santa Clara this April (between 20th & 22nd). As a run up to the event, the good folks at PASS are conducting free webinars on various topics of Excel.

Here are 2 topics on which I am doing webinars. You can watch them and pick up some really cool Excel skills. Just click on below links to enjoy them.

5 Dashboard mistakes to avoid (watch it any time)
Excel charting – productivity tips (live on February 3rd)

That is all for now. See you in the webinar.

Read This »

February 2, 2015

What is the length of longest winning streak? [Excel homework]

Here is a fun problem to think about.

Let’s say you are looking at some data like above.

And you want to find out what is the longest streak of wins in the list.

How do you calculate it?

bonus question: What formula calculates when the longest streak began?

Read This »

January 30, 2015

Doing Cost Benefit Analysis in Excel – a case study

Imagine you are the in-charge of finance department at Hogwarts. So one fine day, while you are practicing the spells, Dumbledore walks in to your office and says, “Our electricity bills are way too high. As the muggles don’t accept wizard money, we have to find a way to reduce our power consumption.”

So you summoned the previous 12 month utility bills to examine energy consumption patterns, and pretty soon you realized that most of the electricity consumption is due to the light bulbs. You suddenly have a brilliant idea. Why not replace the light bulbs with a variety that consumes low power? A light bulb moment indeed.

Your next step is to figure out what varieties of light bulbs are out there. Fortunately this is easier than catching a snitch in a game of quidditch. A quick search revealed that there are 3 types of light bulbs:

Regular incandescent bulbs (the kind Hogwarts currently uses)
Compact Fluorescent Light bulbs (CFL)
Light Emitting Diode bulbs (LED)

Now your job is to do a cost benefit analysis of these options and pick one.

Read This »

January 28, 2015

CP028: How to tell business logic & rules to Excel?

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 28th session of Chandoo.org podcast, let’s figure out how to express business rules & logic to Excel.

What is in this session?

What good are spreadsheets if they can’t solve business problems?

But we all struggle when it comes to modeling real world business conditions in Excel. For example, if you have below business rule to decide how much discount to offer a customer,

If the customer bought 3 or more times previously and offer 15% discount
If the customer bought 1 or 2 times previously AND customer’s age is >40, offer 10% discount
If the customer visited our New York store between 6PM-9PM offer 5% discount
Else no discount

How would you go about modeling these in Excel?

That is our topic for this podcast session.

In this podcast, you will learn