Application skills and in-depth analysis of data validity in tables

The accuracy of data entry often determines whether a spreadsheet can truly deliver value. When multiple people collaborate on the same data sheet, without a unified standard, errors are prone to occur: someone might enter a date in an inconsistent format, someone might enter an overly long text, or someone might type numbers instead of text. These issues not only affect statistical analysis but also create tedious correction tasks in subsequent work. To address this pain point, spreadsheet software (such as WPS官网 Spreadsheets and Excel) provides a data validation feature to help users ensure data quality during the entry phase.

Table of Contents

Core Concepts of Data Validity

Data validation, as the name suggests, restricts what can be entered into a cell through pre-set rules. This feature allows spreadsheet creators to define input criteria in advance, preventing invalid or erroneous data from entering the dataset.

Common restrictions include:

Integer or decimal range : Specifies that only numbers within a certain range can be entered, for example, fractions can only be between 0 and 100.
Date and Time : The input content must be a date and within a certain time period. For example, only dates within 2025 are allowed.
Text length : Specifies the length of input characters, for example, the student ID is fixed to 10 digits.
List drop-down selection : provides preset options, and users can only select from the drop-down menu. It is commonly seen in department names, regional classifications, etc.
Custom formulas : Use functions to set more complex validation logic, such as requiring an input to equal a value in another column.

These rules are equivalent to adding a layer of “gatekeeper” to the table to ensure that the data entering complies with the regulations.

Basic steps to enable data validation

The process of applying data validation function in a table is not complicated. Take WPS电脑版 table as an example:

Select a cell range : Select the cells or entire column to which you want to apply the rule.
Enter the Data tab : Click the [Data] option in the menu bar.
Open data validity settings : Find the [Validity] function button and click it to enter the settings dialog box.
Select validation conditions : Select “Integer”, “Decimal”, “Date”, “Text Length”, “Sequence” and other conditions from the drop-down menu as needed.
Set the rule range : for example, limit the input number to be between 1 and 100, or select the custom sequence option.
OK and Apply : After completing the settings, click “OK” and the selected cells in the table will be restricted to only enter content that meets the conditions.

This process may seem simple, but it can greatly improve data reliability. For example, in a financial reimbursement form, the “Amount” column can be restricted to only numbers greater than 0, eliminating statistical errors caused by mistakenly entering letters or negative numbers.

Error warnings and input prompts

Simply setting rules is not enough; it is also crucial to provide users with timely feedback during input. Data validation provides two types of auxiliary functions:

1. Input information prompt

In the settings interface, you can add an “Input Information” field. When a user clicks a cell, a prompt will automatically pop up, explaining how to enter the information. For example, in the “Date of Birth” column, a prompt might read “Please enter a date in the format YYYY-MM-DD.” This approach provides instant guidance, preventing users from making mistakes due to a lack of understanding of the rules.

2. Error Warning

If the user enters content that does not conform to the rules, the system will immediately pop up an error dialog box to remind the user that the input is invalid. There are three modes of error prompts:

Stop : Completely prohibit the entry of incorrect data.
Warning : Allows the user to decide whether to keep the input, but warns of the risks.
Information : This is just a reminder and will not be a mandatory block.

For example, when entering a score greater than 100 in an exam score sheet, you can select “Stop” to ensure that out-of-range values are not entered; when entering free text in a questionnaire, you can use the “Information” mode for reference only.

Advanced application: circle invalid data

In large-scale data tables, even if rules are set, it is inevitable that someone will bypass or copy and paste incorrect content into the table. To address this situation, data validation provides a function to circle invalid data .

How to do it:

Select the area to be inspected;
Click [Data] > [Validity] > [Circle Invalid Data];
The table automatically marks all cells that do not comply with the rules with a red circle.

This feature is very practical, especially when you need to clean up existing data. It can quickly locate erroneous content and save the trouble of checking line by line.

Advanced skills: multi-level linkage drop-down menu

The true power of data validation lies in its advanced uses when combined with functions. For example, you can use the INDIRECT function to create a multi-level drop-down menu.

Application scenarios:

Select “Province” in the first-level menu, and the second-level menu will automatically display the “City” under the province.
Select “Product Category” at the first level, and the “Specific Models” under that category will be displayed at the second level.

Implementation ideas:

First, create a categorized data source, such as a “province-city” table;
Set the name of the city list for each province;
The first level menu is restricted to the “Province” list through data validity;
The secondary menu =INDIRECT(选中省份单元格)calls the corresponding named area through the formula.

This not only saves users time and effort when filling out the form, but also helps avoid spelling errors or inconsistent input.

Application cases of data validity in real scenarios

1. Student performance management

The score is limited to 0-100;
Use error prompts to prevent teachers from mistakenly filling in scores that are out of range;
Use the circle to quickly find outliers.

2. Collection of corporate personnel information

Limit the ID card number to 18 characters;
The restricted date format is “YYYY-MM-DD”;
Department names are selected through a drop-down list to avoid spelling differences.

3. Questionnaire and market data

Gender, education, region and other fields can be selected through the drop-down menu;
The age limit is within a reasonable range (e.g. 18-60 years old);
Multi-level linkage enables regional hierarchical selection and more standardized data.

Through these applications, the cost of data collation can be significantly reduced, ensuring the efficiency of subsequent statistics and analysis.

Considerations for using data validity

Avoid overcomplication : While custom formulas are powerful, overly complex rules can be daunting for users.
Clear data source management : For drop-down menus, it is best to create a separate “data source table” to avoid confusion.
Consider the copy and paste problem : Some users may directly copy and paste external data, which may bypass validation and require subsequent checks.
Combined with conditional formatting : Conditional formatting can further intuitively show whether the data is abnormal, complementing data validity.

Improve the overall standardization and usability of collaborative forms

Data validation is a seemingly simple yet incredibly useful feature. It not only reduces input errors but also improves the overall standardization and usability of collaborative spreadsheets. Through features like input prompts, error warnings, and circling invalid data, users can more intuitively understand the rules and avoid repeated revisions. Further integration with functions allows for complex operations like multi-level linkage, making spreadsheets even more intelligent. Whether collecting data in academic research or using spreadsheets in daily business management, data validation helps users control data quality, reduce errors at the source, and improve overall efficiency.