How to Import Data From a Website Into Excel

To import data from a website into Excel, use Data > From Web: paste the page address, pick the table and load it. Excel keeps a link to the page, so you can refresh the data later instead of copying it again. This guide is for anyone who tracks prices, rates, schedules or published statistics from the web, and it also covers what to do when From Web can't reach the data.

The quick answer: Data > From Web#

From Web is part of Power Query, Excel's tool for getting and cleaning data (on the ribbon it appears as Get & Transform Data). It reads the tables in a web page and loads the one you choose into your workbook.

If you're new to Power Query, Power Query for beginners explains the editor and its steps.

How to import a table from a website, step by step#

Here is the full process on Windows:

1. Copy the address of the page from your browser, for example https://example.com/prices.
2. In Excel, go to Data > From Web.
3. Paste the address into the URL box and click OK.
4. If Excel asks how to connect, choose Anonymous for a public page and click Connect.
5. The Navigator window lists the tables it found, named Table 1, Table 2 and so on. Click each one to preview it. The Web View tab shows the page itself, which helps you spot the right table.
6. Tick the table you want and click Load.

Excel adds a new sheet with the data as a formatted Excel table, and the query appears in the Queries & Connections pane. You can now sort it, chart it or point formulas at it. To show only some rows, use the filter buttons on the header row; the filter lesson covers them, including totals with SUBTOTAL.

Check the site's terms of use before you import from it regularly, and don't set a very short refresh interval on someone else's server.

Transform the data before you load it#

Click Transform Data instead of Load to clean the table in the Power Query Editor first. The common fixes for web tables are:

• Remove columns you don't need: select them and press Delete.
• Remove header or footer rows: Home > Remove Rows > Remove Top Rows.
• Fix data types: click the icon on a column header and pick Whole Number, Decimal Number or Date. Web tables often arrive as text.
• Trim and clean text: Transform > Format > Trim removes spaces at the ends.

Click Close & Load when you're done. Power Query records each step and repeats it on every refresh, so you only clean once. For when to use Power Query and when formulas are enough, see Power Query vs formulas.

Refresh the data automatically#

A web query is a live connection, so you can update it at any time:

• Refresh everything: Data > Refresh All, or Ctrl+Alt+F5 on Windows.
• Refresh one table: right-click inside it and choose Refresh.
• Refresh on a schedule: in Queries & Connections, right-click the query, choose Properties, and tick Refresh every with a number of minutes, or Refresh data when opening the file.

Formulas that point at the loaded table update with it. If the website changes its layout, the query may break; open it with Edit and adjust the step that fails.

When From Web can't see the data#

From Web reads the tables in the page's HTML. It struggles in three situations:

1. Data drawn by scripts. Many modern pages load their tables after the page opens. Navigator may show no tables or only empty ones.
2. Pages behind a login. Excel can't use the session from your browser.
3. Data that isn't a table, such as cards or lists laid out with styling.

Try these alternatives, in order:

• Look for a download link. Many sites offer CSV or Excel downloads, which are cleaner than any web table. See how to import a CSV file into Excel.
• Use the site's data feed. If the site offers an API that returns JSON, paste that address into From Web. Power Query turns the JSON into a table you can expand.
• Copy and paste. Select the table in your browser, copy it, and paste it into Excel. Right-click and choose Match Destination Formatting to drop the web styles.

Clean pasted web data with TRIM and VALUE#

Pasted web data often looks fine but doesn't calculate. Numbers arrive as text, usually with extra spaces, so SUM returns 0. Here are four prices pasted from a page (the quotes show where the extra spaces are):

Fix one cell with TRIM and VALUE:

=VALUE(TRIM(B2))

TRIM removes the spaces at the ends and turns double spaces inside into single ones, and VALUE turns the text "12.50" into the number 12.5. To total the whole column in one step:

=SUMPRODUCT(VALUE(TRIM(B2:B5)))

The result is 46.5. The TRIM function guide and the VALUE function guide cover both in detail.

Web pages also use the non-breaking space, character 160, which TRIM does not remove. If VALUE still returns #VALUE!, replace it first:

=VALUE(TRIM(SUBSTITUTE(B2,CHAR(160)," ")))

For more causes and fixes, read why numbers are stored as text and how to clean messy data in Excel.

Practise it#

Getting the data in is half the job; cleaning it is the part employers test. Practise cleaning messy text in a live spreadsheet: you'll use TRIM, PROPER and SUBSTITUTE, and each formula is checked instantly. Then practise filtering a table, or total the pasted prices in the exercise on this page.