You need to bring live information from a website into your Excel spreadsheet. Manually copying and pasting data is slow and prone to errors. Excel’s Get Data feature connects directly to web pages and imports tables automatically. This article shows you how to use this tool to pull data from the web into your workbook.
Key Takeaways: Importing Web Data to Excel
- Data > Get Data > From Web: Opens the connection dialog where you enter the URL of the webpage containing the data.
- Navigator Pane: Shows all tables and lists found on the webpage, allowing you to preview and select which ones to import.
- Load or Transform Data: Loads the data directly into a worksheet or opens the Power Query Editor to clean and shape it first.
What the Get Data From Web Feature Does
The Get Data From Web tool is part of Excel’s Power Query technology. It acts as a bridge between your workbook and a public webpage. The feature scans the HTML structure of the page to identify structured data like tables and lists. It then creates a live query connection that can be refreshed to pull in updated information with one click.
Before you start, ensure you have a stable internet connection. The webpage you want to import from must be publicly accessible without requiring a login. The tool works best with static HTML tables. It may not correctly interpret data displayed through complex JavaScript or interactive web apps.
Steps to Import a Web Table into Excel
- Open the Get Data Dialog
In Excel, go to the Data tab on the ribbon. Click Get Data in the Get & Transform Data group. Hover over From Other Sources and select From Web from the menu. - Enter the Web Address
A dialog box titled From Web will appear. Paste or type the full URL of the webpage containing your data into the URL field. Click OK to proceed. - Select Your Data in the Navigator
Excel will connect to the webpage and analyze its content. The Navigator pane will open on the right. It shows a list of detected tables under the Display Options. Click on each table name to preview its contents in the pane. - Load or Transform the Data
Check the box next to the table you want to import. You have two choices. Click Load to import the data directly into a new worksheet. Click Transform Data to open the Power Query Editor first, where you can remove columns, filter rows, or change data types before loading.
Using the Power Query Editor to Clean Data
- Remove Unnecessary Columns
In the Power Query Editor, select the columns you do not need. Right-click on the column header and choose Remove. You can also select multiple columns by holding Ctrl while clicking. - Filter and Sort Rows
Click the dropdown arrow in a column header to apply filters. For example, filter a number column to show values greater than a certain amount. Use the Sort Ascending or Sort Descending button on the Home tab to reorder data. - Change Data Types
Excel may import numbers as text. Click the data type icon next to a column header, such as ABC for text, and select the correct type like Whole Number or Date. Click Close & Load on the Home tab to apply changes and send the data to Excel.
Common Mistakes and Data Import Limitations
Webpage Requires Login or Has Dynamic Content
The Get Data From Web feature cannot bypass login screens or interact with web forms. It also struggles with data rendered dynamically by JavaScript after the page loads. If your target data is behind a login, this method will not work. For some dynamic sites, using the From Web option in Power Query Desktop, a separate application, might be more successful.
Imported Data Appears in a Single Column
Sometimes web data that looks like a table is not formatted with proper HTML table tags. Excel may import it as a single block of text into one column. To fix this, use the Text to Columns tool on the Data tab after loading. Alternatively, in the Power Query Editor, use the Split Column feature under the Transform tab to separate the text.
Refresh Connection Fails or Shows Old Data
The connection might fail if the website’s structure changes or the URL is no longer valid. To manage this, right-click any cell in the imported table and select Refresh. If it fails, check the original webpage. To update the connection URL, go to the Data tab, click Queries & Connections, right-click the query name, and select Properties to edit the source.
Get Data From Web vs. Legacy Web Query
| Item | Get Data From Web (Power Query) | Legacy Web Query |
|---|---|---|
| Access Path | Data > Get Data > From Web | Data > Get Data > Legacy Wizards > From Web |
| Data Transformation | Full Power Query Editor for cleaning | Basic formatting options only |
| Connection Type | Modern, structured query connection | Simple, static data pull |
| Refresh Management | Centralized in Query & Connections pane | Managed via external data range properties |
| Best For | Repeated imports needing cleaning and automation | One-time import of a simple HTML table |
You can now import live tables from public websites directly into Excel. Use the Data tab to launch the Get Data From Web tool and select your data in the Navigator pane. For your next project, try importing data from a financial website to track stock prices. Use the Refresh All button on the Data tab to update all your web queries at once when you open your workbook.