Excel is for analyzing data. Access is for enforcing data integrity. But if you try to force Excel to behave like a database, your reports will silently lie.
The Setup
You’re managing vendor contracts for Alibaba’s internal procurement team. Eight vendors. Each has multiple active contracts, renewal dates, payment terms, and assigned managers. You get a raw dump from SAP — 37 rows, inconsistent formatting, duplicates, and mismatched IDs.
| Vendor ID | Vendor Name | Contract # | Start Date | Value (USD) | Manager |
|---|---|---|---|---|---|
| V-721 | BrightLine Logistics | CL-8842 | 2023-06-12 | $142,500 | Sarah Chen |
| V-309 | Nexus Procure Ltd | CL-8843 | 2023-07-03 | $89,200 | Rajiv Mehta |
| V-721 | BrightLine Logistics | CL-9107 | 2024-01-15 | $168,000 | Sarah Chen |
| V-444 | TerraFibre Systems | CL-9108 | 2024-02-22 | $215,750 | Amina Diallo |
| V-309 | Nexus Procure Ltd | CL-9109 | 2024-03-10 | $76,300 | Rajiv Mehta |
| V-721 | BrightLine Logistics | CL-9221 | 2024-04-05 | $194,100 | Sarah Chen |
| V-555 | Orion Supply Group | CL-9222 | 2024-04-18 | $133,800 | James Wu |
| V-309 | Nexus Procure Ltd | CL-9330 | 2024-05-02 | $92,400 | Rajiv Mehta |
| V-444 | TerraFibre Systems | CL-9331 | 2024-05-14 | $201,600 | Amina Diallo |
| V-721 | BrightLine Logistics | CL-9440 | 2024-06-01 | $177,200 | Sarah Chen |
The Challenge
You need to answer three questions every month:
- Which vendors have >2 active contracts?
- What’s the total value of contracts per manager?
- If Sarah Chen leaves, which contracts vanish — and who owns them next?
Excel can do the first two — barely. The third? It can’t track ownership transitions or prevent duplicate contract numbers across vendors. You’ll manually update 37 rows. Then miss one. Then someone pays CL-9107 twice.
Access solves that — but only if you model it right. And most people don’t.
Walking Through It
Start in Excel. Paste the raw data into Sheet1, A1:F11.
Step 1: Find vendors with >2 contracts
Use =COUNTIFS(A:A,A2) in G2, drag down. Filter column G for values >2. Result: V-721 (4 contracts), V-309 (3 contracts). That’s 2 rows — fine.
| Vendor ID | Count |
|---|---|
| V-721 | 4 |
| V-309 | 3 |
Step 2: Total value per manager
In a new sheet, list unique managers in A2:A5. In B2, enter =SUMIFS(Sheet1!E:E,Sheet1!F:F,A2). Done. 4 rows.
Step 3: Ownership handover
This is where Excel fails. You’d need to replace every “Sarah Chen” with “James Wu” in column F. But what if CL-9221 was already reassigned last week — and you overwrote it? Excel won’t warn you. No audit trail. No enforced uniqueness on Contract #.
Now open Access. Import the same data as a table named tContracts. Create a second table tVendors with Vendor ID (primary key), Name, and Contact Email. Link them via Vendor ID.
Then build a query: SELECT tVendors.Name, COUNT(*) AS ContractCount FROM tContracts INNER JOIN tVendors ON tContracts.[Vendor ID] = tVendors.[Vendor ID] GROUP BY tVendors.Name HAVING COUNT(*) > 2;
That’s not a formula. It’s a rule. It runs fresh every time. No dragging. No broken references.
The Result
Here’s what each tool actually delivers — not what brochures claim:
| Criteria | Excel | Access |
|---|---|---|
| Handles 10,000+ rows without lag | Yes — if no volatile formulas | Yes — with indexed keys |
| Prevents duplicate contract numbers | Only with Data Validation (easily bypassed) | Yes — set Contract # as unique index |
| Enforces referential integrity (e.g., Vendor ID must exist) | No | Yes — cascade updates/deletes built-in |
| User-friendly forms for data entry | No — users paste into grids | Yes — bound forms with dropdowns and auto-fill |
| Calculate totals across related tables | Requires complex SUMIFS + INDIRECT (breaks easily) | One query: SUM(tPayments.Amount) GROUP BY tVendors.Name |
| Share live, multi-user editing | No — file locking kills concurrency | Yes — via backend split (front-end on each PC) |
| Keyboard shortcut to open Relationships view | N/A | Alt+J, R, L |
What Could Go Wrong
Mistake #1: Using Excel’s ‘Remove Duplicates’ on Vendor ID alone
You select column A → Data → Remove Duplicates. Excel deletes rows — but keeps the *first* instance. If CL-9221 (V-721) was entered before CL-8842 (same vendor), you lose the earliest contract. No warning. No log. Just gone.
Mistake #2: Copy-pasting Access queries into Excel for ‘formatting’
You export a query result to Excel to add colors and logos. Then someone edits the Excel copy — changes a value, adds a row — and saves over the original. The database stays clean. The report lies. This happens weekly on Team A’s Q3 dashboard.
Mistake #3: Naming an Access table ‘Data’ or ‘Sheet1’
It seems harmless. But when you later link it to Power BI or write VBA, DoCmd.OpenTable "Data" fails if another object uses that name. Use descriptive names: tVendorContracts, qActiveByManager. Save yourself 47 minutes of debugging.
Next step: Open Excel. Press Alt+D, L — that’s Data → From Access. Try linking to an existing .accdb file. Don’t import. Link. See how changes in Access instantly reflect in Excel — without manual refresh. That’s the boundary. Respect it.
