Import Fully Formatted Website Tables into Excel …

Find a cool or interesting table on a website and wish you could easily bring it to Excel? Guess what…. You can, and it is easy….

  1. Start by copying the URL of the webpage with the table you want.

  2. Next, open a new, blank worksheet or an existing workbook you want to insert the table into.

  3. In Excel 2003 or earlier, point to Import External Data from the Data menu, and then click on New Web Query.
    In Excel 2007, click on Data. Find the Get External Data panel on the Data Ribbon, and choose From Web.

  4. In the New Web Query dialog, delete any address that is showing in the Address bar. Paste the URL that you copied earlier. You might need to use the Paste keyboard shortcut (Ctrl+V), as your right click menu probably will not show up. Once you have your web address in the box, click Go. The webpage will show up in the query box.

  5. Click the little arrow to the left of the table or tables (or any other data) you want to import into Excel and assure the table is highlighted.

  6. If you want to keep the formatting and any hyperlinks for the table on import in Excel 2007, click the Options Tab in the upper right corner and choose radio button; Full HTML Formatting and click OK and then click the Import button.

  7. In the Import Data box, indicate what cells you want to use to contain the table. Excel will “guess” for you, but you can change it if you would like to. It will default to the existing worksheet you are working in, but you will also have the option to create a new worksheet.

  8. Click the Properties button for options such as Query Definition, Refresh Control and other formatting.

  9. When you are ready, click OK and the table will be imported for you!Light bulb Another Tip From:

    Microsoft Store

    This site uses Akismet to reduce spam. Learn how your comment data is processed.