10 functions

Cube & Web Functions

Two specialized function families in one category — Cube functions for Power Pivot and SSAS analytics, and Web functions for calling REST APIs directly from cells.

💡 Two very different toolsets

Cube functions read data from OLAP cubes — Power Pivot data models, SQL Server Analysis Services, and Azure Analysis Services. They're the "escape hatch" from Pivot Tables when you need pixel-perfect layouts backed by a full data model.

Web functions call web APIs and parse XML responses directly from cells. WEBSERVICE fetches a URL, FILTERXML extracts values from the response. Great for prototypes and simple integrations, though Power Query is better for production workflows.

The essential four

CUBEVALUE

=CUBEVALUE("ModelName", member1, member2)

Return a single aggregated value from a cube. The workhorse of Power Pivot reporting. Build report layouts row by row, cell by cell, with precise control.

CUBEVALUE Complete Guide →

CUBEMEMBER

=CUBEMEMBER("ModelName", "[Products].[Widget]")

Reference a specific member of a cube dimension. Use CUBEMEMBER values as arguments to CUBEVALUE. Basis of every cube-driven report.

CUBEMEMBER Complete Guide →

WEBSERVICE

=WEBSERVICE("https://api.example.com/data")

Fetch data from a URL and return the response as text. Simple REST API calls straight from a cell. Windows-only, and Microsoft has been deprecating it in favor of Power Query.

WEBSERVICE Complete Guide →

FILTERXML

=FILTERXML(response, "//element[@name='x']")

Parse XML responses using XPath queries. Extract specific values from the mass of XML that WEBSERVICE returns. Together, they turn Excel into a mini-API-client.

FILTERXML Complete Guide →

Browse by sub-group

Cube Values & Members

3 functions

Get aggregated values and dimension members from OLAP cubes.

CUBEVALUE CUBEMEMBER CUBEMEMBERPROPERTY

Cube Sets & Ranking

3 functions

Build sets of members, count them, and extract ranked members.

CUBESET CUBESETCOUNT CUBERANKEDMEMBER

Cube KPIs

1 function

Extract KPI values, targets, statuses, and trends defined in the cube's KPI model.

CUBEKPIMEMBER

Web & XML

3 functions

Fetch URLs, parse XML responses, and safely encode query parameters.

WEBSERVICE FILTERXML ENCODEURL

⚠️ Platform notice: WEBSERVICE and FILTERXML are Windows-only and not supported in Excel for Mac, Excel Online, or Excel on iPad. For cross-platform API integration, use Power Query or the PY function instead.

Real-world patterns

  • Financial dashboard from Power Pivot: =CUBEVALUE("ThisWorkbookDataModel", "[Measures].[TotalRevenue]", "[Date].[2026]")
  • Fetch USD/EUR exchange rate: =FILTERXML(WEBSERVICE("api-url"), "//rate")
  • Weather in a cell: combine WEBSERVICE with a weather API and FILTERXML to extract temperature
  • Ranked list of top products: use CUBESET + CUBERANKEDMEMBER for top-N reports
  • URL-safe query building: =ENCODEURL(A1) before appending user input to a URL

Frequently asked questions

Do I need Power Pivot to use CUBE functions?

Yes. CUBE functions require either the built-in Power Pivot data model in your workbook, or a connection to SQL Server Analysis Services / Azure Analysis Services. Without a cube source, they return errors.

Why does WEBSERVICE return #N/A?

Most common causes: (1) The URL requires HTTPS but you used HTTP or vice versa, (2) The service requires authentication headers that WEBSERVICE can't send, (3) The response is JSON not XML (WEBSERVICE + FILTERXML expect XML), (4) You're not on Windows Excel.

Can I call JSON APIs with WEBSERVICE?

Not directly — FILTERXML only parses XML. For JSON APIs, use Power Query (Data → Get Data → From Web) which handles JSON natively. Or write a LAMBDA that parses JSON strings manually.

Should I still learn CUBE functions?

Yes if you work with Power Pivot models. Pivot Tables are easier for exploration; CUBE functions give you precise report layout control that Pivot Tables can't match. Together they're both useful.

Is WEBSERVICE being deprecated?

Effectively yes. Microsoft has stopped adding features and it doesn't work on Mac/Online. Power Query is the recommended replacement for all new API integration work. WEBSERVICE still functions on Windows Excel for backward compatibility.

Live data in Excel — the modern way.

Ask the Add-in "help me connect this API to Excel" — get a Power Query setup that works on every platform, not just Windows.

Get the Add-in →