It's 3:12 PM. You're staring at cell D7 in Dashboard.xlsm, where last week’s sales forecast is still hardcoded from a CSV you downloaded on Tuesday. Your colleague just Slack’d you a link to the live inventory API endpoint — https://api.supplychain.acme/v2/stock?region=APAC. You open Excel. You type "data connection" into Help. Nothing useful comes up.
The Setup
You’re supporting the APAC procurement team. Their weekly report pulls stock levels for 9 SKUs across 4 warehouses — but it’s manually updated every Friday from a shared Postman collection. You’ve been asked to automate it. Here’s the raw API response (simulated as a table you’d see in Power Query preview):
| sku_id | warehouse | in_stock | reorder_level | last_updated |
|---|---|---|---|---|
| SKU-7721 | Shanghai DC | 142 | 85 | 2024-03-15 |
| SKU-8849 | Tokyo Hub | 0 | 120 | 2024-03-15 |
| SKU-9103 | Singapore Fulfillment | 67 | 50 | 2024-03-15 |
| SKU-7721 | Tokyo Hub | 203 | 85 | 2024-03-15 |
| SKU-8849 | Shanghai DC | 31 | 120 | 2024-03-15 |
| SKU-9103 | Tokyo Hub | 19 | 50 | 2024-03-15 |
| SKU-7721 | Singapore Fulfillment | 0 | 85 | 2024-03-15 |
| SKU-8849 | Singapore Fulfillment | 112 | 120 | 2024-03-15 |
| SKU-9103 | Shanghai DC | 88 | 50 | 2024-03-15 |
The Challenge
You need this data refreshed daily — not copied and pasted. But here’s what trips people up:
- They assume all APIs require VBA or Power Query Advanced Editor. Not true. Excel’s native Web Data Connection handles most REST GET endpoints — no code.
- They paste the full URL into Data > From Web, forgetting that some APIs need headers (like
Authorization: Bearer xyz) — and Excel won’t prompt you. - They refresh and get “Data source error” — then blame the API. Usually, it’s because Excel caches the first response and ignores subsequent parameter changes (like changing
?region=EMEAlater).
Also: if your IT policy blocks external connections, this won’t work — and you’ll get no warning until refresh time.
Walking Through It
Open a blank workbook. Go to Data tab > Get Data > From Other Sources > From Web. Type the full URL: https://api.supplychain.acme/v2/stock?region=APAC.
Click OK. A Navigator window opens. If the API returns JSON, Excel usually auto-detects the table. Select the top-level table (often named Table or data). Click Load.
That loads raw data starting at cell A1. But notice: the last_updated column shows as text, not date. And in_stock has leading/trailing spaces in some rows (API inconsistency). So we clean it.
Select column E (last_updated). Press Ctrl+1, choose Date > 14/03/2024, click OK. Then select column C and D — right-click > Format Cells > Number > 0 decimal places.
Now fix the whitespace. In cell C2, enter: =TRIM(C2). Drag down to C10. Do same for D2:D10. Then copy → Paste Values over C2:D10. Done.
Before cleaning (A1:E10):
| sku_id | warehouse | in_stock | reorder_level | last_updated |
|---|---|---|---|---|
| SKU-7721 | Shanghai DC | 142 | 85 | 2024-03-15 |
| SKU-8849 | Tokyo Hub | 0 | 120 | 2024-03-15 |
After cleaning (A1:E10):
| sku_id | warehouse | in_stock | reorder_level | last_updated |
|---|---|---|---|---|
| SKU-7721 | Shanghai DC | 142 | 85 | 15-Mar-2024 |
| SKU-8849 | Tokyo Hub | 0 | 120 | 15-Mar-2024 |
Surprising tip: To force a full reload (bypass cache), press Alt+F5 — not F5. That triggers “Refresh All” *with cache override*. Works even if you have multiple queries.
The Result
Here’s the final cleaned, auto-refreshing dataset — ready to feed into your dashboard formulas (e.g., =SUMIFS(C2:C10,A2:A10,"SKU-7721") in cell G2). Refreshes on open, or manually via Alt+F5:
| sku_id | warehouse | in_stock | reorder_level | last_updated |
|---|---|---|---|---|
| SKU-7721 | Shanghai DC | 142 | 85 | 15-Mar-2024 |
| SKU-8849 | Tokyo Hub | 0 | 120 | 15-Mar-2024 |
| SKU-9103 | Singapore Fulfillment | 67 | 50 | 15-Mar-2024 |
| SKU-7721 | Tokyo Hub | 203 | 85 | 15-Mar-2024 |
| SKU-8849 | Shanghai DC | 31 | 120 | 15-Mar-2024 |
| SKU-9103 | Tokyo Hub | 19 | 50 | 15-Mar-2024 |
| SKU-7721 | Singapore Fulfillment | 0 | 85 | 15-Mar-2024 |
| SKU-8849 | Singapore Fulfillment | 112 | 120 | 15-Mar-2024 |
| SKU-9103 | Shanghai DC | 88 | 50 | 15-Mar-2024 |
What Could Go Wrong
Here are three things I saw break it last week — all in production files:
- API returns HTML instead of JSON: Some endpoints (especially internal ones) serve login pages when auth fails. Excel shows “The specified file could not be found.” Solution: Test the URL in Edge first — if you see a login screen, you need headers or cookies. Excel doesn’t support either natively.
- Column name changes silently: The API added
warehouse_codelast Tuesday. Excel kept loading old schema — so new column appeared asColumn1in row 1, breaking allVLOOKUPs. Fix: Right-click query > Properties > uncheck Enable load, then edit > Advanced Editor > manually update column names. - Auto-refresh fails on startup: User double-clicked the file — Excel opened in Protected View. Data connections are blocked by default. They saw blank cells and assumed the API was down. Fix: File > Options > Trust Center > Trust Center Settings > External Content > Enable all data connections (only if your org allows it).
Next step: Open your target workbook. Go to Data > Queries & Connections (Alt+A+Q). Right-click your query > Edit. In the formula bar, change the URL’s region=APAC to region=EMEA. Press Enter. Then hit Alt+F5.
