Another Thing You Didn’t Know About Excel: Integrate Online data Into Your Spreadsheets

Microsoft Excel has a really cool feature that allows you to import data directly from a website. That feature is called “web query” and you can use it to integrate live information into your spreadsheets or just look at data and analyze it in a different way than it was originally presented on the website.

In this post we’ll learn how to built an excel query in a couple of clicks. As an example, I’ve created a single spreadsheet with the top 1,000 twitters (based on data from Twitterholic), which you can download.

Create an excel web query

1. Open a new Excel workbook.
2. Open a website and Select with your mouse the data to import. Copy it, and paste into the workbook.
3. At the lower right end of the pasted area you will see the “Paste Options” button, which looks like that . Click it and choose “Create Refreshable Web Query”.
4. A new window will open, called “New Web Query” (see image below) . Once it is done loading, little yellow boxes with an arrow inside them will show up. Click the arrow next to the data you which to import.


New Web Query window

5. Click “Import“. Within a few seconds, the data from the website will appear in your Excel file.
6. Control the format and layout of the imported data and its refresh rate by right clicking the data area and choosing “Data Range Properties…” .

Repeat this process to import data from different sources or pages in the same website.

Top 1,000 twitters in a single spreadsheet

I used a web query to import the top 1,000 twitters from Twitterholic. You can download the file here: top 1000 twitters . I defined the file to refresh every time it is opened, so it should always be updated. If it doesn’t, simply right click the table and choose “refresh”.

The nice thing about it is that the links to the original data exist, so you’ll get directly to the page of the top twitter you choose from the list.

Do you use excel web query or have interesting uses for it? Tell us in the comments!

Related posts:

1. How to Import Your Contacts to a Web Service without Giving Away Your Email Password
2. Excel Secrets Discovered – 6 Steps For Perfect Printing
3. 6 Web Apps to Instantly Capture, Share, Display and Meet
4. The Online Collaboration Tools Guide
5. How to Convert MS Word to HTML