To import data from a URL in Excel, use Power Query’s From Web command: go to the Data tab, click From Web, paste the URL, and then select the table(s) you want to load. This built-in feature (available in Excel 2016 and later, including Microsoft 365) lets you pull data from web pages, APIs, and CSV files directly into your workbook, with options to refresh the data later.
What Is Power Query and Why Use It for URLs?
Power Query is Excel’s data connection and transformation engine. It lets you connect to external data sources—including web URLs—clean and reshape the data, and then load it into a worksheet or the Data Model. Unlike a simple copy-paste, Power Query remembers the connection and can refresh it with one click, so your data stays up to date. For URLs, this is especially useful for pulling live tables from websites, JSON APIs, or CSV files hosted online.
How to Import Data from a URL (Step-by-Step)
Here’s how to do it in Excel for Microsoft 365 on Windows. On Mac, the steps are similar but the menu is under Data > From Web in the ribbon (Excel for Mac 2016 and later).
- Open a blank worksheet.
- Go to the Data tab.
- In the Get & Transform Data group, click From Web.
- In the dialog box, paste the full URL (e.g.,
https://example.com/data.csv). - Click OK. Power Query connects to the page and shows a preview.
- If the page has multiple tables, choose the one you want from the Navigator pane on the left.
- Click Load to load the data into a worksheet, or Transform Data to open the Power Query Editor first.
For a CSV file, the Navigator will show the file contents directly. For a JSON API, you may need to click Transform Data and then expand the records using the expand icon in the column header.
What Types of URLs Work Best?
Power Query can handle most public URLs, but the structure matters. Common formats that work well:
- HTML tables – Any webpage with
<table>tags, like Wikipedia or government statistics pages. - CSV files – Direct links to .csv files hosted on a server.
- JSON/API endpoints – Public APIs that return JSON, such as weather data or currency rates.
- XML feeds – RSS or sitemap files.
Power Query will try to detect the structure automatically. For HTML pages, it lists all tables it finds in the Navigator. For JSON, it shows the raw data as a table with columns for each field.
Refreshing and Managing the Connection
Once loaded, your data is linked to the URL. To update it later:
- Right-click anywhere in the loaded table and choose Refresh.
- Or go to Data > Refresh All to refresh every connection in the workbook.
You can also set automatic refresh intervals if you’re using Power BI, but in Excel desktop, refresh is manual unless you use VBA. To see or modify the connection settings, go to Data > Queries & Connections (on the right pane), right-click the query, and choose Properties.
Common Problems and How to Fix Them
A few issues come up often when importing from URLs:
- “This webpage is not accessible” – The site may block automated requests, require a login, or be temporarily down. Try opening the URL in a browser first. If it works, try using the Advanced option in the From Web dialog to specify HTTP headers (like a User-Agent) if needed.
- Data is nested or messy – Use the Transform Data button and then the Expand icon (arrows) on columns to flatten nested JSON or HTML tables.
- Refresh fails later – Some websites change their structure or block repeated requests. Check the URL still works in a browser, and consider using an API instead if available.
- Only one table is detected – If the page has multiple tables, the Navigator shows them all. If you don’t see the one you want, click Transform Data and use the From Web connector again, or use the Data Source Settings to adjust.
Power Query URL vs. Other Methods
You might wonder why not just use Excel’s old Web Query (from the Data tab in older versions) or copy-paste. Here’s a quick comparison:
- Copy-paste – Quick but static; no refresh, and formatting may break.
- Legacy Web Query – Still in Excel, but less flexible and harder to edit than Power Query.
- Power Query – More powerful, supports transformation steps, and refreshes reliably. It’s the recommended method for any recurring data import.
For a one-time import, copy-paste is fine. For anything you’ll need again, Power Query is the right tool.
Safety and Privacy Considerations
When importing from a URL, be aware of the following:
- Public vs. private data – Only import data you have permission to use. Check the website’s terms of service.
- Credentials – If the URL requires authentication, Power Query will ask for credentials. These are stored in the workbook’s connection settings, which can be a risk if you share the file. Avoid embedding passwords; use a service account or a token with limited permissions.
- Malicious content – A URL could lead to a page with malicious scripts, but Power Query only reads data, it does not execute scripts. Still, only import from trusted sources.
- Data volume – Large pages can slow down Excel. Use the Navigator to select only the table you need, and consider filtering rows in the Power Query Editor before loading.
Frequently Asked Questions
Can I import data from a URL that requires a login? Yes, but you’ll need to provide credentials. In the From Web dialog, click Advanced, then set the authentication type (Basic, Windows, or API key). The credentials are stored with the connection, so be careful when sharing the workbook.
Why does my refresh fail after a few days? Websites often change their HTML structure or block automated requests. Check if the URL still works in a browser. If it does, try using the Advanced option to add a User-Agent header (e.g., Mozilla/5.0) to mimic a browser.
Can I import data from a URL into Excel for the web? No, the Power Query From Web connector is not available in Excel for the web. You’ll need to use the desktop version (Windows or Mac) to create the query, and then the data will appear in the web version after you upload the file.
How do I import a JSON API that returns nested data? After connecting, click Transform Data. In the Power Query Editor, find the column with the nested list (it may show as Record or List). Click the expand icon (arrows) in the column header, and choose the fields you want. Repeat for deeper levels.
Is there a limit to how many rows I can import? Excel has a row limit of 1,048,576 per worksheet. Power Query can load more into the Data Model (if you choose Add to Data Model), but for a worksheet, you’re limited. If your data exceeds that, filter it in the Power Query Editor before loading.
Can I schedule automatic refreshes? In Excel desktop, you cannot schedule refreshes without using VBA or a third-party tool. However, if you publish the workbook to Power BI, you can set a scheduled refresh in the Power BI service.
Related
- Excel Mobile Repair Grand Rapids: Fast Phone Fixes
- Excel Month of Date as Text: 5 Easy Ways
- Free Excel Mileage Log Templates: 7 Real Options
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Find the Highest Number in ExcelIf your numbers are in A2:A6, enter =MAX(A2:A6) in an empty cell to return the largest one. For…
- Excel Conditional Formatting: Color Rows by Cell TextYes. In Excel for Microsoft 365, select the rows you want to format, create a formula-based conditional formatting…
- Delete a Page in Word: Can You Remove One?Yes. In Word for Microsoft 365, you can select the contents of a page and delete them, but…
- Excel Merge Blank Cells Below: Steps and AlternativesTo merge blank cells below a value in Excel for Microsoft 365 on Windows, select the cell containing…
- Google Sheets: Count Cells With a Specific Dropdown ValueTo count cells that contain a particular Google Sheets dropdown choice, use COUNTIF. For example, if dropdowns are…
- How to Double Space in Word: A Step-by-Step GuideTo double-space text in Microsoft Word, select the text, then choose Home > Line and Paragraph Spacing >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.