MinhTech.com

Yet another technology tutorial blog.

Excel Validation with Dropdown Lists

By • Mar 1st, 2009 • Category: Office

Here is how to setup Excel dropdown lists to improve data integrity during data entry.

Validation improves data integrity especially when you need entry to be consistent. For example, the value “United States of America” could also be entered as: United States, US, USA, America, and more. Consistency improves the results of many tasks such as sorting, grouping, and various aggregate functions.

Setup a Named Ranged:

Open Microsoft Excel, activate Sheet2, and create a list of colors. Select the cells, and then go to Insert > Name > Define (shortcut: ctrl + F3). In the Define Name dialog, let’s name the range “ListColors.”

Excel

Create the Validation Rule:

Activate Sheet1, select a range of cells for validation, and then go to Data > Validation. In the Data Validation dialog, select the Settings tab, and then change the Allow combobox to “List” and enter “=ListColors” for the Source. Make sure In-cell dropdown is checked.

Excel

Test It Out:

Each selected cell is now embedded with a combobox. Items appear in the list as they appear in the named range, including sort order. Users may key in a value or select an item from the dropdown. An error message appears if the user attempts to enter a value not listed in your named range.

Excel

Finally, Hide Your Named Ranges:

Activate Sheet2, and then go to Format > Sheet > Hide.

is a technology junkie.
Email this author | All posts by

Leave a Reply