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

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.

Want to master Microsoft Excel and take your work-from-home job prospects to the next level? Jump-start your career with our Premium A-to-Z Microsoft Excel Training Bundle from the new Gadget Hacks Shop and get lifetime access to more than 40 hours of Basic to Advanced instruction on functions, formula, tools, and more.

Buy Now (97% off) >

Other worthwhile deals to check out:

Join the Next Reality AR Community

Get the latest in AR — delivered straight to your inbox.

Be the First to Comment

Share Your Thoughts

  • Hot
  • Latest