Have you ever been in a situation where you were building a template in Microsoft Excel 2003 and it would have been really useful to actually have a drop down box so that your users could choose from a preset list, rather than them simply typing in their own values? Well the cool part is that Microsoft Excel 2003 allows you to build your own drop down boxes in the application and in this article we will show you exactly how to do this. The first step in the process of us create a drop down box is to first create a new workbook. So, open Microsoft Excel 2003 and create your new workbook. On the first work sheet add the following headings -
Cell A1 - Title Cell B1 - First Name Cell C1 - Last Name
Now click on the second worksheet in your workbook. The name of the worksheet should be called Sheet2. In cell A1 type the word Title and then in the following cells -
Cell A2 - Mr Cell A3 - Miss Cell A4 - Mrs Cell A5 - Ms
What we now need to do is to select the cell range A2 to A5. We are then going to give this range of cell addresses a name. To do this simply select the Insert menu and then choose the Name option from the drop down menu and choose Define. The Define dialog box will open and in the Names in Workbook text box simply type in the name Title and press the OK button. What you have now done is to simply create a named range which you can work with.
Now, return to the first worksheet. We now have to go to cell A2 and define the drop down box. Click on cell A2 and then choose the Data menu. From the drop down menu choose Validation. This will open the Data Validation dialog box.
In the Allow drop down list, choose the List option. The next step is to define the name Title as the range. You can achieve this by typing in the following in the Source text box -
= Title
To complete the process, simply press the OK button.
Return to the cell A2 and you will now see a drop down box. If you click on the arrow you will see the results from your second sheet.
One of the problems you will notice is that the values in the drop down box are not in alphabetic order. To get them into the appropriate order simply go to Sheet 2 of your workbook and choose the range A1 to A5. Select the Data menu and then in the Sort dialog box make sure you click once on the option button - Header Row. Now press the OK button.
If you return to Sheet1 you will now find that the Title drop down box is in alphabetical order.
Drop down lists can be used for virtually an Microsoft Excel template. Remember one thing though, if you do not want your range for your list to be on the same worksheet, then you must name the range. Learn to master Microsoft Excel with our MS Excel Course. We also have available a MS Excel Shortcuts cheat sheet which will help you with your shortcuts.