Data Validation – Drop Down Lists

For those of you currently using Data Validation, you will know that it is a great Excel feature.  It allows you to restrict data values entered within cells, on your spreadsheets, by providing a drop-down menu to select limited and defined data from.  This not only keeps the integrity of the data intact but can also speed up data entry.

During some recent training, the issue of editing this drop-down list was raised.

So here are some useful tips…

  • When you select the source data for your list, selecting the whole column will allow you to edit and add to the list.
  • However, the column title and blank cells will appear, when you click on the drop-down arrow.
  • You could select all the cells in a column, omitting the title heading, by clicking in the first cell in your source data and pressing the down-arrow whilst holding the CTRL +SHIFT keys together. This will highlight all the cells with data in them.
  • Pressing the down-arrow for a second time will highlight all the cells in the column to the very last cell. Cell number 1048576! However, when you click on the drop-down arrow you will have blank cells at the bottom. (See image below)

  • Another option, is to only select the cells for your source data E.g. A1:A6. When you want to update the list source add the new data in the cells below. E.g A7 – Meat.
  • Select the new cell(s) and holding the SHIFT key, click on the outside edge of the cell(s) until you have the black four-arrowed cursor symbol     
  • Drag the selected cell(s) up to another position within in the list. (See image below)

  • The drop-down menu will update and include the new entries automatically and in A-Z order if you apply a sort on the source data.

Leave a Reply

Your email address will not be published. Required fields are marked *