Excel Power Query can import data from a web page directly into your workbook, and you can refresh it with one click. You use Data > Get Data > From Other Sources > From Web, paste the URL, then select the tables you want in the Navigator window. This works for tables, lists, and even some dynamic content, though not every website allows it.
What Power Query Web Import Actually Does
Power Query is Excel’s built-in data connection tool. When you import from a web page, it reads the HTML structure of that page and identifies tables (elements marked with <table>) and lists. It does not take a screenshot; it pulls the underlying text and numbers. This means the data you get is as current as the page itself when you refresh.
For example, importing a Wikipedia table of country populations gives you columns like “Country” and “Population” that you can sort, filter, and load into a worksheet. If the page updates, you can right-click the query in the Queries & Connections pane and choose Refresh to pull the latest values.
Step-by-Step: Import Web Data in Excel 365
- Open a blank workbook. Go to the Data tab.
- Click Get Data > From Other Sources > From Web.
- In the dialog, paste the full URL (including
https://) and click OK. - If prompted, choose Anonymous access (most public pages) and click Connect.
- The Navigator window shows a list of detected tables. Click one to preview it on the right.
- Click Load to place it in a new worksheet, or Transform Data to open the Power Query Editor first.
On a Mac, the path is the same: Data > Get Data > From Web. Excel for the web does not support importing from a URL directly; you need the desktop version.
Choosing the Right Table in Navigator
Web pages often contain many tables: navigation menus, sidebars, footers. The Navigator preview helps you distinguish them, but names are generic like “Table 0”, “Table 1”. Look at the preview columns and row count to pick the one that matches your need. If you see a table that contains links or images, you can remove those columns later in the editor.
For pages with no obvious table, Power Query may show nothing. In that case, you can use the From Web option and then click Add Table Using Examples in the editor, but that is more advanced. Most practical uses involve pages that already have structured tables, like financial reports, sports stats, or government data.
Refreshing and Managing the Query
Once loaded, your data is not static. To update it:
- Right-click the query name in the Queries & Connections pane (on the right side of the worksheet) and select Refresh.
- To refresh all queries at once, go to Data > Refresh All.
- To set automatic refresh, go to Data > Queries & Connections > right-click the query > Properties, then check “Refresh every” and enter minutes. This works only while the workbook is open.
If the website changes its layout, the query may break. You will see an error in the query step. Edit the query by going to Data > Queries & Connections, double-click the query, and adjust the steps in the Power Query Editor.
Common Problems and How to Fix Them
The most frequent issue is the error “This table is empty” or “We couldn’t connect”. Here are typical causes and fixes:
- Site blocks automated requests: Some sites require a user agent or cookies. In the Power Query Editor, go to Options > Data Source Settings and set the privacy level, but there is no built-in way to send cookies. Try a different URL or use a public API instead.
- Dynamic content: Pages that load data via JavaScript (like many modern dashboards) will not show in Power Query because it only reads the initial HTML. Use the site’s API if available, or copy the data manually.
- Login required: If the data is behind a login, you can use the “Windows” or “Basic” authentication in the connection dialog, but this is rarely supported for consumer sites.
- URL changes: If the page moves, update the URL in the query source step.
Power Query vs. Copy-Paste vs. Web Scraping Tools
Copy-pasting is faster for one-time data, but it is not refreshable and often brings formatting junk. Dedicated web scraping tools like Python with BeautifulSoup are more flexible but require programming knowledge. Power Query sits in the middle: it is free, built into Excel, and refreshable, but limited to static HTML tables. For a quick, repeatable pull of a table from a public page, Power Query is the best choice. For complex scraping or sites that use JavaScript, a programming approach is better.
Is It Safe? Privacy and Security Considerations
Importing public data is generally safe, but be aware that you are sending a request from your IP address. Do not import data that requires a login or contains personal information. Also, some sites have terms of service that prohibit automated access. For corporate data, check with your IT department. Excel may warn you about connecting to external sources; that is normal. Always verify the data after import, because web pages can contain errors or formatting artifacts.
Frequently Asked Questions
Can I import data from a website that uses JavaScript to load content?
No, Power Query only reads the static HTML that the server sends. If the site loads data via JavaScript after the page loads, you will not see it. Look for an API endpoint or use a browser extension to export the data.
How do I refresh web data automatically in Excel?
In the query properties, you can set a refresh interval (e.g., every 30 minutes). This only works while the workbook is open. For a fully automated refresh without opening Excel, you would need to use Power Automate or VBA.
Why does my imported table show only one column with all the text?
This usually happens when the table is not properly structured, or the site uses divs instead of a real table. Try selecting a different table in Navigator, or use the “Split Column” feature in the editor to separate the data.
Can I import data from a webpage that requires a login?
Yes, but it is tricky. In the connection dialog, choose “Windows” or “Basic” and enter your credentials. However, many sites use session cookies that expire, so the refresh may fail. It is safer to export the data manually.
What is the difference between “Load” and “Transform Data”?
Load imports the data as a table in a worksheet immediately. Transform Data opens the Power Query Editor first, where you can clean, filter, and shape the data before loading. Use Transform Data for any non-trivial cleanup.
Related
- How to Rotate a Table in Excel: 3 Easy Ways
- Excel How to Select Certain Cells: 6 Quick Ways
- Excel Transpose: How to Quickly Flip Data
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel: View Data as a Table and Use Its FeaturesTo view a range as an Excel table, select a cell in the data and press Ctrl+T in…
- How to Freeze a Reference in Excel with Dollar SignsTo freeze a cell reference in an Excel formula, add dollar signs to the cell address—for example, change…
- Excel Care Group: Homes, Services and What to KnowExcelcare, often described as the Excel Care Group, is a UK care provider that operates care homes and…
- Excel Shortcut to Resize Cells to Fit Text and DataIn Excel for Microsoft 365 on Windows, select the columns you want to fit and press Alt, H,…
- Does Google Sheets Have VLOOKUP? How to Use ItYes. Google Sheets has the VLOOKUP function, which searches down the first column of a range and returns…
- Google Sheets INDIRECT in Data Validation: How It WorksYes, you can use INDIRECT to help create dependent dropdowns in Google Sheets, but the reliable approach is…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.