Home > microsoft excel > How do you put data from a web table into Excel?

How do you put data from a web table into Excel?

August 20Hits:1
Advertisement

I'm trying to build a spreadsheet from a few different webpages that have data in a table format.

How do you put data from a web table into Excel?

Answers

What you want is a web query. It can be set up to be live (automatically refreshed) or manually refreshed.

In 2003, it's under the Data menu, then Import External Data, New Web Query.

Enter the web address, then find and click on the table to import.

  • Excel 2002 Getting data from the Web in Excel
  • Excel 2003 About getting data from a Web page
  • Excel 2007 Get external data from a Web page

Alternatively, you can copy the static table data. In IE, right-click and Export Table to Excel. In Firefox, you will need an extension like Table2Clipboard.

Easiest thing to do would just to start at the top of the table and end at the bottom.

Copy it and paste it in to a new Excel file, extract the data you require and put it in to the final spreadsheet.

There are a few tricks you may need to do, but excel can understand a wide range of files including web pages, so it should be able to extract the data in a tabular form.

Found a video that explains it for Excel 2000, but Excel 2007 is basically the same.

If you use Internet explorer and have Excel installed, right click on the table and click "Import Into Microsoft Excel".

On Windows with Internet Explorer (versions 6 - 8), I've been able to do this simply by highlighting the entire table, then choosing Edit:Copy (or right-clicking and choosing copy), then pasting into Excel.

On Windows, with Firefox, I can still do it, but it seems a bit trickier -- I feel like I have to highlight a little above the table and a little below it, then trim that in Excel.

I've not even attempted it on Mac, nor with Safari nor Chrome. I imagine in the Windows world, if Safari and Chrome were using the Clipboard correctly, they'd "just work" like Firefox does.

You can take any html file with table, rename it with a "*.xls" extension, and Excel will happily open it up as if it were a native document.

This will sometimes work:

  • Select the web page text in your browser
  • In a blank Excel worksheet, hit Control + A to highlight all columns and rows
  • From the menu, choose Edit > Paste Special and choose unformatted text
  • If it says the areas aren't the same size and shape, tell it to paste anway

If you're lucky, the data will end up in proper columns and rows. You can then re-copy and paste it wherever you want it.

Related Articles

Copyright (C) 2017 ceus-now.com, All Rights Reserved. webmaster#ceus-now.com 14 q. 0.691 s.