In Excel, you can enter any value into a cell by default. Data validation is used to help prevent a user from accidentally or incorrectly entering information into a cell.
Use this workbook for the chapter.
To open the Data Validation window, complete the following steps:
- Choose the cell(s) to apply data validation to.
- Select the Data tab on the ribbon toolbar
- Select Data Validation in the Data Tools field. (See Figure 20.1)
The Data Validation window will open. The window will include three tabs for Settings, Input Message, and Error Alert. Let’s look at the Input Message tab.
The Input Message menu has Shows Input Message When Cell is Selected enabled by default. An input message will display whenever the user selects the designated cell. Fill in the Title and Input message fields, then press OK. (See Figure 20.2)
Although they appear similar, an input message is different from a comment attached to a cell. An input message requires that the user selects the cell first to view it. A cell comment is displayed whenever the user moves the mouse over the cell, and the cell contains a little colored triangle in the top right corner.
Now, let’s look at Data Validation’s Settings tab. In this tab, we can set the validation criteria by allowing specific data. For example, let’s select Text length from the Allow field’s drop-down list.
The Text length option allows the user to enter text into a cell as long as the length is between the designated minimum and maximum values.
For example, if the minimum value is 4, and the maximum is 10, then the user must enter between four and ten characters into the cell. Otherwise, if less than four or more than ten letters are entered, an error will display stating that the entry isn’t valid. (See Figure 20.3)
Additionally, we can select other data rules such as Equal To to restrict valid input data to the designated length. (See Figure 20.4)
The Decimal Number option will require the user to input a decimal between the designated minimum and maximum values.
For example, if the minimum is zero and the maximum is one, then the user must enter a decimal number between 0 and 1 (such as 0.3). Otherwise, if the user enters a number lower than 0 or higher than 1 (such as 5), an error message will be displayed. (See Figure 20.5)
The Date option works the same way. The Start and End date values designate the range of valid dates that can be entered in the cell. (See Figure 20.6)
The List option is an essential tool that will be used frequently. It allows the user to enter or select valid data from a drop-down list designated in the Source input.
For example, if “Item1,Item2” is entered into the Source input, a drop-down list will be available in the specified cell with those options. The user may also type “Item2” without receiving an error. Additionally, the Source data may reference a list of cells with the desired input options. (See Figure 20.7)
The error message for an invalid cell input can be modified in the Error Alert tab. The type of error symbol can change to Stop, Warning, or Information associated icons. The title of the error message pop-up window and the window’s content can be customized in the Title and Error message fields. (See Figure 20.8)
The XLOOKUP function in conjunction with a data validation list is a powerful tool for a dashboard. We can create a dashboard to look up data about a specific part selected using the data validation list.
In this example, we’ll create our dashboard with a cell formatted with the data validation list referencing part numbers in our Data spreadsheet. (See Figure 20.9)
Next, we’ll enter an XLOOKUP formula into a separate cell. The XLOOKUP formula requires three data references—lookup value, lookup array, and return array. The result will display the data of the designated column in the formula depending on the part selected in our list. (See Figure 20.10)
- Type =XLOOKUP and a left parenthesis ( ( ).
- Select the lookup_value data; in this case, it will be the data validation list of part numbers.
- The lookup_value in the formula designates the value to search for in the lookup_array coming up.
- Tip: Use the F4 key to anchor this referenced cell. We will be copying this formula for other data references, but we don’t want this cell reference to change.
- Select the lookup_array; this will be the list of part numbers on the Data spreadsheet.
- The lookup_array is the index of cells the formula will search through to find the previously designated lookup_value.
- Tip: Use the F4 key to anchor these referenced cells too.
- Select the return_array data; this will be the column of data associated with the parts (Price, Lead Time, Order Qty, Class, % OTD columns). (See Figure 20.11)
- The return_array is the index of values the formula will reference and return the specific data based on the search performed using lookup_value and lookup_array.
- Tip: Use the F4 key to anchor only the rows.
- Type a right parenthesis ( ) ) and press Enter to complete the formula.
Finally, copy the formula for the remaining data, check that the formulas reference the correct cells, and organize the dashboard as needed. The dashboard will now display the data for the specified part number that the user selects from the list.