Login to online classes

Month: September 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.

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

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.

20150912_woc650_1

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…

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.

Excel Links – Dashboards book delayed edition

Quick update about Dashboards for Excel book:

Dashboards for Excel - advanced Excel, analysis, reporting & automation book from Jordan Goldmeier and Purnachandra DuggiralaAs 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.

Thanks to all of you who pre-ordered this book. I am sorry for this unexpected delay. Just few more weeks and you will be reading it. 🙂

Time for another round of Excel links