Avoiding Duplicates:
In this tip, I will show you how to avoid duplicates when entering data.

In column A, I have a list of products.
To avoid typing a product name repeatedly , we can use the “Data Validation” option with the COUNTIF function.
To do this, first select the cells, or the row where you want to avoid duplicates (1).
Then select the “Data” (2) tab and click the “Data Validation” (3) button.

In the “Data Validation” dialog box, select the “Settings” tab (4) and select the “Custom” option in the “Allow” box (5).
Then select the “Allow Blanks” check box.

Then type the formula = COUNTIF ($ A $ 2: $ A $ 10, A2)<=1 in the “Formula bar” (6).
Let me try to explain this.
The COUNTIF function counts the number of cells that satisfy a condition.
If the cell contents adhere to the condition, it is TRUE and if they do not, it is FALSE.
TRUE is equal to 1
FALSE is equal to 0 (zero).

Then we open the brackets and select (or type) the range A2: A10. This reference to the range is made absolute, so $ A $ 2: $ A $ 10.
We make this absolute so that if this formula is copied to the underlying cells, the range will still remains the same.
We type a “,” (comma).
We then type the condition. In this case, the first cell of the range, is cell A2. This cell reference is “relative”.
Why is this relative?
Because when the formula is copied to the underlying cells, the condition, must adapt to the underlying cell.

Next, we close the brackets), and type <=1 (Less than or equal to 1).
It will therefore be a 1 or a 0 (zero).
In other words, if the number is greater than 1, then it will display an error message.
TRUE = 1 and FALSE = 0, remember.

The error message that must be displayed, is typed in the “Error” (7) tab.
For the alert window of the error, type the “Title” (8) as “Error” (9).
Click the OK button.

If you type a product name that already exists (10), Excel will display a warning window (11) with the error message that this is not allowed.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats