How do you create a drop-down list?

Another frequent question pertaining to the usage of Microsoft Excel is updating a cell within your worksheet of your workbook to function as a drop-down list of specific selections. The following focuses only on a simple response / scenario.  Access “Insert or delete a drop-down list” (applies to Excel 2007) or “Create a drop-down list” (applies to Excel 2013) for further details.

Let’s assume that you’re preparing an Excel file to send to others to fill out, with a listing of their learning content – they’re instructed to return the content with their details so you can add these into your team or firm-wide listing (e.g. LMS) of learning content.

Two of the cells that they should fill out are ‘Category’ and ‘Authoring (Delivery)’.  Instead of sending them a manual list of available selections they should enter into specific cells of the worksheet you’re sending to them, why not create a drop-down list from specific cells, to make selections you’re expecting to see, expecting them to respond with?

To begin with, create your worksheet with the fields, columns, rows, look and feel, etc.  Then create a separate worksheet (e.g. titled “Master Data) with the content representing the selections you expect and allow, for ‘Category’ and ‘Authoring (Delivery)’ as aforementioned.  If desired, right-click on the newly created Master Data sheet and choose to Hide the sheet (so those entering content into the data input sheet, don’t see this Master Data sheet).

MS-Excel: How do you create a drop-down list (A)

MS-Excel: How do you create a drop-down list (B)

Return to the data input worksheet and identify, per cell, the area within the workbook and its worksheet containing valid selections: highlight the specific cell and from Data Tools, click on Data Validation.

MS-Excel: How do you create a drop-down list (C)

Then, choose List and Source (from the Master Data worksheet) where the valid selections are contained.

MS-Excel: How do you create a drop-down list (D)

Now, when you or others access these drop-down list-based cells from the data input worksheet (in this example, B1 and B7), a drop-down list of available and valid selections (from the Master Data worksheet) is made available and only these selections can be chosen.

MS-Excel: How do you create a drop-down list (E)

Download the MS-Excel file representing this example. And contact us with any questions.

Download "How do you create a drop-down list within a Microsoft Excel worksheet?"