A 2024 workplace survey of 1,247 finance and ops professionals found that 81% tried — and abandoned — pulling live API data into Excel because they assumed it required coding or add-ins.
The Problem
Every Monday at 9 a.m., Sarah Chen in Procurement copies JSON output from the supplier status dashboard, pastes it into Notepad, restructures it in WordPad to remove curly braces, then manually enters 17 fields across 3 sheets. Last week, she missed two late shipments because the timestamp field was misaligned in column D instead of E — and no one caught it until invoice reconciliation.
| Supplier | Status | Last Updated | Lead Time (days) | Notes |
|---|---|---|---|---|
| Acme Corp | Shipped | 2024-03-15T14:22:01Z | 4 | ETA delayed by customs |
| Veridian Logistics | Pending | 2024-03-16T08:03:17Z | 12 | PO# VRD-8821 |
| Nexus Parts Ltd | On Hold | 2024-03-14T22:51:44Z | — | Awaiting quality sign-off |
| Stellar Components | Shipped | 2024-03-16T11:19:05Z | 2 | Air freight confirmed |
| Orion Fabrics | Cancelled | 2024-03-15T19:07:33Z | — | Client requested change |
This isn’t bad data — it’s unstructured, time-sensitive, and lives behind an API endpoint like https://api.supplychain.example.com/v2/shipments?status=active. And yes, Excel can fetch it natively. No VBA. No third-party tools.
The Solution
Excel’s Get Data → From Web works with many REST APIs — as long as they return HTML, JSON, or XML, and don’t require OAuth 2.0 or custom headers. Here’s what actually works:
- Go to Data tab → Get Data → From Web (Alt + A → W → W)
- Paste your API URL — e.g.,
https://jsonplaceholder.typicode.com/posts?_limit=5. Don’t include authentication tokens in the URL if possible. - Click OK. If the response is valid JSON, Excel shows a Navigator window listing tables. Select the top-level array (often labeled “Table” or “List”).
- Click Load. Excel drops clean, refreshable data starting at cell A1.
Try it now with this real working test endpoint. It returns 5 blog post objects — each with id, title, body, and userId. You’ll get five rows. Column headers auto-populate.
| id | userId | title | body |
|---|---|---|---|
| 1 | 1 | sunt aut facere repellat provident occaecati excepturi optio reprehenderit | quia et suscipit suscipit recusandae consequuntur expedita et cum reprehenderit molestiae ut ut quas totam nostrum rerum est autem sunt rem eveniet architecto |
| 2 | 1 | qui est esse | est rerum tempore vitae sequi sint nihil reprehenderit dolor beatae ea dolores neque fugiat blanditiis voluptate porro vel nihil molestiae ut reiciendis qui a delectus |
| 3 | 1 | ea molestias quasi exercitationem repellat qui ipsa sit aut | et iusto sed quo iure voluptatem occaecati omnis eligendi aut ad voluptatem doloribus vel accusantium quis pariatur molestiae porro eius odio et labore et velit aut |
| 4 | 2 | eum et est occaecati | ullam et saepe reiciendis voluptatem adipisci sit amet autem assumenda provident rerum culpa quis hic commodi nesciunt rem tenetur doloremque ipsam iure quis sunt voluptatem rerum illo velit |
| 5 | 2 | nesciunt quas odio | repudiandae veniam quaerat sunt sed alias aut fugiat sit autem sed est voluptatem omnis possimus esse voluptatibus quis est aut tenetur dolor quam |
That’s it. Refresh anytime with Data → Refresh All (Alt + F5) or right-click → Refresh. The query lives in the Queries & Connections pane (Ctrl + Alt + Q).
Counterintuitive tip: If your API returns plain JSON (no HTML wrapper), try adding ?format=json to the end of the URL — some endpoints silently serve HTML unless told otherwise. Also: never paste credentials into the URL bar. Use Power Query Advanced Editor only if you need headers or auth.
Going Further
You can extend this without writing code:
- Add parameters dynamically: Link a cell (say, D1) to your URL using
=CONCATENATE("https://api.example.com/data?date=",TEXT(D1,"yyyy-mm-dd")), then reference that cell in Power Query’s Source step. - Combine multiple APIs: Load two separate queries (e.g., orders + inventory), then merge them in Power Query using Home → Merge Queries → Merge as New.
- Filter before loading: In Power Query Editor, filter columns or rows *before* clicking Close & Load — reduces memory use and speeds up refresh.
- Auto-refresh on open: Right-click the query name → Properties → check “Refresh data when opening file”.
If your API needs Bearer tokens, skip the web connector entirely. Use Power Query’s Web.Contents() function with headers — but that’s a separate workflow. Stick with From Web for public, token-free endpoints.
When NOT to Use This
This method fails — silently or noisily — in four specific cases:
- CORS or login walls: If the API responds with “401 Unauthorized” or redirects to a login page, Excel’s From Web won’t help. You’ll see “The webpage cannot be displayed” or blank results.
- Rate-limited endpoints: Some APIs throttle requests per IP. If you refresh every minute, you’ll hit limits — and Excel won’t warn you. Check your API docs for
X-RateLimit-Remainingheaders. - Large payloads (>10 MB): Excel may freeze or crash. Test with
?_limit=10first. For >50k rows, use Power BI or export to CSV via script. - Dynamic auth (OAuth 2.0, SAML): Excel doesn’t handle redirect flows. You’ll need Postman + manual export or a dedicated connector like Office Add-ins.
If your procurement team uses SAP Ariba or Coupa, their APIs require OAuth 2.0. Don’t waste time pasting those URLs into From Web — it’ll just spin forever.
Keyboard Shortcuts
| Action | Shortcut | Notes |
|---|---|---|
| Open From Web dialog | Alt + A → W → W |
Fastest way — no mouse needed |
| Refresh all queries | Alt + F5 |
Works even if sheet isn’t active |
| Open Queries & Connections pane | Ctrl + Alt + Q |
Manage, rename, or delete queries |
| Edit current query in Power Query | Alt + F3 |
Only works if cell is inside loaded query table |
