WEBSERVICE Function

Fetches data from a web address straight into a cell.

Category

Web

Available in

Excel 2013+ for Windows, Microsoft 365 (not Mac or the web)

Returns

The text the web service sends back

Overview

WEBSERVICE sends a request to a URL and returns whatever text comes back — often XML or JSON from an online service, such as exchange rates or weather.

Combine it with FILTERXML to pick out the value you need, and with ENCODEURL to insert cell values into the address safely.

Syntax

=WEBSERVICE(url) Explain

Arguments

ArgumentRequired?What it does
urlYesThe full web address (http or https) to request, up to 2,048 characters.

Examples

Example 1 — Fetch a feed

Returns the raw text the service sends back.

=WEBSERVICE("https://example.com/api/rates?base=USD&format=xml") Explain

Example 2 — Fetch and read one value

Ask for XML, then use FILTERXML to pull out the value of the rate element.

=FILTERXML(WEBSERVICE("https://example.com/api/rates?base=USD&symbol="&ENCODEURL(A2)&"&format=xml"),"//rate") Explain

Common mistakes

  • #VALUE! The address is invalid or unreachable, the reply is longer than 32,767 characters, or the URL is over 2,048 characters.
  • It needs a sign-in. WEBSERVICE can't log in or send custom headers. For services that need keys in headers, use Power Query (Data › Get Data › From Web).

Notes

  • Results refresh when the workbook recalculates, so very frequent calls can hit a service's rate limit.
  • Because it needs a live web request, WEBSERVICE can't run in our practice Sandbox.
  • Related functions: FILTERXML, ENCODEURL, STOCKHISTORY.
Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.