Pie of a Pie of a Pie chart [Good or Bad?]

Pie of a Pie of a Pie chart [Good or Bad?]:
There are some charts that look so stunning and yet confusing. You cant make up your mind whether it tells a compelling story or it is just plain wrong.
Today, let me present you one such chart. I call this Pie of a Pie of a Pie chart (source: thiswoo, thanks to Nassar, our forum member for mentioning this)

Pie of pie of pie – What is good about it?

The idea of this chart is quite noble. Show how the water in this world is distributed. Obviously, with such large numbers, making one chart would be difficult. So make 3. The initial pie of salt vs. fresh water, which explodes to show the distribution of fresh water which explodes to show the spread of other water sources.
The chart narrates the story of how little water is available for consumption and where it is one simple picture.
And the pie leaking in to another pie metaphor works really well in this case.

What is not good about it?

To begin with, the 3D pies are very deceptive. It is not clear how much % of water is fresh water (other than the fact that it is very little). Also, compared to a 2d pie, the fresh water slice in 3d pie looks bigger (due to its orientation and 3d effects).
Then, no matter how good the colors are, the 2nd and 3rd pies are confusing. The colors imply that the blue slice (fresh water) corresponds to just ice caps and glaciers.
Since all 3 pies are of similar sizes, we immediately think that we have enough water. Only on closer inspection it becomes obvious that the size of lower pies has no relation to actual water amount (only angles convey the information).
Finally, I think this chart is bad because it takes excessively large amount of time to make in almost any software. That said, it is a good exercise to come up with this tinkering with various drawing shapes etc.

Aren’t there any alternatives?

The easiest and clear (and obviously not so exciting as pie of pie of pie chart) alternative is to just show the numbers like this:

Note: This table too is misleading as row heights wrongly indicate that we have more fresh water than salt water etc.
The next option is to use a variation of bar of bars, some thing like this:

One more option I can think of is a tree map. For hierarchical data like this Treemaps are also a very good option. That said, since the largest value is 390,000 times the smallest value, any treemap will be very large to show the smallest value clearly.

Do you like Pie of Pie of Pie chart?

What do you think? Do you love this or would you prefer some other chart to visualize this data? Share your opinion using comments.

Previously on Pie Charts

Pie charts are one of the most (mis)used charts in business world these days. That is why we talk about them from time to time. See these articles to learn more:

• An example where pie charts work well
• Alternatives to Pie charts
• Group smaller slices in pie charts to improve readability
• Do not make pie charts like these

PS: If you want to know to create this pie of pie of pie chart in excel, see here.

Highlight Quarters, Weekends in pivot reports using styles [quick tip]

Highlight Quarters, Weekends in pivot reports using styles [quick tip]:
Here is a quick pivot table tip.
When reporting summaries by month, it would be better to highlight 3 months at a time (Jan, Feb, Mar in one color, Apr, May, Jun in another color) than showing all in one color. Today, lets learn how to do this in easiest possible way.

Highlight Quarters (3 months at a time) using Pivot Table Styles

We can use pivot table styles for this. Just follow below steps:

1. Select the pivot table which you want to format
2. Go to Design tab & select a pivot table style you want.
3. Right click on the style and choose Duplicate
4. Select “First Row Stripe” from modify screen. Enter stripe size as 3.
5. Repeat this step for Second row stripe too.
6. Click ok.
7. IMPORTANT 1: Apply this new style to your pivot report.
8. IMPORTANT 2: Check the banded rows option from Design tab.

Using this technique, you can also highlight weekends in a different color with first row stripe size = 5 and second = 2. See a demo here.
More on this tip: Customize banded rows / columns in tables
Note: This approach works only when your months start on Jan (or other quarter starts like April, July or October) and days start on Monday. Most business data is like that anyway.

Bonus tip: Generate monthly report from daily data

You can use group dates feature in Pivot reports to generate monthly (or quarterly, yearly) reports from daily data. Learn how to do this.

Do you use Pivot table styles?

Formatting a pivot report is often painful. That is where styles can help us. Once you define the correct styles, your pivot reports will look professional and neat. So go ahead and try them. Share your feedback, tips using comments.

More on Pivot Tables

Along with formulas, Pivot tables are best friends of Excel analysts. They can take massive amounts of data, process and summarize in just a few clicks. To learn more about them, use below resources.

• Tutorial: Introduction to Excel Pivot Tables
• Resource: Pivot tables – examples, tips & information
• Case study: Analyzing non-performing customers using pivot tables
• Feature: Using slicers to setup quick dynamic dashboards
• Training: Join Excel School & become a kick-ass analyst

Show monthly values & % changes in one pivot table

Show monthly values & % changes in one pivot table:
Pivot tables are great help when analyzing lots of data. One of the common questions managers & analysts ask (when looking at monthly sales data for example) is,

How is the monthly performance of our teams (or regions, products etc.)?

A pivot report can answer this question in a snap.

But the answer is incomplete!
Why? Because, we don’t want sum of sales by month & sales person alone. We want to know their performance! Something like below:

Performance eh?!? How to measure it?

There are many ways to measure performance. For our monthly sales data, we can measure performance by comparing,

• Sales with targets
• This month value with previous month value
• This month value with same month last year value
• One person’s sale with rest of team etc.

One of the most common ways to measure performance in situations like this is to see how this months value has changed compared to previous month.

How to show monthly values &  % changes in pivot?

Do you know that with just a few clicks, we can add % changes to our pivot? Follow these steps:
1. Create a pivot report with months & sales persons (or months & products, months & regions etc.)
2. Add Net sales (or any other metric) to value field area of pivot report
3. Now, add net sales once again to value field area
That is right. You can add same metric more than once to pivot table value field area.
At this point, our report looks like this:

4. Right click on 2nd value and choose value field settings.
5. Click on Show values as tab and follow below steps. (see image aside)

1. Choose “% Difference from” from the drop down
2. Select Month as base field
3. Select (previous) as base item
4. Click ok

This will show % changes with respect to previous month in the pivot report!

Extending this to make it even more awesome

1. Clean up the titles

Change the titles to Sales & % change. To do this, just select the first column title and type over. Repeat for 2nd column.

2. Add conditional formatting

Select any cell in the % change column. Go to Home > Conditional Formatting > New rule
[Resource: Introduction to Excel Conditional Formatting, more]
Specify the rule as mentioned in below illustration.

3. Show just icons

We can go one more step and show just icons. Since pivot tables show tool tips on hover, we can easily find % change for any month / sales person by just pointing on that cell.

Finalized monthly report

Our final report looks like this:

Download Example Pivot Report

Click here to download this example pivot report. Examine various settings & conditional formats to learn this better.

How do you use value field calculations in Pivot reports?

Although most of my pivots use simple sum or count type of summaries, often I use custom calculations like % difference from, running total , % of row etc. to understand the data better. These are very simple to setup yet give powerful insights.
What about you? Do you use value field settings to modify your pivot reports? What other summary techniques you use? Please share your tips, ideas using comments.

More on Pivot Tables

Along with formulas, Pivot tables are best friends of Excel analysts. They can take massive amounts of data, process and summarize in just a few clicks. To learn more about them, use below resources.

• Tutorial: Introduction to Excel Pivot Tables
• Resource: Pivot tables – examples, tips & information
• Case study: Analyzing non-performing customers using pivot tables
• Feature: Using slicers to setup quick dynamic dashboards
• Training: Join Excel School & become a kick-ass analyst

Write a formula to check if two dates are in same month? [homework]

Write a formula to check if two dates are in same month? [homework]:
Its Home work time folks. Sharpen your Excel pencils and get cracking.

Find out if 2 dates are in same month

Lets say you have 2 dates in A1, A2.
Q1. What formula tells us if both of them are in same month?
Both dates must be in same month & year!
Go ahead and post your answer in comments.

You think that was easy, try this then:

The above formula would be very easy for seasoned Excel pros. So I have 2 additional questions:
Q2. How to find out if n dates (A1:An) are in same month?
Q3. How to find if n dates (A1:An) are in a given date range – start date in B1, end date in B2?

Post your answers in comments

Go ahead and post your answers in comments. I am eager to see what creative, fun & awesome solutions you can come up with for this problem.
Click here to post your answer.
PS: Click here for more Excel home works, quizzes & challenges.

Clue: Click here for a clue. Got it?

Journey of Hurricane Sandy – Animated Excel Chart

Journey of Hurricane Sandy – Animated Excel Chart:
Hurricane Sandy has taken front seat in all major news channels, papers, websites even in far off places like India. I hope & pray that our readers in US East coast are safe.
Today, lets understand the journey of Hurricane Sandy in this animated chart, prepared by Chris from Excel365.net.

Hurricane Sandy Journey – Animated Excel Chart

How is this animated chart made?

The basic ingredients of this chart are:

• An outline map of Americas
• Data of the storm since it was tropical depression (22nd October) to the time it crossed the coast (30th October)
  
  • Lat & Long of the storm
  • Wind speeds
  • Pressure
• A scroll bar form control that shows only a subset of this data
• A VBA macro that animates the chart

Since all these techniques are previously discussed on Chandoo.org, I am going to list down the process in high-level with links to learn more.

1. Set up a scroll bar form control
2. Based on scroll bar position extract first n values only in to another table. Learn more: OFFSET formula *
3. Use an outline map & bubble chart to plot circles along storm’s trajectory. Learn more: Olympic medals by country since 1900
4. Change scroll bar values from 1 to ‘n’ using a vba macro, when triggered. Learn more: Creating a clock in Excel
5. You are done!

* Chris uses #N/A based technique instead of OFFSET ranges in the chart.

Download this and play with it

Click here to download the workbook & learn by breaking it apart. Examine macros & charts closely and add new techniques to your toolbox.

Do you love this? Say thanks to Chris

I really loved this idea. Very intuitive and timely. Thank you so much Chris for sharing this and teaching us something new.
If you loved this, say thanks to Chris. Check out his website (it is in Chinese) for some useful tricks.

Why am I killing Vitamin XL?

Why am I killing Vitamin XL?:

People who were right a lot of the time were people who often changed their minds.

-Jeff Bezos, Founder, Amazon.com #

About 10 days ago, I have introduced Vitamin XL and asked for your feedback. I got several enthusiastic responses (both for and against it).
I have decided to go ahead and kill this product idea. Please read to understand why.

What is Vitamin XL & Why I thought of it?

Vitamin XL is a membership program that offers Excel training, resources & community access for monthly fee.
During last 1 year, many of existing customers & supporters of Chandoo.org have told me that I should have a membership program where people can access all our training programs, Excel resources & get help for a monthly fee.
A good business person must prioritize the ideas and work on most promising ones first. Since I have few product ideas in mind, I wanted to make sure that Vitamin XL is worth investing my time. As a first step to this, I conceptualized Vitamin XL and shared the idea with all of you on 19th October for your feedback.

How was the feedback?

With in 1 day of announcing Vitamin XL, it was clear to me that not many of you are excited about it. Although there were many who wanted Vitamin XL and could not wait to join, the negative feedback was overwhelming. Some of the concerns raised are,

• There is no value in it. Already Chandoo.org offers everything for free.
• It is too pricey. $30 or $50 per month is just not feasible.
• It would divide our community in to 2 parts – members & non-members.
• The offering is not clear. Can I still access all the features (forums, downloads, articles) of Chandoo.org?

My next options were…

Based on this feedback, I narrowed down to 2 options for future of Vitamin XL.

1. Rework on Vitamin XL’s features & Reduce the price to $99 per year (or $10 per month).
2. Kill it and work on something else.

At first glance, #1 seemed to be a natural choice. This is because I got several emails (and comments) telling me that Vitamin XL would work at lower price point.

So why I am killing it?

What is true loss?
There is no truer loss than nobody telling you when you are making a mistake.

I don’t know where I read this quote, but it came to my mind when deciding the future of Vitamin XL.
As many long time readers of Chandoo.org know, I run a poll / survey whenever I start work on a new product. Excel School, VBA Classes, Financial Modeling Classes, various crash courses are all born like that. And I do know that for any product, there will be some people who will not like it. So I take the negative feedback with a pinch of salt.
But in case of Vitamin XL, I got lot of personal emails, passionate comments telling me it is not a good idea.
The decision became obvious. Its time I let go of Vitamin XL and worked on something else.

What next?

There is no change whatsoever to Chandoo.org or all our awesome stuff.

• I will continue to write every week and share something beautiful, fun & useful with you, so that you can become awesome at your work.
• I will continue to post excellent examples, downloads & videos every now and then so that you can do more & become a hero.
• I will offer new courses, products & books so that you can learn even more and become a ninja.

During next 2-3 months, I will be working on few more new product ideas:

1. Advanced Excel School course (slated for launch in Jan 2013)
2. An iPhone app to learn Excel
3. Conceptualize & Design an MS Access course
4. Upgrade our PM Templates to Excel 2010, 2013
5. Ready to use Excel Dashboard Templates
6. One / two more e-books

As always, I will take your suggestions & feedback about these ideas.
Meanwhile, you can continue to enjoy our courses & products:

• Excel School – for Excel, advanced Excel & Dashboard training
• VBA Classes – for beginner to advanced level VBA training
• Financial Modeling classes – for creating integrated financial models using Excel
• PM Templates – to manage a project like a pro using Excel
• Formula e-book – to learn 75 every day formulas
• Crash courses – to quickly master formulas, project management or dashboard concepts

Thank you & Sorry

I feel so fortunate & humbled to know how much you care about Chandoo.org & our future. Thank you.
My apologies for anyone who felt excited about Vitamin XL and wanted to be part of it. I am sorry I am unable to offer it at this time. I am thankful for your kindness & encouragement. It keeps Chandoo.org going.