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

Latest Tips & Tutorials

Excel is 30!!! – what was your earliest memory of Excel? [weekend poll]

This week Excel turned 30. As per wikipedia,

Microsoft released the first version of Excel for the Macintosh on September 30, 1985

Let’s celebrate 30 years of Excel by reliving your first memory of it. Share your earliest memory of Excel in the comments section.

Read This »

October 2, 2015

Imagine you are looking customer data like below and want to sort them by performance. If you sort the data by any one column, you will not get full picture of performance. To understand which customers rank low on performance, you need to defined a weighed sort, the kind of sort where you assign weights to each attribute (customer age, recent purchases and rate of returns) and come up with single score to sort them all.

Sounds interesting? Watch below video to understand how to do weighted sorting in Excel.

Read This »

October 1, 2015

Unpivot data quickly with Power Query [tutorial]

Power Query (Get & Transform data in Excel 2016) is a must have tool, if you wrangle with data every day. Here is a quick introduction, in case you are new.

Let’s learn how to use Power Query to unpivot data.

Essentially, we are trying to go from left to right in the above picture.

Doing something like this thru either formulas or VBA can be very complex. But Power Query can get you unpivoted data in just a few clicks. Sounds interesting? Read on.

Read This »

September 29, 2015

CP045: Introduction to Monte Carlo Simulations in Excel

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 45th session of Chandoo.org podcast, let’s get in to Monte Carlo simulations.

What is in this session?

In this podcast,

Quick personal updates – 200km BRM and book delay
History of Monte Carlo simulations
Monte Carlo simulations – an example
How to do simulations in Excel
- Formulas
- VBA
- Data Tables
Using data tables to run simulations – case study – estimating Pi value
Things to keep in mind when setting up your simulation models
Resources on Monte Carlo simulations in Excel
Conclusions

Read This »

September 24, 2015

How countries spend their money – chart alternatives

Econimist’s daily chart is a one of my daily data porn stops. They take interesting data sets and visualize in compelling ways. While the daily chart page is insightful, sometimes they make poor charting choices. For example, this recent chart visualizing how countries spend their money uses a variation of notorious bubble chart. Click on the chart to enlarge.

What is wrong with this chart?

Bubble charts force us to measure and compare areas of circles. Unless you have a measuring tape somehow embedded in your eyes and you are a walking human scientific calculator, you would find this task impossible.

So when you look at the chart and want to find out what percentage Japanese spend on restaurants or how much Americans pay for housing, your guesses will have large error margins.

Not only bubble charts are difficult to read, they are very hard to align. So when you have a bunch of bubbles, no matter how hard you try, your chart looks clumsy (see how the Russian food bubble eats in to Mexico’s bubble, as if it is too hungry 😉 )

Let’s check out a few alternatives to this chart. Read on…

Read This »

September 22, 2015

Show forecast values in a different color with this simple trick [charting]

Let’s say you made a chart to show actual and forecast values. By default, both values look in same color. But we would like to separate forecast values by showing them in another color.

If you are a seasoned Excel user, you may be thinking, “Oh, that’s easy. I will just create 2 sets of data (one for actual and one for forecast), make a chart from them and apply separate colors.”

But here is a really simple way to get the same effect.

Use a semi-transparent box to mask the forecast values, as shown above. Read on to learn how to do this.

Read This »

September 16, 2015

Excel Links – Dashboards book delayed edition

Quick update about Dashboards for Excel book:

As you may know, my first print book – Dashboards for Excel (co-authored with Jordan Goldmeier) is supposed to release today – 15th September, 2015. But unfortunately, there is some delay with the printing process, so the book is not available yet. Our publisher, Apress, tells us that it can take two more weeks.