42 commentsWe all know that is a very useful feature in Excel. You can use data validation to and limit the values user can enter. But, do you know that you can use data validation in a multitude of ways to prevent users from entering wrong data?Here is a practical application: Prevent users from entering duplicate values in a range of cells.For eg. You are making an invoice. Wouldn’t it be cool if Excel prompted you when you enter a duplicate line item so that increase the item quantity instead of repeating it.Here is a 3 step tutorial to do just that.Step 1: Identify the range of cells where you want only unique values to be enteredThis is simple. All you have to do is find the range where you want to control the user input. Lets say the range is B4:B11Step 2: Set up Data Validation to prevent duplicate entriesSelect the range (B4:B11) and go to data validation (Excel 2007: Data Ribbon Data Validation, or press ALT+AVV)Now, specify the validation type as “Custom”, this will allow us to use formulas to check for valid data.
In our case, we need check if a particular entry is duplicated in the range B4:B11. This can be easily done using.Go to the formula field and type the countif formula like this: =COUNTIF($B$4:$B$11,B4). I have 21 years of experience working as data entry assistant. I constantly read several blogs to keep myself up-to-date with the advances in data entry profession.
I really enjoyed this blog post. From my several years of experience, I agree with you 100% when you say, “ We all know that data validation is a very useful feature in Excel. You can use data validation to create a drop-down list in a cell and limit the values user can enter. ”Keep blogging.
- For my example to work, the data would need to be sorted by the first column and there would need to be a header row. You would need two more columns (C & D). First, add a formula that essentially says to concatenate the data in column B if data in column A is the same as the row above it, otherwise reset the concatenation.
- Sep 08, 2011 You can prevent duplicate entries from creeping into an Excel worksheet when you protect selected cells with Data Validation.
Finding duplicates in existing data is one of the important features of MS Excel. You can do it with just a few clicks through the ‘Conditional Formatting’ menu option. The ‘Conditional Formatting’ menu option can be found on the Home tab of Excel 2013 as shown below.
Prevent Duplicates When Entering Data Into Excel Worksheet
I will come here again.-data entry assistant.