Keeping Data Clean: Validation Rules a Spreadsheet Can’t Enforce
What spreadsheet data validation in Excel and Google Sheets can and can’t do, the rules small businesses need that a grid can’t enforce (required fields, uniqueness, links between records, status order, who may change what), and how an app enforces them.
Key Takeaways
- Use spreadsheet validation; just don’t rely on it. Dropdowns, number ranges and date rules catch typing mistakes. They don’t catch pasted data, and they don’t check existing cells unless you ask.
- Microsoft says copy and fill bypass validation. Excel’s documentation says validation shows messages and prevents invalid entries only when users type directly in a cell; when data is copied or filled, the messages do not appear.
- The rules that matter most span more than one cell. No duplicate customers, every job linked to a real customer, statuses in the right order, only managers can approve. A spreadsheet can’t enforce these reliably.
- Apps enforce rules at three layers. The form (right inputs), the server (business rules) and the database (constraints that nothing can bypass).
- Clean data is a design decision. Write your rules down before you build anything. Each one becomes a field type, a check or a permission.
Quick-Start Prioritization Framework
| Rule | Spreadsheet | Database-backed app |
|---|---|---|
| Value must be from a list | Yes (dropdown), but pastes can bypass it | Yes, enforced on every save |
| Number or date in a range | Yes, for typed entries | Yes, enforced on every save |
| Field is required | Not reliably; blank cells are allowed | Yes |
| No duplicates (one row per customer) | Only with formulas or warnings | Yes, with a unique constraint |
| Must link to an existing record | Only by lookup formulas that can break | Yes, with a reference (foreign key) |
| Statuses in order | No | Yes, in the server logic |
| Only certain people may change a field | Range protection, per sheet | Yes, per role and per record |
| Who changed it and when | Version history, hard to search | Recorded on the record |
Every business spreadsheet eventually fills with small errors: a date typed as text, a customer spelled three ways, a job marked Paid before it was Invoiced, a blank where the price should be. Excel and Google Sheets both offer data validation, and it helps. But validation in a spreadsheet is a polite suggestion attached to a cell. It can be pasted over, it doesn’t check what was there before, and it can’t express rules that span rows, tables or people. This guide explains what spreadsheet validation does well, where Microsoft’s and Google’s own documentation says it stops, the rules small businesses most often need that a grid can’t enforce, and how a database-backed app enforces them.
What spreadsheet validation does well
Both Excel and Google Sheets let you attach rules to cells. Used consistently, they prevent a large share of typing errors.
Excel
Microsoft’s documentation lists validation types for whole numbers, decimals, lists, dates, times, text length and custom formulas. You choose one of three alert styles: Stop requires users to fix the error before proceeding; Warning asks them to choose whether to continue; and a third, milder style warns but lets them proceed after dismissing the message.
Google Sheets
Google Sheets offers dropdowns from a list you type or from a range of cells, along with other rule types. Google’s help says an entry that doesn’t match the list is rejected by default, and that you can change this in Advanced options to show a warning instead.
Where to use it
- Choice columns: status, category, site, payment method.
- Dates: no dates in the future for completed work, no dates before the business opened.
- Numbers: quantities above zero, percentages between 0 and 100.
- Text length: phone numbers, postcodes, reference codes.
Where spreadsheet validation stops
These limits are documented by the vendors themselves or follow from how a grid works.
Pasting and filling bypass it
Microsoft’s “More on data validation” page says validation is designed to show messages and prevent invalid entries only when users type data directly in a cell, and that when data is copied or filled, the messages do not appear. Pasting a block from another file, an email or a chat is how most bad data enters a business spreadsheet.
Existing data isn’t checked
The same page says you can apply validation to cells that already contain data, but Excel does not automatically notify you that existing cells are invalid. You have to run Circle Invalid Data to find them.
Warnings can be clicked through
If the rule is set to warn rather than reject, which people often do because rejection annoys them, any value goes in with one click.
Rules live on cells, not on records
Validation is attached to a range. Insert rows outside it, sort in a way that moves data, or copy the tab, and the rule may not follow. New columns start with no rules at all.
Shared or protected sheets can’t be changed
Microsoft notes you cannot change data validation settings if the workbook is shared or the sheet is protected, which makes maintaining rules awkward in exactly the files many people use.
Rules a spreadsheet can’t enforce
These are the rules that keep operational data trustworthy, and none of them can be enforced reliably in a grid that anyone with edit access can change.
1. Required fields
A job without a customer, an order without a quantity, a timesheet without a date. A spreadsheet allows blank cells by design. In an app, a form can’t be submitted without required fields, and the database can refuse a record with a missing value.
2. Uniqueness
One row per customer, one invoice number per invoice, one stock item per SKU. Conditional formatting can highlight duplicates after the fact. A database unique constraint refuses the duplicate when it is created. PostgreSQL’s documentation describes unique constraints as ensuring the data in a column, or a group of columns, is unique among all rows in the table.
3. Links to real records
Every job should belong to a customer that exists. In a spreadsheet, the job row holds a typed name that may not match the customers tab (“J. Smith” vs “John Smith”), and lookup formulas silently return errors. A database foreign key makes the link a reference to a specific record, and PostgreSQL’s documentation describes it as maintaining the referential integrity between two related tables.
4. Status order and allowed transitions
A job should go Scheduled, In progress, Done, Invoiced, Paid. It shouldn’t jump from Scheduled to Paid, or go back from Paid to Scheduled without a reason. A spreadsheet dropdown allows any value at any time. An app’s server logic can allow only valid next steps and require a note for exceptions.
5. Rules that depend on other fields
“If the job is marked Problem, a note is required.” “If payment method is Invoice, a billing email is required.” “End time must be after start time.” Excel’s custom formulas can express some of these for typed entries, but they are fragile and invisible. In an app they are explicit checks.
6. Who may change what
Staff can mark their own jobs done but not change the price. Only a manager can approve time off. Only the owner can edit pay rates. Spreadsheets protect ranges for named people, but can’t say “each person can edit only their own rows.” An app checks the logged-in person’s role and the record’s owner on every change.
7. Values computed, not typed
Totals, hours worked, days overdue and stock on hand should be calculated, never typed over. In a spreadsheet, anyone can overwrite a formula with a number and nobody notices. In an app, computed values aren’t editable fields.
8. An audit trail
Who changed the price, and when? Spreadsheets keep version history, but searching it for one change is slow. An app can record who created and last changed each record, or keep a full change history for sensitive fields.
How an app enforces rules: three layers
A well-built app checks data in three places. Each layer catches what the one before might miss.
| Layer | What it does | Example |
|---|---|---|
| Form | Right input for each field; immediate feedback | Date picker; quantity must be a number; required fields marked |
| Server | Business rules and permissions on every save | Only managers can approve; status can only move forward; note required for Problem |
| Database | Constraints nothing can bypass | Unique invoice number; job must reference a customer; quantity can’t be empty |
Why the form alone isn’t enough
Form checks improve the experience but run on the user’s device, so they can be skipped by imports, integrations or a bug. The server and database are where rules are enforced for everyone. This is also why importing old data into an app is a good moment to find errors: the database refuses rows that break its rules.
Write your rules down first
Before choosing or building a tool, list your data rules in plain sentences. It takes an hour and becomes the most useful part of any brief.
- For each table: which fields are required?
- Which values must be unique?
- Which fields must point to another record (customer, site, staff member)?
- What are the statuses, in order, and who can move a record between them?
- Which fields depend on others (if X, then Y is required)?
- Which fields are calculated and must never be typed?
- Who may see and change each field?
- Which changes need to be recorded with who and when?
Example rules for a small delivery business
“Every delivery belongs to an existing customer and an existing driver. Delivery date is required and can’t be more than 60 days ahead. Status moves Booked, Out for delivery, Delivered or Failed. Failed requires a reason from a list. Delivered requires a photo. Drivers can change status only on their own deliveries. Only the office can change the price. Order numbers are unique.”
Cleaning up the data you already have
Rules apply to new data. Existing data needs a one-off cleanup, best done in the spreadsheet before moving it anywhere.
- Use Circle Invalid Data in Excel, or a filter on each column in Sheets, to find values outside your rules.
- Standardise choice columns with find and replace.
- Remove duplicate customers and merge their history.
- Fill or flag required fields that are blank.
- Replace typed totals with formulas, or drop them if the app will calculate them.
Common mistakes
- Setting validation to “warning” everywhere, so nothing is ever enforced.
- Adding validation after a year of free-text entries without cleaning the old data.
- Free-text fields for things you want to report on later.
- Relying on colour for status, which no rule can check.
- Letting everyone edit every column because protecting ranges is fiddly.
If you move to an app
Floot, the AI app builder that publishes this site, builds apps on a Postgres database. You can describe your rules in plain English (required fields, unique numbers, status order, who may change what) and they become part of the app’s forms, server logic and database.
Frequently Asked Questions
Does data validation stop people pasting bad data into Excel?
Not reliably. Microsoft’s documentation says validation messages appear and invalid entries are prevented only when users type data directly in a cell; when data is copied or filled, the messages do not appear.
Can Google Sheets reject values that aren’t in a dropdown list?
Yes. Google’s help says entries that don’t match the list are rejected by default, and you can switch the rule to show a warning instead in Advanced options.
How do I find invalid data already in an Excel sheet?
Apply your validation rules, then use Data, Data Tools, Data Validation, Circle Invalid Data. Excel does not flag existing invalid cells automatically.
Why can’t a spreadsheet stop duplicate customers?
Spreadsheets can highlight duplicates with formulas or formatting, but they can’t refuse one at the moment it is created. A database unique constraint can.
What validation should a small business app have?
Required fields, choice lists instead of free text, unique reference numbers, links to existing customers or staff, statuses that move in order, rules that depend on other fields, and permissions that limit who may change what.
Sources
- Microsoft Support, Apply data validation to cells, 2026 https://support.microsoft.com/en-us/office/apply-data-validation-to-cells-29fecbcc-d1b9-42c1-9d76-eff3ce5f7249
- Microsoft Support, More on data validation, 2026 https://support.microsoft.com/en-us/office/more-on-data-validation-f38dee73-9900-4ca6-9301-8a5f6e1f0c4c
- Google Docs Editors Help, Create an in-cell dropdown list, 2026 https://support.google.com/docs/answer/186103
- PostgreSQL Documentation, Constraints https://www.postgresql.org/docs/current/ddl-constraints.html