One of my favourite Excel features is a data validation drop down list. In just a couple of minutes, you can make a list of items, then make that list appear in a worksheet cell. It’s like magic!
The drop down lists make it much easier to enter data, and they help prevent typos, or invalid entries.
Note: The DVSP tool is a free version of the popular DVMSP tool that I sold for several years. In the videos, screen shots and written steps, you might see “DVMSP”, instead of “DVSP”.
Video: How to Use DVSP Setup File
Next, you can watch this video to see the DVSP setup file steps, and there are written instructions in the Setup file.
Excel Skills Needed
To use the DVSP tool, you will need the following Excel skills, to get your file ready:
create a named range (for the list of items) — there are instructions and a video here: Create a Named Range
create drop down list(s) on the worksheet, based on a named range — there are instructions and a video here: Create a Drop Down List
In some workbooks, you might need to block duplicate entries in a column. For example, we don’t want 2 employees to have the same ID number. See how to set up a custom rule for that, with data validation. And keep reading, to see why COUNTIF can cause problems for you.
In some data entry lists, you only want each item selected once. For example, pick players for a baseball lineup, or assign employees for their weekly on-call assignment. To prevent entries from being selected twice, use this data validation trick, to hide the used items in an Excel drop down list.
In this data validation drop down list, the most recently ordered products are at the top. The rest of the product names are below those, in alphabetical order. There’s a worksheet cell where you can set the number of top items. This technique uses new functions, SORTBY and MAXIFS, which are available in Excel 365.
If an Excel drop down has a long list of items, it can take lots of scrolling to find the item you need. To make things easier, add single-letter headings in the list. You’ll be able to get to any starting letter quickly. See Roger Govier’s technique for setting that up, and an updated version of his macros.
Select a region name in one Excel drop down list. Then, in the next drop down list, select from a list of employees who work in that region. You’ve seen this dependent drop down trick before, but watch my latest video below, to see a new twist on this technique.
With Excel data validation, you can create drop down lists in worksheet cells. Show the same list all the time, or show a “dependent” drop down list, based on the value entered in another cell. This video shows how to set one up, and there are written steps too.
In Excel, you can use the drop down arrows in the table headings, to sort or filter the data. In this example, we’ll filter for dates that fall within a specific date range. The video shows two ways to accomplish that task.
Someone asked me how to make a data validation drop down that only shows the visible rows from a filtered list. I created a sample file that shows how you can do that, and here are the details on setting up a drop down from filtered Excel list.