- dotTech - http://dottech.org -

How to add data validation to OpenOffice Calc spreadsheet cells [Guide]

You can add spreadsheet cell validation to highlight invalid data. If you add data validation to spreadsheets, invalid cell data is blocked. For example, to limit spreadsheet cells to numerical values only you can add data validation which blocks text being entered.

Most spreadsheet applications include data validation options. Among them is the freeware OpenOffice spreadsheet package, which you can add to your software library from this website [1]. Once you’ve added OpenOffice to your desktop or laptop, open Calc’s window in the shot below.

Data validation 2 [2]

Then select a variety of cells that will include the validation. Click Data and Validity to open the window below. There you should first select suitable data for the spreadsheet cells from the Allow drop-down list. Selecting Whole Numbers limits the cell data to numbers without fractional components. As such, with that data validation text and fractional numbers cannot be added to the selected spreadsheet cells.

data validation [3]

Next you should select the Error Alert tab. Click the Show error message when invalid values are entered check-box. Below that there is an Action drop-down list where you should select Stop. Input a title such as Invalid Data, and below that a suitable error message. That could be something like:

“You have entered invalid data in this cell. Please enter a whole number without a fractional component.”

Then click on OK to close the Validity window. Input invalid data in the spreadsheet cells you added the data validation to. The validation window should open as below.

data validation3 [4]

You can always make it a little clearer what the cell’s valid data is. To do so, select the cells that you added the data validation to; and then open the Validity window again. Click the Input Help tab, and click the Show input help when cell is selected check-box.

Data validation5 [5]

Input a title for the message window, and then add a suitable pointer that makes it clear what data somebody should enter selected cell. That could be something similar to, “Please enter whole numbers here.” Click OK to close the window, and then select one of the cells which you added the input help to. The selected cell should include a yellow text box as in the shot below.

data validation4 [6]

Now you can add a variety of data validation to your spreadsheet cells. Whilst you may be clear what the valid spreadsheet cell content is, somebody else might not be. Adding data validation ensures that those who open the spreadsheet enter valid data in selected cells, and makes it clear exactly what’s supposed be put in them.