Data Validation Rules in LibreOffice Calc
Data validation in LibreOffice Calc allows you to control the type and format of data entered into specific cells, ensuring data consistency and preventing user entry errors. By defining validation criteria, you can restrict inputs to certain numeric ranges, date boundaries, character limits, or predefined dropdown selections. This guide outlines the core validation rules available in LibreOffice Calc, how they function, and the settings used to enforce them.
Accessing Data Validation
To configure validation criteria on any selected cell or range: 1. Select the target cells. 2. Go to the top menu and select Data > Validity. 3. Use the Criteria, Input Help, and Error Alert tabs to define your rules.
Core Data Validation Criteria
Under the Criteria tab, the Allow dropdown provides several rule types:
1. Whole Numbers
Restricts the cell to integer values only. Any decimal entry or text string will be rejected. You can apply operators such as equal to, less than, greater than, valid range (between), or invalid range (not between).
2. Decimal
Restricts input to numeric values, including numbers with decimal points. This rule supports the same comparison operators as the Whole Numbers rule, making it ideal for currency, weights, and measurements.
3. Date
Enforces valid calendar date inputs. Entries can be constrained to occur before, after, or between specific dates, preventing invalid chronological records.
4. Time
Limits cell values to valid time formats. You can restrict input to specific hours, minutes, and seconds using standard mathematical comparison operators.
5. Cell Range
Creates a dynamic dropdown selection based on values from an existing range of cells within the spreadsheet. When the source range updates, the allowed options in the validated cell update automatically.
6. List
Allows you to manually type a fixed set of permitted values separated by line breaks. Checking the Show selection list option displays these entries as a clickable dropdown menu directly in the spreadsheet cell.
7. Text Length
Restricts the number of characters allowed in a cell. This rule is useful for enforcing standardized input lengths, such as postal codes, identification numbers, or fixed-length abbreviations.
Additional Validation Controls
Blank Cell and Error Behavior
- Allow blank cells: If enabled, users can leave the cell empty without triggering an error.
- Show selection list: When using List or Cell Range, this generates an interactive dropdown arrow in the active cell.
Input Help
The Input Help tab allows you to create an informational tooltip that automatically appears when a user clicks on the validated cell. You can define a custom title and descriptive text explaining the input requirements.
Error Alerts
The Error Alert tab determines what happens when a user enters invalid data: - Stop: Blocks invalid data entry completely and prompts the user to retry or cancel. - Warning: Alerts the user to the invalid entry but allows them to keep the value by clicking “OK”. - Information: Informs the user of the rule violation without restricting data entry. - Macro: Executes a predefined LibreOffice Basic macro when an invalid entry occurs.