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.
Other worthwhile deals to check out:
- 97% off The Ultimate 2021 White Hat Hacker Certification Bundle
- 98% off The 2021 Accounting Mastery Bootcamp Bundle
- 99% off The 2021 All-in-One Data Scientist Mega Bundle
- 59% off XSplit VCam: Lifetime Subscription (Windows)
- 98% off The 2021 Premium Learn To Code Certification Bundle
- 62% off MindMaster Mind Mapping Software: Perpetual License
- 41% off NetSpot Home Wi-Fi Analyzer: Lifetime Upgrades