Software

Open Web pages in Excel

Don't manually re-enter data you find on the Web. Open Excel and transfer the data directly into a worksheet.

If you glean data from the Web (and who doesn't these days), chances are that you print the Web page and then re-enter the data manually. Ouch! You should never re-enter data if you don't have to, and in this case, you don't have to. Instead, open the Web page right in Excel, as follows:

  1. Choose Open from the File menu. The Open command is on the Office menu in Excel 2007.
  2. Enter the URL, including http://, into the File Name text box. Excel will probably display a "connecting to server" message. That's fine, don't worry about it.
  3. Choose Web Pages And Web Archives (*.htm; *.html; *.mht; *.mhtml) from the Files Of Type control. In Excel 2007, the file type is All Web Pages (*.htm; *.html; *.mht; *.mhtml).
  4. Click Open.

Excel actually enters the transferred data from the Web page into the current sheet, and you can manipulate the data as you would any other data. Hyperlinks are live -- go ahead and click one! As long as you're connected to the Internet, they should work just fine.

The resulting Excel sheet won't look much like the Web page however. It's a literal transfer of data, and remember, Excel isn't a browser. However, this feature is a quick and easy way to get the raw data you need into a sheet. Delete extraneous graphics and then copy or transpose blocks of data to organize the pieces just the way you want. You can even share the data with other Office applications.

On a related note, you can open a Web page in your browser from inside Excel. Simply display the Web toolbar and enter the URL.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

7 comments
mjbarden_z
mjbarden_z

I like to read these sorts of posts to see if there are any hints as to making the web data import in Excel actually functional in practice. This one describes "how to do it," but the limiting factor for this in practice is Step 2 "Enter the URL" which has to be done manually. Admittedly, I'm using Excel 2003, but it appears that Excel 2007 hasn't improved on this aspect. The lack of the ability to simply copy a URL from a browser and paste it into the File Name text box in Excel is a major flaw that makes this feature unwieldy and non-functional in practice since the URLs that I run into where I'd like to import data are typically long and complicated to transcribe; for example the URL for this post [http://blogs.techrepublic.com.com/msoffice/?p=639&tag=nl.e103] which is a comparatively simple one. I've found that in most cases, by the time I've transcribed a URL correctly into the text box and found the data I want, I could have saved the web page, manipulated the format as needed, and imported it into Excel as text. It's too bad, since either process is a major pain to deal with, all for the lack of simple copy/paste functionality.

elliot_bendoly
elliot_bendoly

Actually there is a pretty quick way to automate URL changes... write a macro. There are examples of the steps needed to get this done in "Excel Basics to Blackbelt" (Cambridge 2008). The code is short and quick.

IdeaDiva
IdeaDiva

" ... and undesputed title holder of 'Laziest Human in the Universe ...." That's me, alright. You want to open a web page, say this one, but who wants to manually type in the URL? Who COULD type in all those characters without an error? I don't; I couldn't. So here's what I do. 1. Have a blank Workbook open. 2. Select this URL, Copy. 3. Alt Tab to your blank workbook (or even a blank worksheet) and Paste. 4. Text is pasted, yes as a hyperlink in the cell, but is displayed in the Formula Bar as text -- which you will now Copy. 5. Ctrl O and in the file name field, Paste. 6. Hit Open and Voila!

sdmagic_z
sdmagic_z

First, this tip is top-notch. After trying it, I immediately went around to other workers here and showed them how to do it. Second, copy and paste works fine. In fact, I didn't even think of typing a URL myself. I tried the tip by opening a web page in IE, copying the URL (Ctrl-C) and pasting the copied URL into Excel's File Open dialog (Ctrl-V). Worked like a charm.

ThreeLittleBirds
ThreeLittleBirds

mjbarden_z I tried this hint and had no problems doing a data import of a web page into my Excel 2003. I was able to copy the URL and paste it onto the file open space of the Open menu using the "CTRL + V" keystrokes. I would prefer clicking the right mouse button but the key combo works for me. See if that works for you.

heavener
heavener

This is a terrific alternative to the lame-o thing that Word does with web pages. For extracting reasonably formatted content from a web page, this seems to be the better alternative than copying from the web page, pasting into Notepad, and then copying the resulting plain text from Notepad into Word. Otherwise, Word gunks up the result with all sorts of left-over script, table, and image artifacts that take forever to hunt down and delete.

mjbarden_z
mjbarden_z

Many thanks for the tip on the cntl-V shortcut. It does in fact work. I'll add that to my monitor sticky notes of Excel tips. Mike

Editor's Picks