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 Microsoft Word! This wikiHow will show you import data from a remote URL into Excel in Windows and macOS.

Method 1
Method 1 of 2:

Windows

  1. 1
    Click an empty cell in the spreadsheet. Choose the cell where you'd like the pasted data to begin.
  2. 2
    Click the Data tab. This tab is located between Formulas and Review at the top of the app window.
    Advertisement
  3. 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] 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.
  4. 4
    Enter the website URL and click OK. The address you enter or paste into the typing area should be the URL that contains the data you want to import.
  5. 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] 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. 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.
  7. 7
    Click the Load button. This will import the selected table data into your Excel spreadsheet.
  8. Advertisement
Method 2
Method 2 of 2:

Mac

  1. 1
    Open Microsoft Word to a blank 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 Microsoft Word first.[3]
  2. 2
    Type or paste the URL you want to import from. It's important that the URL be in plain text. If you copied the URL from somewhere else, click the small icon that appears after pasting and select Keep Text Only.
  3. 3
    Click the File menu and choose Save as. Now you'll need to save the file in the TXT format in a particular location.
  4. 4
    Select the Queries folder. Before you save the file, you'll need to navigate to Applications > Microsoft Office 365 > Office > Queries, as that's where you'll need to save the file.
    • If you're using a different version of Office, you'll see your version instead.
  5. 5
    Save the file as a plain text file. Enter a name for your file and choose the .txt file extension. You'll see a warning message in a window called "File Conversion."
  6. 6
    Select your preferences and click OK. These two options are very important:
    • Under "Text encoding," select MS-DOS.
    • Under "Options," check the box next to "Insert line breaks." The other options should be blank.
  7. 7
    Open Finder and navigate to the Queries folder. Again, it's at Applications > Microsoft Office 365 > Office > Queries.
  8. 8
    Change the file extension from .txt to .iqy.
    • Right-click the file you created and select Get Info.
    • In the file name, replace .txt with .iqy.
    • When prompted, confirm that you want to change the file extension.
  9. 9
    Open your spreadsheet in Excel. Now that you have a query to work with, you can import it into Excel.
  10. 10
    Click the Data menu. It's in the menu bar at the top of the screen.
  11. 11
    Select the Get external data menu. Additional options will expand.
  12. 12
    Click Run web query. This will open a Finder window to the Queries folder, which is where you saved your query.
  13. 13
    Select your IQY file and click Get Data. This will import the data into your Excel file.
  14. Advertisement

About This Article

Nicole Levine, MFA
Written by:
wikiHow Technology Writer
This article was co-authored by wikiHow staff writer, Nicole Levine, MFA. Nicole Levine is a Technology Writer and Editor for wikiHow. She has more than 20 years of experience creating technical documentation and leading support teams at major web hosting and software companies. Nicole also holds an MFA in Creative Writing from Portland State University and teaches composition, fiction-writing, and zine-making at various institutions. This article has been viewed 68,817 times.
How helpful is this?
Co-authors: 3
Updated: August 11, 2022
Views: 68,817
Categories: Microsoft Excel | Databases
Article SummaryX

You can easily import a data table from the web into your Excel file using Excel's "From Web" import tool. To do this, open your Excel file, and then click the cell where you'd like the imported data to appear. On the Data tab, click the From Web button on the left side of the toolbar to open the "From Web" window. Type or enter the URL that contains your data and click OK. If the data is on a public website, click Connect to open it. If it requires a login, choose the option in the left panel that describes how you typically sign in, enter your login details, and click Connect. If you know the name of the table you want to import, click it in the left panel. If you'd rather select it from the website, click the Web View tab above the right panel to load the site, and then click the desired table. You can import more than one table at once by checking the "Select multiple items" box and clicking additional tables. When you're finished, click Load to import the selected data to your spreadsheet.

Did this summary help you?
Advertisement