Excel is the most widely deployed API client that nobody calls one. Get Data → From Web plus Power Query turns any REST endpoint into a refreshable table — no VBA, no add-in — and a huge amount of real reporting runs on exactly that. The sharp edges are the same ones every API client meets: pagination, sources that fail or crawl, and the eternal "what did Excel actually send?" — except here they surface in a finance workbook at month-end instead of a test suite.
The usual practice targets can't rehearse any of that: a real API rations you, a saved JSON file never pages, never fails, never changes. This guide points Excel at a hosted, stateful mock instead — real endpoints, server-side filters, and failure modes you switch on from a URL param. No signup; the M engine is the same one Power BI uses, so everything transfers. Every curl below was verified against production before publishing.
The public demo project is a seeded e-commerce API. In Excel for Windows: Data → Get & Transform Data → From Web, paste:
https://mockbird.mockbird.workers.dev/m/demo/products?limit=20Choose Anonymous in the Access Web content dialog and connect. Power Query sees JSON and opens the editor holding a list of 20 records: To Table → expand the record column → id, name, price, category, inStock, rating land as real columns → Close & Load puts a live-refreshable table on a sheet. That's the whole loop most tutorials stop at — the rest of this page is the part they skip.
JSON import always costs you the To Table → expand ceremony. Every Mockbird endpoint also serves RFC-4180 CSV — add ?mock_format=csv and pick your columns server-side with select:
curl 'https://mockbird.mockbird.workers.dev/m/demo/products?mock_format=csv&select=name,price,category,inStock&limit=3'
# id,name,price,category,inStock
# 1,Bridge Area Night,358.76,clothing,true
# 2,World Thing System Point,153.79,toys,false
# 3,Field Thing Money,99.19,beauty,falsePaste that URL into From Web and Power Query treats it as a CSV source: header row detected, numbers typed as numbers, one step to a clean table. Sorting stays server-side too — &sortBy=price&order=desc returns rows already ordered (verified: 926.11, 913.03, 792.85…). For teaching, quick lookups, or feeding a chart, this is the two-minute path; it's the same trick the Google Sheets guide builds on with IMPORTDATA.
Refresh is where Excel-as-API-client becomes an ops question: Ctrl+Alt+F5 (Refresh All) refreshes every connection now, but the interesting settings live in Data → Queries & Connections → Connections tab → right-click the query → Properties → Usage: Refresh every N minutes and Refresh data when opening the file.
Against a mock you can see each tick land. Set Refresh every 1 minute, then open the public demo request inspector: every refresh appears with its method, path, and query string. It answers questions that are unfalsifiable from inside Excel — did refresh-on-open actually fire? does one Refresh All hit the endpoint once per query or once per visual reference? is the workbook someone mailed to the whole team quietly polling every minute from ten laptops? On your own project (§7) the inspector is private and the shared drill still works.
List endpoints take _page/_limit and return the collection total in an x-total-count header (verified: page 2 at limit 5 returns ids 6–10, x-total-count: 30). But response headers are awkward for M outside custom connectors — so put the total in the body with mock_envelope, and let Web.Contents' Query record do the URL-escaping:
let
PageSize = 10,
Envelope = "{""data"":""$data"",""page"":""$page"",""total"":""$total"",""hasMore"":""$hasMore""}",
GetPage = (p as number) =>
Json.Document(
Web.Contents(
"https://mockbird.mockbird.workers.dev",
[
RelativePath = "m/demo/products",
Query = [
_page = Text.From(p),
_limit = Text.From(PageSize),
mock_envelope = Envelope
]
]
)
),
PageCount = Number.RoundUp(GetPage(1)[total] / PageSize),
AllRows = List.Combine(List.Transform({1 .. PageCount}, each GetPage(_)[data])),
ToTable = Table.FromList(AllRows, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Expanded = Table.ExpandRecordColumn(
ToTable, "Column1", {"id", "name", "price", "category"})
in
ExpandedPaste into a Blank Query's Advanced Editor: 30 rows from three requests, each visible in the inspector. The envelope was verified server-side — page 1 returns {"data":[…10 records…],"page":1,"total":30,"hasMore":true}, page 3 flips hasMore to false. The static-base-URL + RelativePath/Query shape also keeps Data source settings to a single credential entry — and if the workbook ever graduates to Power BI's service, it's already in the only shape scheduled refresh accepts (the Power BI guide drills that, plus the retry engine, in depth).
Filtering after Json.Document means downloading everything and filtering on your laptop. With an API that filters server-side, the Query record is where the work should go:
Query = [category = "electronics"] // exact match on any field
Query = [price_gte = "500"] // _gte/_lte/_gt/_lt/_ne/_like
Query = [q = "laptop"] // free-text search across fields
Query = [sortBy = "price", order = "desc"] // server-side sortVerified above: category=electronics returns only the matching records; price_gte=500 returns 12 of 30. Rehearsing the where-does-the-filter-run question here builds the instinct that keeps a real refresh from pulling a warehouse through your VPN.
By default a non-200 fails the refresh with a yellow banner. ManualStatusHandling turns chosen codes into data you can branch on, and mock_status produces any code on demand (verified: 404 and 500 on the single-record endpoint):
let
Response = Web.Contents(
"https://mockbird.mockbird.workers.dev",
[
RelativePath = "m/demo/products/1",
Query = [mock_status = "404"],
ManualStatusHandling = {404, 500, 503}
]
),
Status = Value.Metadata(Response)[Response.Status],
Result = if Status = 404 then null else Json.Document(Response)
in
ResultWalk mock_status through your whole matrix — 404, 500, 503 — and check each branch. Try 401 once too: that one can't be intercepted outside a custom connector, so what you get is Excel's credentials prompt — worth having seen on purpose before a real API's token expires mid-month-end. For slowness, ?mock_delay=3000 (up to 5,000 ms; verified 3.76s measured) makes a refresh observably slow — useful for watching the status-bar background refresh, testing Esc to cancel, and checking a Timeout = #duration(0,0,0,30) override actually applies against the 100-second default.
| Excel | What works |
|---|---|
| Windows | Everything on this page: From Web, Blank Query M, connection properties, timed refresh. |
| Mac | Microsoft's current source list for Get Data on Mac (Excel Workbook, Text/CSV, XML, JSON, SharePoint, OData, Blank Table, Blank Query) has no From Web entry, and Web sources aren't in its supported-refresh list either. The community workaround is a Blank Query with the same Web.Contents M as §4 — try it on your build; if it balks, download the CSV (curl -o data.csv '…?mock_format=csv') and use Text/CSV, or use Google Sheets' IMPORTDATA. |
| Excel for the web | Viewing and refreshing existing queries is available to all Microsoft 365 subscribers — build the workbook on desktop, share it, and browser users hit Data → Refresh All, choosing anonymous when asked for the auth method. Authoring new Power Query queries in the browser needs a Business/Enterprise plan. |
When a real API rejects a request, the argument starts: header, encoding, caching? End it with a bin — the demo's catch-all route echoes whatever arrives, and logs it:
curl -s -X POST https://mockbird.mockbird.workers.dev/m/demo/hooks/excel-refresh \
-H 'content-type: application/json' -d '{"workbook":"sales.xlsx","source":"powerquery"}'
# → {"ok":true,"caught":"POST /hooks/excel-refresh","body":{…},"receivedAt":"…"}Point any query at a bin URL and the inspector shows method, path, headers, and body — including whether Power Query made a request at all (it caches aggressively within an evaluation; an absent request is an answer too). On your own project, one ANY /* custom route makes a private bin.
The data flow also runs the other way — and it's one curl. Export any Excel table as CSV, import it, and it becomes a typed REST API the rest of the team (or a script, or Power BI, or another workbook) can pull From Web:
curl -s -X POST 'https://mockbird.mockbird.workers.dev/api/projects/import?resource=pipeline' \
-H 'content-type: text/csv' --data-binary @pipeline.csv
# → {"id":"abc123xyz9", "adminKey":"…", ...}
# columns come back typed — numbers filter as numbers, booleans as booleans:
curl 'https://mockbird.mockbird.workers.dev/m/abc123xyz9/pipeline?revenue_gte=40000&sortBy=revenue&order=desc'
curl 'https://mockbird.mockbird.workers.dev/m/abc123xyz9/pipeline?closed=true'
# …and straight back out as CSV for anyone still in a spreadsheet:
curl 'https://mockbird.mockbird.workers.dev/m/abc123xyz9/pipeline?mock_format=csv'Verified end-to-end before publishing: a 3-row sales CSV came back with revenue as a float and closed as a boolean, range-filtered and sorted server-side. That's a static workbook turned into a filterable, always-up HTTP source — the thing people reach for SharePoint links and shared drives to approximate. Prefer a seeded practice API instead? One curl (or one click): curl -X POST …/api/projects -d '{"preset":"ecommerce"}' — presets blog/ecommerce/saas, or import an OpenAPI spec or db.json. Projects have a 10,000 requests/day cap; even a 1-minute timed refresh uses ~1,440.
Fair's fair: if the data is a file, open the file; if it lives in SQL Server or Power BI datasets, the native connectors with real query folding beat anything From Web can do. And heavy automation eventually belongs in Power BI or a script, not a workbook. Reach for a hosted mock when the source is a REST API — then pagination, refresh behavior, error branches, and the what-did-it-send question are the work, and they deserve a practice target that misbehaves on demand instead of a production API that misbehaves on its schedule.
Related: the Power BI / Power Query deep dive (retry engine, scheduled refresh, ManualStatusHandling), the Google Sheets version (IMPORTDATA + CSV), CSV → REST API, the Power Automate version, cursor pagination, and testing loading and error states.
Verification: all demo curls on this page (limit=20 list returning 20 records with fields id/name/price/category/inStock/rating, the mock_format=csv&select=… output shown verbatim incl. server-side sortBy=price&order=desc ordering, _page=2&_limit=5 returning ids 6–10 with x-total-count: 30, the envelope template returning {"data","page","total","hasMore"} with hasMore:false on page 3, mock_status 404 and 500, a 3.76s measured mock_delay=3000, category and price_gte=500 (12 of 30) server-side filters, the bin echo with inspector log, and the CSV import round trip — typed float/boolean columns, revenue_gte + closed=true filters, CSV export back out, scratch project deleted) were run against production on 20 Sep 2026 before publishing. Excel behavior — the From Web flow and Anonymous access dialog, Ctrl+Alt+F5 / Ctrl+F5 / Esc, Connection Properties → Usage's Refresh every N minutes and Refresh data when opening the file, the Mac Get Data source list without From Web and its supported-refresh list, and Excel for the web's view-and-refresh availability for all Microsoft 365 subscribers with anonymous-auth selection on refresh — is from Microsoft Support documentation as of September 2026; Power Query M semantics (ManualStatusHandling, the 100-second default Timeout, RelativePath/Query) are from Microsoft Learn's Power Query documentation. We don't run your copy of Excel; if a step here doesn't work, that's a bug: tell us.