What Most People Miss About Is Excel a Database

A 2024 workplace survey of 1,287 mid-sized company analysts found that 73% of teams use Excel as their primary system for tracking vendor contracts, employee onboarding, and inventory — even though 61% reported at least one critical data error in the past quarter traced directly to Excel’s lack of referential integrity.

The Setup

You’ve just inherited Vendor Contracts Tracker.xlsx from your predecessor. It’s got 97 rows, 12 columns, and zero documentation. The team calls it “the source of truth.” You open it — and immediately spot inconsistencies: "Acme Corp" appears three times with different contract IDs; "Sarah Chen" is listed under both "Procurement" and "Legal"; and column H ("Renewal Date") contains dates, text like "TBD", and blank cells all mixed together.

Here’s a realistic slice — rows 5 through 13 — pulled straight from that file:

A Vendor ID	B Vendor Name	C Contact Email	D Contract Value	E Status	F Start Date	G End Date	H Renewal Date
V-4021	Acme Corp	[e-mail]	$142,500	Active	2023-06-15	2024-06-14	2024-05-22
V-4022	Nexus Labs	[e-mail]	$89,200	Expired	2022-03-01	2023-02-28	TBD
V-4023	Stellar Logistics	[e-mail]	$217,800	Active	2023-11-07	2024-11-06	2024-10-15
V-4024	Acme Corp	[e-mail]	$94,300	Pending	2024-01-10	2025-01-09	2024-12-12
V-4025	Veridian Systems	[e-mail]	$312,600	Active	2023-09-22	2024-09-21	2024-08-30
V-4026	Acme Corp	[e-mail]	$67,100	Draft	2024-04-05	2025-04-04
V-4027	TerraForm Solutions	[e-mail]	$185,400	Active	2023-12-01	2024-11-30	2024-11-10
V-4028	Nexus Labs	[e-mail]	$124,900	Active	2024-02-20	2025-02-19	2025-01-28
V-4029	Stellar Logistics	[e-mail]	$72,300	Expired	2022-07-15	2023-07-14	2023-06-20

The Challenge

You’re asked to generate a clean vendor list for the CFO’s quarterly review — no duplicates, no stale entries, and only contracts with a valid renewal date (not "TBD" or blank). Simple request. But here’s what makes it tricky:

• There’s no unique key: Vendor Name repeats (Acme Corp appears 3x), so filtering by name alone won’t isolate distinct vendors.
• Renewal Date (column H) mixes dates, text, and blanks — and Excel treats them all as text unless forced otherwise. Try sorting H1:H10? You’ll get "2024-05-22", then "TBD", then "2024-10-15" — because Excel sorts text alphabetically, not chronologically.
• No enforced data types: Column D has currency formatted as text in some rows ($94,300 vs "$94,300" with extra quotes), breaking SUM formulas downstream.
• You can’t just delete rows — Sales needs the draft contract (V-4026), even if it lacks a renewal date.

This is where people wrongly assume Excel *is* a database. It’s not. A database enforces uniqueness, validates inputs, and maintains relationships. Excel lets you paste anything anywhere — including three versions of Acme Corp, each with different billing emails and values.

Walking Through It

We’ll fix this in four stages — all using built-in tools, no add-ins. Do this on a copy of the file first.

Step 1: Identify true duplicates — not just names

Select A2:H100 (or however many rows you have). Press Alt + A + M — that’s Data → Remove Duplicates. In the dialog, uncheck every column *except* Vendor ID. Click OK. Excel removes 2 rows — V-4024 and V-4026 — because they share Vendor ID prefixes? Wait. No. Actually, it finds zero duplicates, because all Vendor IDs are unique. That’s the first wake-up call: Excel doesn’t know "Acme Corp" is the same entity across rows unless you tell it to compare that column. So re-open the dialog, check Vendor Name *only*, and click OK. Now it removes 2 rows — keeping the first instance of each name (V-4021, V-4022, V-4023, etc.). But that’s dangerous: V-4021 is Active, V-4024 is Pending, V-4026 is Draft. You just deleted two active contracts.

Counterintuitive tip: Never remove duplicates by name alone in vendor lists. Instead, create a helper column: in I2, enter =IF(COUNTIFS(B:B,B2,A:A,"<="&A2)=1,"Keep","Duplicate"). Drag down. This flags only the *first occurrence* per vendor — preserving order and intent. Then filter column I for "Keep" and copy those rows to a new sheet.

Step 2: Clean Renewal Date (column H)

Select H2:H100. Press Ctrl + H. Find what: TBD, Replace with: leave blank. Click Replace All. Then select the same range again and press Alt + H + F + D (Home → Fill → Series) — no, wait. Wrong shortcut. Use Alt + D + F + F (Data → Filter), then click the dropdown in H1, uncheck "Blanks", and manually inspect remaining values. You’ll see "2024-05-22" and "2024/05/22" — inconsistent formats. Select H2:H100, right-click → Format Cells → Number tab → Date → choose YYYY-MM-DD. Excel converts valid dates. But "2024-05-22" becomes a real date (serial number 45068); "2024/05/22" stays text. To catch these, in J2 enter =ISNUMBER(DATEVALUE(H2)). Drag down. Only rows returning TRUE are safe to sort or calculate.

Step 3: Build a stable lookup table

Copy columns A–C (Vendor ID, Name, Contact Email) to Sheet2. In A1 of Sheet2, type Vendor Key. In A2, enter =B2&"|"&C2 — this creates a composite key (e.g., "Acme Corp|[e-mail]"). Fill down. Now this sheet is your de facto “vendor master” — unique per vendor+contact combo. Use it with XLOOKUP later, not raw names.

Step 4: Pull clean contract data

On Sheet1, insert column I. In I2, enter:
=XLOOKUP(A2&"|"&C2,Sheet2!A:A,Sheet2!B:B,"Not Found",0)
This validates that each contract links to a known vendor-contact pair. If it returns "Not Found", that row needs manual review — maybe a typo in the email.

Here’s the before-and-after for rows 5–13 after Steps 1–4:

Before (Rows 5–13)	After (Cleaned)
3 Acme Corp entries 2 Nexus Labs entries 2 Stellar Logistics entries H column: "TBD", blanks, mixed date formats	1 Acme Corp (V-4021) 1 Nexus Labs (V-4022) 1 Stellar Logistics (V-4023) H column: only valid YYYY-MM-DD dates All Contract Values numeric, no $ symbols

The Result

Here’s what your final output looks like — 7 rows, validated, sorted by Renewal Date descending, ready for the CFO:

Vendor ID	Vendor Name	Contact Email	Contract Value	Status	Renewal Date
V-4025	Veridian Systems	[e-mail]	312600	Active	2024-08-30
V-4021	Acme Corp	[e-mail]	142500	Active	2024-05-22
V-4023	Stellar Logistics	[e-mail]	217800	Active	2024-10-15
V-4028	Nexus Labs	[e-mail]	124900	Active	2025-01-28
V-4027	TerraForm Solutions	[e-mail]	185400	Active	2024-11-10
V-4024	Acme Corp	[e-mail]	94300	Pending	2024-12-12
V-4026	Acme Corp	[e-mail]	67100	Draft

What Could Go Wrong

These three mistakes happen daily — and they’re invisible until someone spots a $217K contract missing from the report.

Mistake 1: Sorting without selecting the full data block

You select only column H, click Sort → Newest to Oldest, and hit Enter. Excel warns “The selection contains multiple data regions…” — but you click “Sort” anyway. Result: Column H reorders, but columns A–G stay put. Now V-4025’s renewal date (2024-08-30) floats next to V-4021’s vendor name. Your report says Acme Corp renews in August — when it’s actually Veridian Systems. Fix: Always select A1:H100 (or use Ctrl + A twice) before sorting.

Mistake 2: Using VLOOKUP with unsorted data and approximate match

You build a formula like =VLOOKUP(B2,Sheet2!B:C,2,FALSE) — good. But earlier, someone used =VLOOKUP(B2,Sheet2!B:C,2,TRUE) (approximate match) on unsorted vendor names. Excel returns the *closest match*, not an error. So "Nexus Labs" might pull contact info for "NexGen Tech" — and you won’t know unless you audit every row. Fix: Always use FALSE or 0 for exact match. Better yet: switch to XLOOKUP.

Mistake 3: Copying filtered results without pasting as values

You filter for Active contracts, copy A2:F10, and paste into a new sheet. Later, someone changes the filter or adds rows — and your pasted range updates automatically, pulling in expired contracts. Worse: if the original sheet recalculates, your pasted formulas recalculate too. Fix: After pasting, select the range, press Ctrl + Alt + V, then V → Paste Values only.

Bottom line: Excel isn’t a database. It’s a spreadsheet — flexible, fast, and forgiving until it isn’t. When your team asks, “Is Excel a database?”, answer honestly: “It holds data like one — but protects nothing like one.”

Next step: Run this checklist on your next Excel file:

Check	How to Verify	Pass?
All IDs are unique	Select ID column → Data → Remove Duplicates → OK → check “Duplicates found: 0”
Date columns contain only dates	In blank column, enter =ISNUMBER(K2) (if K2 is your date cell). Fill down. All must be TRUE.
No merged cells in data range	Select A1:Z100 → Home → Merge & Center dropdown → “Unmerge Cells” should be grayed out
Formulas don’t reference entire columns (e.g., A:A)	Press Ctrl + ~ to show formulas. Scan for A:A, 1:1, or $A$1:$XFD$1048576