Life Hack: How To Validate Data In Spreadsheets Without Microsoft Excel

Data validation is a crucial part of any data processing task. If your data isn’t accurate and complete, you won’t get accurate results. That’s why it’s important to check the accuracy and clarity of data before making any conclusions.

The most convenient way to perform data validation is using Microsoft Excel. This software tool offers all the required features to verify and validate data but its cost is not affordable for everyone. In this article, we will tell you how to validate data without having to purchase the Microsoft product.

Table of Contents

What Is Data Validation?

Data validation is a feature that is used to restrict the type of data or values that can be entered in a cell of a spreadsheet. It is designed to prevent users from entering incorrect values and avoid typing errors. In other words, data validation specifies what kind of data can be used in a cell according to certain conditions or criteria in order to ensure data integrity and consistency.

The most common scenario for this feature is creating drop-down lists that allow users to choose one value from a predefined range. This means that only one option can be selected from the list, which reduces the risk of errors.

When Is Data Validation Useful?

Data validation is practically useful when you share a spreadsheet with other users and want the data to be accurate and consistent during the collaboration process. You can use this feature in the following cases:

Restrict the number of options in a drop-down list. For example, you can limit a user’s selection to some predefined items.
Restrict numbers to a specific range. For example, you can specify a maximum percentage value or allow it to be a whole number within a certain interval.
Restrict dates or times to a specific period of time. For example, you can set a certain limit, and nobody will be able to select a day or time earlier than the current limit.
Restrict the number of text characters. For example, you can limit the text in a cell to any number of characters.

ONLYOFFCE Spreadsheet Editor, An Excel Alternative

Although Microsoft Excel remains one of the most popular spreadsheet applications for data validation operations, there are many alternative solutions. A great example is the ONLYOFFICE office suite. It’s an open-source software tool that combines three editors for text documents, spreadsheets and presentations and is available in the cloud, as a self-hosted solution and a free desktop app for Windows, Linux and macOS.

The ONLYOFFICE spreadsheet editor is an excellent Excel replacement because it natively supports the Microsoft formats allowing you to create and edit XLSX files without any compatibility problems. It also opens XLS, ODS, CSV files and saves spreadsheets to PDF.

The editor is equipped with a wide set of tools that make it possible to analyze data and perform complex calculations even at a professional level. It also allows you to automate routine tasks using micros and co-edit spreadsheets with other users in real time.

Data Validation In ONLYOFFICE

To access the data validation feature in the ONLYOFFICE spreadsheet editor, you need to choose a cell or a range of cells and click the Data tab. Among the available options, select Data Validation and you will see three sections: Settings, Input Message, and Error Alert.

In the Settings section, you can specify the type of data to be entered. To do so, choose the required parameter (any value, whole number, decimal, date, time, text length, etc.) and set a condition. Specify the lowest and the highest values and click OK. If someone enters a value that doesn’t correspond to the requirements, a warning message will appear.

To avoid errors, you can set up an input message that shows which values are acceptable when clicking a cell. To do so, click Data validation and choose Input message on the left. Enter the title and description of your message and click OK.

The error message is also personalizable, and you can edit it as you like by selecting one of the available styles: Stop, Alert or Message. Enter the title and description of your message and don’t forget to click OK.