How To: Make a list in Excel which only contains unique values

This tutorial describes how to make a list with unique values in Excel.This procedure makes the defined cells in a column (called as list) NOT to accept repeated values, which makes the list more simple, readable, easy to manipulate when preparing a worksheet. Things to remember: 1. This works only on MS-Excel.2. Every cell has unique name of the form CR, where C is a set of alphabets (column name) and R is an integer (row number ). E.g. A12, C35, AX105, etc. To know the cell name, select a cell and you can see it from the highlighted column and row. How to make the list unique: 1. Decide which cells make the list. Let us assume our list is from cells B5 to B20.2. Place the cursor at B5, i.e., the first cell of the list.3. Press Alt + D + L to open the "Data Validation" window.4. In the settings tab, for "Allow" field, set the value "Custom".5. Under "Formula" field, type "=COUNTIF($B$5:$B$20,B5)=1" (without quotes). Note that we preceded the column and row name with $. Hence the B5 and B20 has changed $B$5 and $B$20 respectively in the given formula. The same rule applies to any column , row name.6. Press "OK".7. Make sure the cursor is at the beginning of the list (B5) and Press Ctrl+C to copy the cell.8. Now select the entire list (from B5 to B20) and press Ctrl+V.9. To verify, by placing the cursor in any of the cells in the list, press Alt+D+L. You could see the formula set. This would be empty when the same is done on cells apart from the list. Now each value in the selected cells must be unique. To test, enter '1' in two different cells in the list. You must not be able to enter the value '1' a second time within the list.