Archive for February, 2016

Autosum many ranges quickly with Multi-select & ALT= [quick tip]

Published on Feb 26, 2016 in Keyboard Shortcuts, Learn Excel
Autosum many ranges quickly with Multi-select & ALT= [quick tip]

Let’s say you have data in a worksheet in various ranges, and you want sum up each range at the bottom.

Something like this:

How to do all this one shot?

Simple. We use multi-select & ALT=

Continue »

Analyzing half a million complaints – Customer Satisfaction Scorecard [Part 3 of 3]

Analyzing half a million complaints – Customer Satisfaction Scorecard [Part 3 of 3]

This is the final part of our series on how to analyze half a million customer complaints. Click below links to read part 1 & 2.

  1. Complaint reason analysis – Part 1
  2. Regional trends & analysis – Part 2

Customer satisfaction scorecard

In the previous parts of this case study, we understood what kind of complaints were made and where they came from (states). For the customer satisfaction scorecard, let’s focus on individual companies.

Continue »

Analyzing half a million customer complaints – Regional Trends [Part 2 of 3]

Analyzing half a million customer complaints – Regional Trends [Part 2 of 3]

This is part two of our three part series on how to analyze half a million customer complaints. Read part 1 here.

Analyzing Regional Trends

As introduced in part 1, our complaints dataset has geographical information too. We know the state & zip code for each complaint. Please note that zip codes are partial or missing for a 10% of the data.

In this article, let’s explore three ways to analyze regional trends.

  1. Regional trends by state, product & issue
  2. Complaints per million by state
  3. Complaints by zip code
Continue »

Analyzing half a million consumer complaints [Part 1 of 3]

Published on Feb 16, 2016 in Analytics, Charts and Graphs
Analyzing half a million consumer complaints [Part 1 of 3]

How would you analyze data when you have lots of it? That is the inspiration for this series.

Let’s meet our data – Finance Industry Consumer Complaints

As part of open data initiatives, US government & Consumer Financial Protection Bureau maintain a list of all consumer complaints made against financial institutions (banks, credit unions etc.) You can download this data from the catalog page here. I have obtained the data on 1st of February, 2016. The download has 513,824 records. Each row contains one complaint.

In this and next two parts of the series, we are going to analyze these half a million complaints to find insights.

Continue »

Formula Forensics 040 – Apportioning Sales by Criteria

Published on Feb 15, 2016 in Excel Howtos, Formula Forensics, Huis, Posts by Hui
Formula Forensics 040 – Apportioning Sales by Criteria

Lets look at how to apportion sales according to multiple criteria

Continue »

Not so wild lookups [video]

Published on Feb 12, 2016 in Excel Howtos, Learn Excel
Not so wild lookups [video]

In case, this is the first time you are hearing about Excel formula wildcards, check out the Using wildcards in Excel VLOOKUP formula tutorial.

So you know about wild cards like * ?, now how would you tell VLOOKUP to ignore them?

Say, you are genuinely interested in looking the value “* Payroll” in a lookup table. What then?

This is exactly the problem faced by Peter in our forum post VLOOKUP and cells with “*” NOT to be interpreted as wildcard

Continue »

CP052: Book Review – M is for Data Monkey by Ken & Miguel

Published on Feb 11, 2016 in Chandoo.org Podcast Sessions, Power Query
CP052: Book Review – M is for Data Monkey by Ken & Miguel

In the 52nd session of Chandoo.org podcast, let’s discuss monkeys, Ok, I am kidding. We are going to talk about M is for Data Monkey book.

What is in this session?

In this podcast,

  • Updates: Why so much gap between episodes?
  • Quick introduction to Power Query
  • Why you should get this book?
  • What is in this book?
  • A very cool example of the techniques you will learn
  • Conclusions
Continue »

Use slicers to create a cool selection mechanism [quick tip]

Published on Feb 8, 2016 in Learn Excel, Pivot Tables & Charts

Most advanced Excel users know that slicers are cool. Today, let’s learn how to use slicers to create an awesome selection mechanism for your dashboards and forms.

First see a quick demo

using-slicers-as-selection-mechanism-demo

Looks slick, eh? Read on.

Continue »