Ads 468x60px

Thursday, March 28, 2013

There is an Easter egg in this chart!

There is an Easter egg in this chart!:
Can you find an Easter Egg in this chart?Do not worry, you are not time traveling or seeing things. Its just that, this year I have decided to publish our Easter Egg a few days early.
And oh, I have 3 reasons for it:
  1. 2 of my favorite festivals – Easter & Holi (a festival of colors, celebrated in India) are this week. Holi is today (Wednesday) & Easter on Sunday.
  2. My kids are super excited about Holi as this is the first time they will be playing it. So we have family time from today until Wednesday and I do not feel like writing a blog entry on Friday :)
  3. I like to have 3 reasons for everything.
Hence the Easter Egg is advanced a few days. But it is just as fun (or may be better) as previous Easter eggs.

Easter Egg 2013:

  1. Download this file.
  2. Find the Easter Egg

Previous Easter Eggs

If you want few more challenges, try our Easter eggs from past – 2009, 20102011 & 2012.
PS of new readers of Chandoo.org: Every year, around Easter time, I hide Easter egg(s) and our community searches for them. It is a fun adventure and along way you might learn something cool about Excel. So jump in and Enjoy.
PPS: Oh yea, Happy Holi to our Indian friends & Happy Easter to all.

Join my Excel Dashboard Masterclass in USA [May-June 2013]

Join my Excel Dashboard Masterclass in USA [May-June 2013]:
Hello folks,
I have a super exciting news to share with you all. I will be conducting a series of Masterclasses on Advanced Dashboards & Excel in USA this Summer (May – June 2012). So if you live in (or nearby) Chicago, New York, Washington DC or Columbus OH, and want to one up your dashboard skills, you know what to do next.
I am conducting these masterclasses in partnership with PowerPivotPro.
Advanced Excel & Dashboards Masterclass by Chandoo.org - USA 2013

What is in this Masterclass & Who should attend?

This 2 day masterclass is aimed at managers, analysts, reporting professionals, executives in sales, marketing, customer service or anyone who wants to learn about Excel Dashboards.
In this masterclass we will be learning,
  • Day1: Our first dashboard in Excel
    • Excel formulas for dashboards
    • Applying advanced conditional formatting & sparklines for dashboards
    • Building our first dashboard – hands-on exercise
  • Day 2: Advanced dashboard skills
    • Selecting right charts for any situation
    • Enhancing dashboards with Excel 2010 & 2013 – sparklines, slicers, timelines
    • Customizing Excel charts – top 5 problems
    • Building our second dashboard – hands-on exercise
    • Q&A
I will also be conducting another 2 day masterclass on PowerPivot & Data Analysis in Columbus OH. The agenda for this is,
  • Day1: Data analysis with Pivot & PowerPivot features


    • Excel formulas for analysis
    • Excel Pivot tables (2010, 2013)
    • Introduction to PowerPivot & Case study
    • Using DAX to analyze data
  • Day 2: Advanced Analysis thru PowerPivot


    • Selecting right charts for any situation
    • Building PowerPivot Dashboard
    • Top 10 PowerPivot problems & solutions
    • Q&A

Download our Masterclass Brochure:

For complete details about the masterclass, please download our brochure.

Benefits of this Masterclass – what you will get?

These are the benefits of the of our Masterclass:
  1. Learn the process to create robust, easy-to-maintain Excel Dashboards
  2. Build better & error-free Excel reports
  3. Know how to choose right charts based on your need
  4. Understand design principles that make your work stand out
  5. Discover some of the less commonly-known Excel features
And you will get this by enrolling:
  • Two full days of tuition by me
  • Use of workstations, lunch and refreshments
  • USB stick with Example workbooks, PDF guides & videos
  • Access to selected videos & material on Chandoo.org
  • Email support for Excel questions for 3 months following the course by Chandoo.org

When is it?

The venues and dates of the masterclass are,
  • Chicago 30, 31 May (Thu, Fri)
  • New York 6, 7 June (Thu, Fri)
  • Washington DC 13, 14 June (Thu, Fri)
  • Columbus OH 18, 19 June (Tue, Wed)
  • Columbus OH 20, 21 June – PowerPivot class

Masterclass fees & Enrollment

This masterclass is US $1,200 per participant.
Early bird discount: If you sign-up before May 8th, you get $200 discount. Hence, you will pay $1,000.

Enrolling for the Masterclass

Please select your city & date choice to enroll in this class. Hurry up as we have limited seats in each venue.




Course Options














More payment options

If you would like to pay by invoice / PO / bank transfer etc. please email jocelyn@powerpivotpro.com for details.
Use the subject “AED 2013 – Chandoo.org” for quick response.

Bulk discounts & corporate in-house training

  • Bulk discounts: If you enroll 3 or more delegates, you get 20% discount on total fees. Write to Jocelyn (jocelyn@powerpivotpro.com) for instructions.
  • In-house training: If you want me to conduct in-house training for your team, write to me at chandoo.d@gmail.com. I have a few training slots available in May-June 2013.
For more details, Please download our brochure.

Thank you so much for making this happen

I am really excited about my trip to USA this summer. Finally I can meet many of our long-time readers & supporters. I am so thankful to you for supporting Chandoo.org all along & making this happen. :)

How well do you know your Lookups? [Quiz]

How well do you know your Lookups? [Quiz]:
Excel VLOOKUP QuizIts quiz time folks.
So you think you know VLOOKUP formula? Well, test your knowledge.
Click here to take our first ever quiz – on Look up formulas.
Hint: Check out Comprehensive guide to LOOKUP formulas for help
Once you finish the quiz, enter your name & email address to record your results in leader-board.
PS: More quizzes & problems in Excel.

Tuesday, March 19, 2013

Finding Nearby Zipcodes using Excel Formulas

Finding Nearby Zipcodes using Excel Formulas:
Finding nearby zipcodes using Excel 2013 Web formulas
Recently, I had a peculiar problem. I have a list of zip codes and I wanted to find out nearest zip codes for each of them.
Now, If I wanted to find out near by zip codes for one area, I could go and search in Google. But, how to do it for dozens of them?
Today, lets understand how you can use Excel (that’s right) to do this automatically. We will be using Excel 2013 for this.
Excel 2013 Web Formulas - an overview

A little background – Excel 2013 Web Formulas

In Excel 2013, Microsoft has introduced 3 powerful new formulas. These will help you fetch & parse XML / HTML data from web. The formulas are,
  • ENCODEURL: to encode web URLs (replaces special characters in URLs with % codes like space becomes %20, / becomes %2F etc…)
  • WEBSERVICE: connects to a webservice / website and fetches response as XML / HTML.
  • FILTERXML: extracts a portion of XML/HTML using specified XPATH.
Using these formulas and web services, we can quickly fetch near by zipcodes for any input value.

Step 1: Find a web-service that can tell us near by zipcodes

I am sure there are many web sites that can offer a service like this. After searching a while, I came across a website called as geonames.org which has many webservices around address / zip code search. The service I have used is,
Find nearby postal codes.
This service is available as XML & JSON. Since Excel 2013 formulas only process XML data, I went with XML service. The service API url is this:
http://api.geonames.org/findNearbyPostalCodes?postalcode=ZIPCODE&country=US&radius=15&username=UNAME&maxRows=10
ZIPCODE is where you enter the zipcode from which you want to find nearby zipcodes
UNAME is where you enter your user name for geonames.org. Click here to register with geonames.org.

Step 2: List all original Zip codes in a column

This is simple. Just paste all original zip codes in a column.

Step 3: Write WEBSERVICE Formula

First enter the API URL in a cell like B1. (Make sure your user name is included in the service url)
Now write WEBSERVICE formulas so that we can fetch XML listing for each of the zip codes. Assuming zip codes are in A3:Ax, in adjacent column write =WEBSERVICE(SUBSTITUTE($B$1,”ZIPCODE”,A3))
And drag it down to fill down the formula for all zipcodes.

Step 4: Write FILTERXML formulas

Now that we have full XML corresponding to each zip code, we need to parse this XML to extract the nearby zip code numbers. The original XML looks something like this:
XML output provided by geonames.org - Finding nearby zip codes using Excel 2013 Web formulas
To extract the zipcodes alone, we need to use FILTERXML formula.
FILTERXML takes 2 inputs – XML text, Xpath.
XML text is what WEBSERVICE has generated.

XPATH will tell Excel, which portion of XML to extract.
What is XPATH?
If you imagine XML as a tree, then XPATH is the language you use to tell how to navigate to a certain node in that tree. Since XPATH is a complex world, I think explaining all the syntax & nuances can be hard. So I will leave you with 2 useful links.
So what is the XPATH for nearby zip code.
As you can see in above image, the response from geonames has 10 code nodes, each containing one zip code (in the postalcode child node).
If we write =FILTERXML(b3,”/geonames/code/postalcode”) we will get all the postalcodes as an array.
Since Excel cannot show arrays in cells, it will show one of the 10 values.
So we need 10 cells to show these 10 zip codes. Once you have 10 cells, you can use either INDEX formula or alternative XPATH syntax (/geonames/code[1]/postalcode for first code, ../code[2]/.. for second code etc.) to extract all the 10 zip codes.

Things to keep in mind

Web formulas (WEBSERVICE formula to be specific) can be really slow depending on your net connection and webserver speeds. Since for most data, we do not need a live connection once the data is fetched, it would be good idea to replace WEBSERVICE formula with results once you have the XML.
Also, working with XPATH can be frustrating if the source XML is not correctly formatted or you are not familiar with right XPATH commands. In such cases, use SUBSTITUE or Text formulas to strip away un-necessary portions of webservice text before feeding it to FILTERXML.
Last but not least, Web formulas are compatible only with Excel 2013 or above. So you need to replace all formulas with results when emailing the workbooks to colleagues who are using older versions of Excel.

Download Example File – Finding Nearby Zipcodes

Click here to download the Excel workbook. Play with it to understand how the formulas are working. Please note that this file is protected as I do not want you to use my username for geonames.org.

Do you use Excel Web formulas?

Although Excel 2013 includes only 3 web formulas, they can let us do several interesting things. I am playing with them often to see what additional uses we can put them to.
What about you? Have you used Excel 2013 web formulas? What is your experience like? Please share using comments.

More on using Excel to get data from web

If you often need data from external websites for your Excel analysis work, check out below articles too:

How to remove all cells containing John (or anything else) [Quick tip]

How to remove all cells containing John (or anything else) [Quick tip]:
Here is an interesting question someone asked me recently,
If I have to delete all rows with “John” in it. Do you know how to do it? 
Well, it looks like they really hate John. But it is none of my business.
So lets go ahead and understand a dead-simple way to get rid of all cells with John or whoever else you fancy.
  1. Select all your data
  2. Press CTRL+F and search for “John”
  3. Click on “Find all” button
  4. Now, select the first result.
  5. Scroll down, holding SHIFT key, select the last result. This will select all search results.
  6. Close the Find box.
  7. Press CTRL - (to delete cells)
  8. Select “Entire row”
  9. Click ok
  10. Now poor John is history!
See this demo to understand:
How to delete all cells with a specific value in Excel?

More tutorials on removing stuff…

If you often find yourself with more data than you need & spend a lot of time deleting Johns from your data, then you will find below tutorials very useful.

PS: As I was traveling in last 2 weeks, I could not write often. Starting Monday, you will see your favorite awesome Excel tips regularly.

Wednesday, March 6, 2013

Finding Conversion ratio using Pivot Table Calculated Items

Finding Conversion ratio using Pivot Table Calculated Items:
Today, lets understand how to use Calculated items feature in Pivot tables. We will use a practical problem many of us face to learn this feature – ie calculating conversion ratio from a list of sales calls.
This is inspired from a question posted by Nicki in our forums,
I have a spreadsheet source data full of sales enquiries which have the Status – Lost, Booked or Pending. Each sales enquiry relates to a particular location. I have created a pivot table which counts the enquiries and displays them with the Locations in rows and the Status in the columns. I have got a row total showing the total number of sales enquiries for each location. I also want my table display the sales conversion number, ie the booked enquiries as a % of the total enquiries. How do I do this?

A look at the data

Lets say, you have some data like this and you want to understand what is the conversion ratio by location.
Calculating conversion ratios from sales enquiry data

Setup a pivot table

The first step is to just create a pivot table from this data. Put locations in row labels area, status in column labels are and ID in values area. Now you will have a count of items for each status in each location. Something like this:
Frist version of pivot table - showing distribution of items by status

Add a calculated item to get conversion ratio

Now we want to calculate how much percentage is “booked” status items in all items for a location. To do this,
  1. Select any column label item in the pivot table.
  2. Click on Pivot Options > Fields, Items & Sets > Calculated item

    Inserting calculated item from pivot table options ribbon
  3. Give your calculated item a suitable name like Conv. %
  4. Write the formula = Booked / (Booked + Pending + Lost)

    Calculated item for conversion percentage - Excel pivot tables
  5. Click ok.
Now you should see another column in your pivot table with calculated item – Conversion %.
Conversion ratio calculated - but shown as number...!

Formatting Conversion % in Percentage format

While we got what we wanted, it is not looking alright. We need to format the conversion % so that it looks alright. For this,
  1. Right click on any value in pivot table
  2. Custom number formatting rule to show conversion ratios in %sGo to value field settings
  3. Click on number format
  4. Select custom
  5. Type the custom formatting rule [>=1]0;[<1]0%;”"
  6. This will automatically transform all numbers smaller than 1 (ie all conversion %s) to percentage format while keeping everything else normal.
  7. Done!
Resource: Learn more about custom number formatting

A video tutorial explaining this & more

Since calculated items can be somewhat tricky, I made a short video explaining how this works. In the video you can also see how to use Power Pivot measures to calculate conversion ratios easily. Watch it below (or on our youtube channel).

Download Example workbook

Click here to download example workbook. It has both regular and powerpivot based calculations. Go ahead and examine them. Enjoy.

Do you use Calculated items?

I find calculated items to be very tricky to work with. In most cases, I try to add extra calculations to original data table or use formulas instead. But this example is a good case where calculated item is perfect.
What about you? Do you use Calculated items? In what situations you use them? Please share your experiences and tips using comments.

Convert your self to a Pivot table pro…

If you are use Excel pivot tables & data analysis features, then you will find below resources very useful.