2 Ways to Import Data from the Web into Excel: Windows & Mac

Download Article

Easily import data from a website into your Excel workbook

Written by Megaera Lorenz, PhD

Last Updated: September 12, 2024 Fact Checked

Download Article

Windows

|

Mac

|

Video

If you need to import data from the web into your Microsoft Excel spreadsheet, you can use the import feature of Power Query on both Windows and macOS. While the steps are straightforward in Windows, Mac users technically don't have the option to import web data. Fortunately, we've found a simple workaround for Mac using Apple’s native TextEdit app! This wikiHow will show you import data from a remote URL into Excel in Windows and macOS.

How to Import Data from a Website into Excel

On Windows, go to Data > From Web, enter the URL, and click Connect to select and import web data.
This feature doesn’t work on Mac, but you can still do it with a workaround. Create a text file in TextEdit with a .iqy suffix that contains the URL, then use "Run Web Query" to import the data.

Method 1

Method 1 of 2:

Windows

Download Article

Choose the cell where you'd like the pasted data to begin.
This tab is located between Formulas and Review at the top of the app window.

Advertisement
3
Click From Web on the Data ribbon. You will see this in the upper-left corner of Excel on the "Get & Transform Data" panel. ^{[1]
X
Trustworthy Source
Microsoft Support
Technical support and product information from Microsoft.
Go to source} This option is in the upper-left corner of your Data ribbon. This opens the "From Web" window.
- If you don't see this option, click the Get Data drop-down menu at the top-left, select From Other Sources , and then select From Web .
The address you enter or paste into the typing area should be the URL that contains the data you want to import.
5
Click Connect to access the site anonymously. If the data is on a public website, this will display the tables in Table View. ^{[2]
X
Trustworthy Source
Microsoft Support
Technical support and product information from Microsoft.
Go to source} If the site requires a login:
- If you usually sign in to this page by entering a username and password into the site's own login/sign-in fields, select Basic , enter your login details, and click Connect .
- If you need to log in through a work or school account, select Windows to use a Windows network login, or select Organizational account and then Sign In to log in through your organization's server.
- If you have an API key, select Web API , enter the key, and click Connect .
6
Select a table. The list of tables appears in the left panel of the Navigator window. When you select a table, the data will appear in the panel on the right.
- If you're not sure which table you need, click the Web View tab at the top of the right panel to display the website, and then click the table to select it.
- To import more than one table, check the box next to "Select multiple items" near the top-left.
This will import the selected table data into your Excel spreadsheet.

Method 2

Method 2 of 2:

Mac

Download Article

1
Open TextEdit and create a new document. Because Excel for macOS, even the one that comes with Office 365, doesn't have an option to import data from the web, you'll need to do a little workaround that involves creating a query in a text editor first. ^{[3]
X
Research source} We got the best results using Mac’s built-in TextEdit app.
- To find TextEdit, click the
  
  Spotlight search button at the top of the screen and type in textedit . Click the first result that comes up.
- While this method may seem a little daunting, it’s not as complicated as it looks at first glance and only requires a few extra steps. However, it’s very important to follow these instructions exactly , or it won’t work.
When you open TextEdit, a window will open where you can browse files to open. Click the New Document button at the bottom of the window, or close the window and select New from the File menu.
3
Type or paste the URL you want to import from. It's important that the URL be in plain text. Type it in directly or copy and paste it from the navigation bar at the top of your browser.
- The copied URL should be the only text in the file you're creating.
Open the Format menu in the bar at the top of the screen and choose the Make Plain Text option. This will allow you to save the file in the correct format.
Now that you’ve set the file to Plain Text, it will save to the correct file format (in this case, the Unicode (UTF-8) plain text format). Click Save in the File menu.
Even though you're saving the file as a Unicode plain text file, you'll need to make sure the file has the .iqy file extension. For example, my_query.iqy . Enter your desired name with the .iqy ending in the Save As: text box at the top of the Save pop-up window.
You’ll see a check box in the Save window that says If no extension is provided, use “.txt”. If that box is checked, uncheck it before you save. Your file will now be saved with a .iqy extension.
It doesn’t really matter where you save your file. Just pick a location you’ll remember, like Desktop or your Documents folder.
Now that you have a query to work with, you can import it into Excel. Create a new spreadsheet or open a pre-existing one where you’d like to import your data.
Note that there are two Data menus while Excel is open—one in your Mac's menu bar at the top of the screen, and another in the menu bar at the top of the Excel spreadsheet. These two menus are different. You will need to click the Data menu in the menu bar at the top of your Mac's screen, not the one at the top of the spreadsheet.
Additional options will expand.
This will open a Finder window.
Navigate to the location where you saved your text file and select it, then click Get Data . You'll be prompted to choose where to import the data.
You can import the data into the current sheet at a particular cell location, or create a new sheet for the data. Once you click Import , Excel will go to the URL you added to your IQY file and import the web data into your sheet.