Preventing Bad Input
Svep för att visa menyn
The most important structural principle in a well-built Excel file: input and calculation are separate. Only dedicated entry cells should accept manual typing. Every other cell should be locked, blank, or formula-driven. This separation is what makes a workbook predictable as it grows.
Data validation
A rule applied to a specific cell range that controls what values can be entered. Acts as a gatekeeper before data reaches formulas or tables. Applied from Data → Data Validation.
Excel supports six types of validation rules. Each controls a different dimension of what's allowed.
1. Dropdown lists
Free-text entry in categorical columns ("Paid", "PAID", "paid", "Piad") creates inconsistencies that silently break filters and pivot tables. Dropdowns eliminate this entirely by making users choose rather than type.
- Static list: source typed directly into the validation dialog:
Paid,Pending,Cancelled; - Dynamic list: source points to a named table column on another sheet:
=INDIRECT("Table1[Status]").
INDIRECT converts a text string into a live range reference. The Data Validation source field doesn't directly accept structured table references like Table2[Statuses] — wrapping it in INDIRECT() lets Excel resolve the table name at runtime, including any new rows added after validation was set up.
Validation Rules
Text length, whole number, and date validation all work the same way under the hood — you define a condition, and Excel blocks anything that fails it. What differs is the dimension being checked:
- Text length doesn't look at the value itself — it counts characters using LEN() internally. So "1234" and "hello" are both length 4, regardless of type. Useful for phone numbers, postal codes, or any field with a fixed character requirement;
- Whole number checks that the value falls within a numeric range and has no decimal component;
- Decimal is the same boundary check but allows fractional values — useful for prices or measurements;
- Date is the most subtle. Since dates are stored as serial numbers, a rule like "greater than today" is actually a numeric comparison — TODAY() returns an integer, and the entered date just needs to produce a larger one to pass.
Input Messages vs Error Alerts
Validation has two communication layers. The first tells users what to enter before they try. The second controls what happens when they enter something invalid. These are configured in separate tabs of the Data Validation dialog.
Removing Duplicates
Use Data → Remove Duplicates. Select the columns to compare. Excel keeps the first occurrence of each combination and removes the rest. Best for imported or historical data.
There is no undo after closing the dialog if you save. Always work on a copy of the data or use Ctrl+Z immediately if the result looks wrong. The dialog also tells you how many duplicates were removed — check this number makes sense before closing.
Tack för dina kommentarer!
Fråga AI
Fråga AI
Fråga vad du vill eller prova någon av de föreslagna frågorna för att starta vårt samtal
