Office Watch

Office 2013

Office Mobile / iPad

Office 2010

Office 2007

Office 2003

Office XP

Office for Mere Mortals

Access

Email

Buying Office

Office 365

Winks

Office News Wire

Join us!

Our Ebooks

Mobile | PDA

RSS


Search

Command Finder


Microsoft Office Bookshop

About

Home




Getting data from Internet into Excel

How to get table or other information from the net into Excel (or Word).

by Office for Mere Mortals

Bookmark and Share

  | Mobile | click for more article services     


 Ever seen some data on the Internet and want to copy it for yourself?  From small tables to large data chunks, there’s several ways to do it.

Simple copying

Almost any table or grid on a web page can be copied into Excel with the web page cells becoming cells in Excel.

For example this, admittedly self-promoting, table:

Title

Normal Price

Subscribers price

Discount

Privacy and Security in Microsoft Office

$16.95

$9.95

$7

Office 2010: the real startup guide

$16.95

$9.95

$7

Access Archon: Working with Excel

$16.95

$9.95

$7

Access Archon: Working with Word

$16.95

$9.95

$7

Effective Outlook Calendars

$7.95

$4.95

$3

Organizing Outlook Email

$7.95

$4.95

$3

Clever Outlook Contacts

$7.95

$4.95

$3

Formatting Magic with Word

$7.95

$4.95

$3

Eye-Catching Signs with Word

$9.95

$6.95

$3

Creative Certificates with Word – premium

$19.95

$14.95

$5

Christmas Cheer with Office – premium

$14.95

$10.95

$4

Valentine’s Day with Word

$9.95

$6.95

$3

Office Backup Handbook

$14.95

$9.95

$5

Simply select the entire table with your mouse pointer. It doesn’t matter if you include some text before and after the table; you can remove the excess later.

Then paste the web table into a blank Excel worksheet.  Here’s the result with the sentence before the table included.

Excel - data copied from table without adjustment image from Getting data from Internet into Excel at Office-Watch.com

This method only copies displayed data, not any formulas.  The last column ‘discount’ could be calculated but no formulas are copied, only the displayed values. 

As you can see, all the web table data is now in Excel cells. The hyperlinks from the table have been preserved as well as the formatting. But it needs a little work to make it useable.

The top row can be deleted to put the table headings in row A.

Column B is too narrow (that’s why the #### marks appear) widen until the numbers show up.

Widen the other columns so the headings don’t wrap in such an unsightly way.

Tip: to automatically adjust a column to fit the contents, put the mouse pointer on the divider between column headings until you get the twin-arrow icon then double-click.  A similar trick works for rows.

Excel - adjust column mouse pointer image from Getting data from Internet into Excel at Office-Watch.com

Choose Data | Filter to make the column headings into data labels which you can pull-down on to filter or sort upon.

Notice that Excel has been smart about the currency values.  The $ sign has been stripped out of the web table text and the cell automatically changed to a currency value with $ prefix in the formatting. 

Excel - dollar value in web table changed to number and currency format image from Getting data from Internet into Excel at Office-Watch.com

It’s best to check that Excel has done this, especially with other, non dollar, currency signs. 

Not only are the numbers imported as the displayed values only (as opposed to higher precision underlying values in an Excel worksheet) but they are imported as text – it’s up to Excel to figure out what the cell values are and change them accordingly.  It can cope with currency and numbers as we’ve seen.  Dates are usually converted too, but that depends on the formatting of the text values.

 

You can use Excel formatting commands to change the look of the data from whatever was on the web page.  The formatting you get depends, in part, on which browser you copy from.  No surprise that the most consistent results come from using Microsoft’s Internet Explorer.

Doctor Who and the curse of the evil worksheets

There are plenty of publically available worksheets available.  Google Docs online spreadsheet application encourages people to make worksheets public.  They make is easy to do in a way that Microsoft does not.

 

Some of the data is serious and important but other worksheets are more quirky.  For example this worksheet of all the villains and monsters from the long running UK series, Doctor Who courtesy of The Guardian.

 

The entire list is available from Google docs here.

Google Docs - Doctor Who villians and monsters image from Getting data from Internet into Excel at Office-Watch.com

You can select cells or the entire list and paste into Excel, as described above.  However that’s clumsy for a large worksheet and doesn’t include any cell formulas.

The easier method is to download the whole worksheet. In Google Docs you can do that from the File | Download | Excel menu,

Google Docs - download Excel version image from Getting data from Internet into Excel at Office-Watch.com

Then you can open the XLS file in Excel and format to your heart’s content. 

Excel - Doctor Who villians and monsters in Excel image from Getting data from Internet into Excel at Office-Watch.com

Downloading a worksheet should also include any formulas.

Data.gov

Not all public data is so frivolous.  Data.gov is a web site full of data sets from the US government.

Many are available as Excel XLS downloads, like these details of oil and gas production.

Data.gov - XLS download example image from Getting data from Internet into Excel at Office-Watch.com

This is very raw data – over 37,000 rows of information and cryptic headings like “conden_prod_BBL”.

There are explanatory documents linked from the download page.

The Guardian Datablog

The Guardian newspaper in the UK has an interesting blog about data sources and presenting them in interesting ways.

For example a visualization of the cause of death in the UK which includes near the bottom, a link to a worksheet with all the raw data.  Readers are welcome to download the data for themselves.

The World Bank

Another data source is The World Bank where you can download Excel worksheets compressed into ZIP files for faster downloading.

 

Found any interesting data sources on the web?

Let us know via our Feedback page – please tell us the web link and the data collections you think are interesting or fun.

 

Going the other way?

We have an Office-Watch.com article on copying data from Excel into a web page.

 

 

Article posted: Tuesday, 01 November 2011

there's more ...

If you liked this article you'll LOVE our new ebooks.

Office 2013: the real startup guide

OFFICE 2013: the real startup guide Everything you need to know about Office 2013 but Microsoft won't tell you.

How to save money, install, configure and use the new features in Office 2013.  Get it today - click here.

Windows 8 for Microsoft Office users

Windows 8 for Microsoft Office users A practical guide the new, changed and unfamiliar in Windows 8

A focused and unvarnished look at Windows 8, especially written for the many people who use Microsoft Office  Get it today - click here.

ORGANIZING OUTLOOK EMAIL - tame your Outlook 2010 Inbox

100+ pages of practical tips and help to streamline, automate and search your Inbox.  Get more than you ever thought possible from Outlook.  Read it today - click here.

More from Office Watch:



Article Services sponsored by: Office Watch Ebooks - available now to download and read today.
RSS feed for this category Subscribe

Translate | Mobile | Links
 Add to: Bookmarks | | DiggThis | Yahoo! My Web


New & Popular
» Excel Online – changing date format
» Office Online update
» Deleting Holidays
» Office Online in Google Chrome
» Windows 8.1 Update – secret changes list?
» Missing Easter from Outlook


Office Watch, Office for Mere Mortals, Access Watch and all titles used within the publications are Copyright © 1996-2014 Office Watch.
Microsoft Office, Microsoft Word, Microsoft Excel, Microsoft Outlook, Microsoft Powerpoint and doubtless many other names are registered trademarks of Microsoft Corporation.

Search  |  Sitemap |  Popular Topics | Privacy Statement |  Advertising |  Twitter |  Feedback / Contact Us
Office Watch is definitely not affiliated with Microsoft - and that's just one reason why we are so useful to Microsoft Office users around the world J (Erko).