Create and Troubleshoot an Excel for Mac 2016 Web Query
Question details
Create a selectable web query file and import data from a website into Excel for Mac 2016.

- Product
- Microsoft Excel for Mac 2016
- Device & OS
- macOS
- Scenario
- A user wants to run a web query using a text file that contains a website address.
- Observed behavior
- A query saved with a .txt extension may be greyed out in Excel's query dialog. After the file is selected, some websites may still fail to load.
Identify the URL you want to query and check that it opens in a browser. Have a plain-text editor available to create the query file.
Create an MS-DOS-encoded IQY file
Use this method when a .txt query file is greyed out or cannot be selected in Excel.
Open TextEdit and choose Format > Make Plain Text. Enter WEB on the first line, 1 on the second line, and the full website URL on the third line.
Save the file to your Desktop as query.txt using UTF-8 encoding. Open Terminal and run: iconv -f UTF-8 -t CP437 ~/Desktop/query.txt > ~/Desktop/query.iqy. This creates an MS-DOS-encoded .iqy file.
In Excel, open the Data tab and choose Get External Data > Run Web Query. Select query.iqy on your Desktop and follow the import prompts.

Try another source when a website cannot be reached
Use this approach if Excel accepts the .iqy file but reports that it cannot locate the Internet server or proxy server.
Work with spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files and continue working with your data without a complicated migration. It does not fix Excel for Mac's web query errors.

Frequently Asked Questions
Why is my web query file greyed out in Excel for Mac?
The query dialog expects an .iqy file. If your file ends in .txt, save it with MS-DOS encoding, then use Finder > File > Get Info to change the extension to .iqy.
What should an IQY file contain?
For a basic web query, use plain text with WEB on the first line, 1 on the second line, and the full target URL on the third line. Save it with the .iqy extension.
Why does Excel say it cannot locate the Internet server?
The address, network, or proxy settings may be preventing access, or the website may block Excel's request. Open the URL in a browser, check your connection, and test another available data source.
Will any finance website work with an Excel web query?
No. A website must allow Excel to access its content and provide data in a retrievable form. You can test another source, such as Yahoo Finance, but availability and compatibility may change.




