I opened a store orders workbook in Microsoft Excel and found seven of its twenty price lookups returning #N/A. This was not one of the spreadsheet habits worth unlearning early, and no formula was broken. Two sheets disagreed about how to spell a product name, and the lookups trusted the wrong one.

That is not a formatting problem. It is what relational data looks like when it sits somewhere that enforces nothing about it. Five habits tell you when a workbook has crossed that line, and each one has a name in database terms.

The same product list lives on two different sheets

This is a normalization failure, and the copies drift apart

You need a filtered version of the product list for one report, so you copy the tab. That is the origin story for almost every duplicated lookup table. In the store orders workbook I built to test this, a Products sheet and a Product_List sheet each hold eight rows that are supposed to match.

They do not. Three product names are spelled differently. “USB-C Hub” on one sheet is “USB C Hub” on the other, and 27-inch Monitor loses its hyphen. One product exists only on the first sheet, one only on the copy, and Mechanical Keyboard is priced at $89.50 on one and $84.50 on the other.

Now, if you point a lookup using =VLOOKUP(E2,Product_List!$B:$D,3,FALSE) at the wrong sheet, the damage lands immediately. This returns #N/A for seven of the twenty orders, because the item names in the orders sheet match the first list and not the copy. Those seven failures are the visible half. The rows that do resolve are the dangerous ones, because they return the stale $84.50 with no sign that a newer price exists in the same file.

Databases avoid this through normalization, which means every fact is stored in exactly one place. The Excel equivalent is a single product table the other sheets reference, with any filtering done in the report rather than in a copy of the source.

Record IDs get typed instead of enforced

A primary key is generated and refused on collision, never typed

Record IDs feel like a solved problem until you check who enforces them, and in most workbooks the answer is nobody. The order IDs in the test file are hand-typed text. ORD-1014 sits on two rows belonging to different customers, and one row reads ORD 1019 with a space instead of a hyphen.

The same drift reaches customer records. CUS-201 is entered as both Marcus Bell and M. Bell, and CUS-204 as Dana Whitfield and D. Whitfield, so grouping orders by customer name splits one person into two.

That ID column is doing the job of a primary key, the field a database uses to guarantee every record can be identified uniquely. The guarantee is the whole point, and typing cannot provide it. Dropping this into a helper column at least flags a repeat the moment it appears.

=IF(COUNTIF($A:$A,A2)>1,"Duplicate","OK")

Conditional formatting does the same job visually, and a one-click pass that catches spacing and formatting inconsistencies picks up the rest. Both run after the fact. A database generates the next ID itself and rejects a colliding record, so the bad entry never lands at all.

Item 1, Item 2, and Item 3 keep repeating

Repeating groups of columns violate first normal form

Look at the header row. If it reads Item 1, Qty 1, Item 2, Qty 2, Item 3, Qty 3, the table holds a repeating group, and one row is doing a job that belongs to several. Across the twenty orders in the test file, only ten use the second slot and four use the third.

Most of those columns sit empty, reserved for a case that rarely happens. The cost appears the first time you ask something simple, because counting how many wireless mice sold means summing three separate column pairs with the following formula.

=SUMIF(E:E,"Wireless Mouse",F:F)+SUMIF(G:G,"Wireless Mouse",H:H)+SUMIF(I:I,"Wireless Mouse",J:J)

Add a fourth slot and that formula grows again, along with every other formula pointing at columns to the right of the insertion point. First normal form is the rule being broken, and it says a table should not repeat a group of columns to store several values of the same kind.

The corrected structure is two tables, an orders table with one row per order and an order items table with one row per item on it, joined on the order ID. The count above collapses into a single SUMIF over one column.

Formulas reach into a second workbook

A foreign key held together by a file path is not a constraint

A formula that names another file has made that file a dependency. The Supplier Cost column in the orders workbook cannot calculate on its own, because it reaches into a second workbook that has to be sitting exactly where it was when this one was last saved. The bracketed filename inside the reference is the giveaway as shown below.

=VLOOKUP(E2,'[Supplier_Prices.xlsx]Costs'!$A:$B,2,FALSE)

If you rename that file, the column fills with #REF!. Move it and Excel asks you to locate the workbook before anything recalculates. This is a foreign key in every respect except the one that matters, since a database maintains the relationship itself and refuses changes that would break it.

Caching is what catches people out. Excel keeps the last values it saw so the column can look healthy while showing figures from a source version that no longer exists. There is no automatic sweep, so a pass through the Name Manager is where I start, auditing named ranges for broken references before trusting any number.

The structural fix is to stop referencing the file and start importing it. A Power Query connection pulls the supplier table in as data you refresh on demand, so a missing source fails loudly in one place instead of silently across a column.

Macros and buttons made the file an application

The application layer and the storage layer now live in one file

There comes a point where nobody types into the workbook at all. A form collects the order, a button writes it to the next empty row, and a second macro rebuilds the summary sheet. I have inherited files like this, and opening the raw sheet always feels like stepping backstage.

Macros earn their place, and there is a real case for the point where a macro does what a formula cannot. The concern is not automation. An entry form built on a sheet is an application layer, and a database keeps that layer separate from storage, so the storage can enforce its own rules regardless of what the interface does.

The failures also move out of sight. When a macro writes to the wrong row or a renamed sheet breaks a reference, the error lives in VBA rather than in a cell. Fixing it means reading code, which narrows the repair list to whoever built the file. A proper separation puts the data somewhere with constraints and lets the interface reach it through defined operations. Short of rebuilding, document what each macro touches, because a file only one person understands has a shelf life.

Excel already ships with the layer that fixes most of this

The next time you catch yourself copying a lookup table, load it into Power Query instead and let every sheet reference that single query. The drift problem disappears, and it opens the door to defining relationships between tables without a single lookup formula.

Be honest about the ceiling, though. That layer fixes structure, not concurrency. If three people need to edit records at the same time under different permissions, nothing inside Excel solves that, and a real database is the answer. Most workbooks never get that far. They need one reference table instead of two, and two narrow tables instead of a very wide one.

OS

Windows, macOS

Supported Desktop Browsers

All via web app

Developer(s)

Microsoft

Free trial

One month

Price model

Subscription

iOS compatible

Yes

Microsoft Excel is a powerful spreadsheet application used for data organization, analysis, and visualization. It supports formulas, functions, pivot tables, and charts to process complex datasets efficiently. Widely used in business and education, Excel also integrates with other Microsoft 365 apps for collaboration, automation, and real-time data insights.