Excel drop down lists and data validation tutorial

19 Jun 2023   | Computer Stuff · Excel tips and tricks · How to Microsoft Office

Tags: · · · · · · · · · · · · · · · · · · · · · · ·

In this Excel drop down lists tutorial for beginner’s video I demonstrate this extremely useful feature of Excel that everyone should know about. The drop down feature in MS Excel is most useful when multiple users are entering data into a shared Excel spread sheet. The drop down list ensures that all users must enter only the approved parameters for the data or terminology thus eliminating confusion of various terms relating to the same input item. This is important if this data is to be searched or if other formulas and functions will be applied to this data set later.

Here are step-by-step instructions on how to create and use drop down lists and data validation in MS Excel:-

Excel static drop down list steps

  1. Select first cell for the drop down list
  2. On top ribbon Select Data-Data Validation
  3. In the pop-up window select Settings, then Allow-List (from drop down) make sure ignore blank and in-cell dropdown or ticked.
  4. In the source box type the allowed phrases separated by commas, click Ok.
  5. The selected cell now has a drop down arrow which when click displays the options, click on the desired option. You can also just type in an allowed value NB spelling and case must match exactly.
  6. To apply the same drop down options to other cells in the column hover until see+ on bottom right of top cell then drag down as far as needed. This will repeat validation settings to all cells highlighted but it will also copy any data that was in the cell, the data can be deleted or to get around that select all the first cells in in all the columns then drag downs as many rows as needed for input, CTRL-C, right click, select paste-special and tick data validation. Now only data validation is copied not data. Any changes to the drop down list must be made the source box.
  7. To remove/edit data validation (cannot use delete), select cell, Data-Data Validation-Clear All. Similarly to edit a data validation simply select the cells and reopen data validation window and make changes.

Excel dynamic drop down list steps

  1. Select first cell in the column that will get the drop down list
  2. Go to Data-Data Validation to open the window
  3. Select, Settings-Allow-List
  4. Now click in the Source box then go to your source list and select the cells in the source list. Will see marching ants. This can be on a separate work sheet.
  5. Changes to the drop down list can be made in the original source list but additions to the bottom of the list will not be picked up, You can get around this by SHFT dragging the item to the middle of the list or in most cases Data-Sort-AZ or Sort-ZA
  6. Return to input work sheet. You will now see the source box will be filled with a formula describing the source list, for example;  =’Source Template’!$B$3:$B$11, click OK
  7. The first cell will now have a drop down arrow, to apply this to more cell in the column, hover until see + on bottom right corner of cell and then drag down the column. This will repeat validation settings to all cells highlighted but it will also copy any data that was in the cell, the data can be deleted or to get around that select all the first cells in in all the columns then drag downs as many rows as needed for input, CTRL-C, right click, select paste-special and tick data validation. Now only data validation is copied not data.

Addition features of drop down lists – Input and error messages

  1. To add an input prompt; Data-Data Validation-input message. Type in a Title and or an input message to appear next to the drop down arrow.
  2. To add an Error message: Data-Data Validation-Error Alert. Select an error alert symbol and type in an error message.

Here is a video tutorial:

Here are some time stamps for browsing the content of this video:

00:00 Beginning

01:34 Create an Excel dropdown list by typing in source data for the list

03:58 Populate an Excel dropdown arrow and data validation down a column

05:37 How to edit Excel drop down and data validation

06:45 Create an Excel drop down arrow by selecting source data from another data set

09:40 How to add an input message to an MS Excel drop down arrow

11:13 How to customise an Excel error message of a data validation criteria

12:59 Options for Excel data validation criteria

Here is a link to the blog article:

Comments are most welcome, if you decide to try this please send us some feedback☺️ Why not visit our channel and browse through our other videos? http://www.youtube.com/c/AnnErasmus

Here are some more tutorials on Excel tips and tricks:

Short Disclaimer/disclosure: My videos/articles are for informational and educational purposes only, you should consult with a professional before attempting anything seen or done in my videos/articles. Please read full disclaimer here: https://basiclowdown.com/category/disclaimer-disclosure/