1. How to do data validation in Excel
We will work with a sample dataset from a fictitious company called "OptiSupply Solutions"
You are part of the data management team at OptiSupply Solutions, which distributes various products.
As part of your responsibilities, you maintain and validate data in the company's inventory management system.
Accurate inventory data is crucial for making informed purchasing decisions, managing supplier relationships, and ensuring customer satisfaction.
In this video, you'll learn step-by-step how to apply data validation in Excel using drop-down lists to ensure accurate entries.
Data Validation helps ensure that the data entered into your spreadsheet meets specific criteria, which is crucial for maintaining data integrity.
Let's highlight the cells where the Data Validation rule will be applied. To do so,
Open your Excel workbook containing the dataset.
Highlight the column or select the range where you want to apply Data Validation. For example, if you want to validate the Supplier_Name column, we can select the range D2:D20. You can select more, but this is fine for our example.
In another worksheet named `Suppliers`, we have a list of supplier names. Users can select suppliers from this list, helping to avoid typos or incorrect entries.
Navigate to the Data tab in the Excel ribbon.
Click on Data Validation icon in the Data Tools group.
A dialog box will appear, showing the Data Validation settings.
In the Data Validation dialog box, stay on the Settings tab.
Under Allow, choose the type of validation you want; in this case, we will use a List.
This is ideal for the Supplier_Name column, where you can provide a predefined list of suppliers or type them separated by commas.
Here, we select the range from A2 to A7 from the Suppliers sheet.
Go to the Input Message tab to provide a message that will appear when users select the cell. This can guide them in entering the data.
Switch to the Error Alert tab to customize the error message if invalid data is entered. This can help users understand why their entry was rejected. You can also specify whether the user cannot enter invalid data or just warn them about it.
After setting up the validation, try entering different values in the selected cells to test if it works as intended. At the start, we see the message that guides us.
Now let's add information about a new product we want to store in the inventory sheet.
We can enter product ID 16, the name Z220, the current quantity on hand is zero, and the supplier is “Beta Goods” from the drop-down list. The user cannot make mistakes when entering supplier data.
Ensure that valid entries are accepted while invalid ones are rejected with an appropriate error message.
Let’s assume we try to type the name of a new supplier, “Omega Ltd”, then we would see an error message. Ensure the error message is clear and guides the user on why the input is not accepted.
2. Let's practice!
Now that you have seen how to validate data using lists let's apply them in the following exercise.