1. Formula-based validation
Welcome again, we’ll dive deeper in the applications of Data Validation in Excel.
In this video, we’ll explore how to build a robust and error-free product inventory system. We will be revisiting the OptiSupply company dataset introduced earlier.
ur first task is to ensure that each product code in the SKU_ID column is unique and not duplicated.
That means this product code column won't accept duplicate values. As mentioned earlier in the course, although this feature is not native in Data Validation in Excel, it can be applied with a custom formula based rule.
To start, Select the SKU_ID column. Then open the Data Validation menu in Excel. In the validation criteria choose 'Custom' from the dropdown list.
Then enter the formula =COUNTIF($B$2:$B$20, B2)=1 to enforce uniqueness of the column values.
This formula checks if the value in cell B2 appears exactly once within the specified range ($B$2:$B$20). If it does, the validation passes.
Notice we anchor the range ($B$2:$B$20) but leave the individual cell reference (B2) unanchored. This ensures Excel checks each SKU_ID individually as the validation moves down each row. If any of the value counts is more than 1, Data Validation will trigger an error and will stop or warn the user.
You can add a custom input and error message, but we can skip this part for now.
If we test it, trying to input a duplicate SKU_ID value, for example if we type Z999 in cell B17, Then Excel will block us from continuing and will show us an error message indicating this is not allowed and we cannot continue.
Now let's explore having two criteria for our Data Validation. Assume that products with an importance level "A" should be ordered in quantities only 100 or more each time.
So we need to have two conditions, both of which should return True.
To apply it, we first select the column H that has the importance of SKUs. Then go Data Validation .
Again from the Data Validation criteria drop down list, select custom formula and write the following formula: =NOT(AND(H2="A",G2<100))
This formula will return False if only a product of Type A is less than 100, otherwise it will return True.
Again note that we do not anchor the cell references using "$"
as we need the validation to be applied to each row of the data.
consider this error a warning for the user that should be resolved with the proper team later on.
We can see that row 9 does not meet the specified criteria.
2. Let's practice!
It is time to try it! Good luck