Excel Guides

Using Data Validation in Excel

Data validation is a feature in Excel that allows you to control what data is entered into a cell. You can specify the type of data that you want to allow, and you can also set up rules to restrict the values that can be entered. Data validation is a useful tool for ensuring that data is entered correctly, and it can also help to prevent errors in your worksheets.

To set up data validation, select the cell or range of cells that you want to validate. Then, go to the Data tab on the ribbon and click the Data Validation button. This will open the Data Validation dialog box.

In the dialog box, you can specify the type of data that you want to allow. The options are:

  • Any value: This option allows any data to be entered into the cell. This is the default setting.
  • Whole number: This option allows only whole numbers to be entered into the cell. Decimal values are not allowed.
  • Decimal: This option allows decimal values to be entered into the cell. Whole numbers are also allowed.
  • List: This option allows you to specify a list of values that can be entered into the cell. You can enter the values manually, or you can select them from a range of cells in your worksheet.
  • Date: This option allows only date values to be entered into the cell. The date must be entered in a specific format, such as dd/mm/yyyy or mm/dd/yyyy.
  • Time: This option allows only time values to be entered into the cell. The time must be entered in a specific format, such as hh:mm:ss or hh:mm AM/PM.
  • Custom: This option allows you to specify a custom formula that determines whether a value is valid. The value must meet the criteria that you specify in order to be considered valid.

Once you have selected the type of data that you want to allow, you can then set up rules to restrict the values that can be entered. For example, if you only want whole numbers between 1 and 10, you would select the Whole number option and then enter 1 and 10 in the Minimum and Maximum boxes. You can also specify whether blank cells are allowed, and whether duplicate values are allowed.

If you want to allow multiple types of data, such as whole numbers and dates, you can select the Allow multiple selections check box. This will open up additional options where you can specify which types of data are allowed.

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.