Importing a webpage table into a Spreadsheet

I came across a webpage which listed New York City as the 8th most expensive city to live in, worldwide.  This is up from 22nd last year, with the rise attributed to the stronger US Dollar.  Los Angeles was 23rd, Miami was 45th and Chicago was 50th.

I wanted to map top fifty cities, world wide, to get a visual feel for where they were located.  I'll walk you through an example with the 2009 top fifty most expensive cities webpage on Mercer's website.  Here's a screen shot of what the webpage looks like:

The first step is create a new Google Spreadsheet.  Next, you must setup the spreadsheet to import data from this webpage.  Now, you don't need to save the web page from Mercer's website to your computer; just tell the spreadsheet to import the data from the URL of the webpage by setting cell A1 to the following formula:

=ImportHtml("http://www.mercer.com/summary.htm?idContent=1311145","table",4)

Here's what the imported table looks like in a spreadsheet.  Using the ImportHtml() function creates a live connection between the webpage and the spreadsheet.  If the data goes away from the webpage, it will no longer show up in the spreadsheet.  Every time you open the spreadsheet the data gets imported again.

From here you can do as you please; Google Spreadsheet's allows you to export the spreadsheet to a CSV or a Microsoft Excel file.  One fun new tool is Google Fusion, which allows you to visualize data in tables.  Fusion allows you to use an excel spreadsheet from your computer or a online spreadsheet.  I imported my spreadsheet above into a Google Fusion Table here, from which I generated a map, accomplishing my goal.

Visually, we can easily see that most of the top fifty 2009 expensive places to live are located in Europe.