fbpx

All articles with 'downloads' Tag

Unpivot data quickly with Power Query [tutorial]

Published on Sep 29, 2015 in Pivot Tables & Charts, Power Query
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.

Continue »

CP045: Introduction to Monte Carlo Simulations in Excel

Published on Sep 24, 2015 in Chandoo.org Podcast Sessions
CP045: Introduction to Monte Carlo Simulations in Excel

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
Continue »

How countries spend their money – chart alternatives

Published on Sep 22, 2015 in Charts and Graphs

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…

Continue »

Cropped chart: when some values are too big to fit

Published on Sep 9, 2015 in Charts and Graphs
Cropped chart: when some values are too big to fit

We know that column charts are excellent for presenting information. But what if some of the columns are too tall and hijacking the rest. In a previous article, we discussed few of the approaches. Today let’s learn how to build a cropped chart (broken y-axis chart) using Excel, something like above.

Looks interesting? Read on.

Continue »

Case Sensitive Lookups

Published on Sep 7, 2015 in Excel Howtos
Case Sensitive Lookups

We all know that VLOOKUP (and its cousins MATCH, HLOOKUP and LOOKUP) are great for finding information you want. But they are helpless when you want to do a case-sensitive lookup.

So how do we write case sensitive VLOOKUP formulas?

Simple. We can use EXACT formula.

Continue »

How to create cascading drop downs in Excel – video

Published on Aug 31, 2015 in Excel Howtos, Learn Excel
How to create cascading drop downs in Excel – video

Cascading drop downs enhance usability of your dashboards & interactive workbooks. A cascading drop-down is a 2 or more level selection mechanism. When you have 100s of selection choices, instead of creating one massive drop down or combo-box, you can set up multiple levels of drop downs, so that users can narrow down their selection. For example, users can select Country, State and then City using cascading drop downs.

There are many ways to setup cascading drop downs. You can use formulas coupled with either data validation or form controls. You can also use Slicers. In this video we will review these techniques.

Continue »

Make a quick funnel chart in Excel to track sales performance [video]

Published on Aug 29, 2015 in Charts and Graphs, Learn Excel
Make a quick funnel chart in Excel to track sales performance [video]

Funnel charts are useful to visualize sales & marketing performance. In this brief video, let’s understand how to make a quick funnel chart in Excel.

Read on to learn how to make funnel charts & to download a template for your funnel analysis needs.

Continue »

Monthly Planner Template [downloads]

Published on Aug 28, 2015 in Templates
Monthly Planner Template [downloads]

Here is an awesome planner template to help you manage activities over a month. It is useful for charity drives, activity planning, school schedules, marketing initiatives, project planning etc.

Read on to download a copy of the template & learn how to use it.

Continue »

How to use GETPIVOTDATA with Excel Pivot Tables

Published on Aug 26, 2015 in Pivot Tables & Charts
How to use GETPIVOTDATA with Excel Pivot Tables

Pivot tables are very powerful analysis tools. They can summarize vast amounts of data with just few clicks. But they are lousy when it comes to output. Imagine the horror of putting a pivot table right inside your beautiful dashboard. One refresh could ruin the layout and create half-an-hour extra work for you.

How to combine the power of pivot tables with elegance of your dashboards?

The answer is: GETPIVOTDATA()

Continue »

Summarize only filtered values using SUBTOTAL & AGGREGATE formulas

Published on Aug 25, 2015 in Learn Excel
Summarize only filtered values using SUBTOTAL & AGGREGATE formulas

We all know the good old SUM() formula. It can sum up values in a range. But what if you want to sum up only filtered values in a range? SUM() doesn’t care if a value is filtered or not. It just sums up the numbers. But there are other formulas that can pay attention […]

Continue »

Dynamic Print Areas

Published on Aug 24, 2015 in Excel Howtos
Dynamic Print Areas

Yesterday, you learned about Print Areas – a time & paper saving feature of Excel. While print areas are great, you can only set up one print area per sheet. What if you want to print either report or data based on user selection? 

In such cases, you can set up dynamic print areas. 

That is right. See above demo to understand how it looks. Read on to learn how to set up dynamic print areas.

Continue »

How to import web data to Excel using Power Query

Published on Aug 21, 2015 in Power Query
How to import web data to Excel using Power Query

Power Query offers many ways to get data to Excel. One of them is to Web Data import feature. Let’s understand how this works by importing world stock exchange closing data from Google Finance website.

[Related: Introduction to Power Query]

Continue »

Dashboard best practice: Highlight user selection [video]

Published on Aug 20, 2015 in Charts and Graphs, Learn Excel

Here is a best practice to improve your dashboard usability. If you have an interactive dashboard, highlight user selections thru conditional formatting.

Check out below quick video to understand what this means.

Continue »

Use mail merge to create custom letters, invoices, labels and more

Published on Aug 17, 2015 in Excel Howtos
Use mail merge to create custom letters, invoices, labels and more

The idea of mail merge is simple & powerful. Imagine you handle recruitment at a large company. You just hired 300 analysts for the big data division. The next job – generate employment offer letters for each of them. Of course, you don’t want to type these letters one at a time. You have the details of all the 300 offers (name, email, address, offer code, role, benefit package details and date of joining etc.) in a spreadsheet. You also have a template of the offer letter.

Enter mail merge. This powerful feature of MS Word combines (Excel) data with Word document contents to generate all the 300 letters in just few minutes.

Continue »

A simple trick to make your dashboards user friendly [video]

Whenever you have a dashboard that is quite long or spans across multiple worksheet tabs, it can be hard to use. Here is a simple trick to make your dashboards user friendly. If your dashboard has form controls, create duplicate sets of them and place them in locations where users are looking. For example, If your […]

Continue »