Data Validation with Strict Columns and Cells

In, all data types (other than Automatic) include the option to restrict cells or columns only to the specified data type. For instance, Strict can prevent users from entering anything that is not a number in a Number type column. This setting is represented by the Strict option mceclip1.png in the dialog box to edit a cell or column's data type.

You can use Strict to restrict or validate the type of data into a cell or restrict a column to only data of the specified data type. For instance, if you use for bug tracking, you might wish to restrict a column for Due Date to only be the Date data type, that way no one can input vague entries like "five days from now."

How To Restrict Data Type

In workbooks, you can define strictness at three levels: 

  • The column level
  • The cell level 
  • Or a range of cells

First, set your column, cell, or range's data type by selecting the column, cell, or range, right-clicking, and selecting Edit data type... 

All data types—except for the Automatic data type (which automatically converts text values to the most appropriate data type, possibly different ones for different cells)—can then be restricted. To set a cell, range of cells, or column as strict, select the Strict checkbox. For instance, for this "Strictly Text Column".


Restricted Columns

Restricted columns have two unique properties over restricted cells. First, setting the data type of a column to Strict means that you cannot change the data type of cells in that column. Column-level Strict helps prevent anyone from changing the data type of one cell to get around your restriction. For instance, in that bug tracker Due Date column, no one can change the cell data type to Text and write, "When I feel like it."

Second, only cells in the table region are restricted. Strict Data Types do not affect the header region. Cells above the Table Header row are not restricted to data type, allowing you to add header information with custom formatting above your table. 

Converting Existing Data to A Restricted Data Type

When converting existing data to cells, columns, or cell ranges with Strict enabled, all previous data must be of that data type, or the cell will be emptied. For instance, converting a range of cells that contain text and numbers to a Number data type will empty cells that contain text (even if a given cell also contains numbers).

If you accidentally set a column to Strict and lose data in it that didn't match the data type, click the Undo button in the toolbar mceclip4.png to go back.