So you are on a great website that has some awesome table that you want to put into Excel. You highlight the table and paste it into Excel and…it put all the data into one cell rather than formatting as a table. Ugh!
Never fear. Here is a simple trick that works most of the time.
First, if you have not mastered the basic shortcut keys, what are you waiting for? Almost every PC program uses these universal shortcuts:
<ctrl> + X cut
<ctrl> + C copy
<ctrl> + V paste
Highlight the table you want to copy from the internet and <ctrl> + C
Now click into Excel and right mouse click on your starting cell. Select “Paste Special” from the right mouse click drop down. Then select “Text” from the options. This usually formats the table as a table in Excel.
There have been times when it does not work properly. Sometimes, it will format the table with some of the data rows misaligned. Don’t panic. Just add or subtract cells by inserting or deleting them at the top of specific columns.
The above way is one way to skin a cat. It is probably the fastest way to copy and paste the information. But you can also use Excel to copy the information, provided you are using Excel 2007 or later. Here are the basic steps.
Navigate to the website in your web browser
From within Excel, click the Data ribbon and select “From Web”
Copy and paste the URL from your web browser into the Excel web browser window that you opened in Step 2 (If the website is password protected, you can enter your credentials thru Excel)
Every table on the website can be brought into Excel by clicking on the arrow that is in the top left corner of the table (the arrow changes to a checkmark when you select it)
Click “Import” at the bottom of the Excel web browser window and either put the data in the default location or select another starting cell
If you have any questions or updates for this article, I would love to hear your thoughts. Drop me a note at firstname.lastname@example.org.