Skip to main content

How to add a drop-down list to a cell in Excel 2007/2010

Drop-down lists are useful when you want to force the user to enter only specific values into a cell in an Excel sheet. The user is presented with a drop-down list and he has to choose any one value from it. If the user enters a different value, it will be rejected. For example, you may create a drop-down list where users select a day of the week or Yes/No. This little tutorial will show you how to add such a drop-down list to an Excel cell.

1. The first step will be to create the list. Add the list values in cells either vertically in a column or horizontally in a row. For instance, A1, A2, A3 etc or A1, B1, C1 etc.

excel-dropdown

2. Select the cell where you want to add the list, such as D1, in this example.

3. Click on the Data menu and choose Data Validation.

excel-dropdown2

4. Choose List from the “Allow” option’s drop-down list. Make sure “In-cell dropdown” option is checked.

excel-dropdown4

5. Click the Source control and drag to highlight the column A. Alternately, enter the reference (=$A$1:$A$7 or =$A:$A).

excel-dropdown3

6. Click OK, and you are done.

excel-dropdown5

You must have noticed that we still have the source list (column A) in the sheet and it doesn’t look good. But we can’t delete it because deleting it will remove the values from the drop down list. But we can move the list to another part on the Excel sheet, or to an entirely different sheet in the same workbook. So highlight the column and press Ctrl+X to cut it and then paste it (Ctrl+V) on Sheet 2, in any column – not necessarily column A.

excel-dropdown6

This way you can create as many drop-down list on sheet 1 and place all sources on sheet 2.

Comments

  1. rather surprised that you have provided such mundane detail as a tip here - we have come to expect more meaty stuff from you.

    ReplyDelete
  2. "This way you can create as many drop-down list on sheet 1 and place all sources on sheet 2". Only this part is not work. Kindly suggest me on my mail : [email protected]

    ReplyDelete

Post a Comment

Popular posts from this blog

How to Record CPU and Memory Usage Over Time in Windows?

Whenever the computer is lagging or some application is taking too long to respond, we usually fire up task manager and look under the Performance tab or under Processes to check on processor utilization or the amount of free memory available. The task manager is ideal for real-time analysis of CPU and memory utilization. It even displays a short history of CPU utilization in the form of a graph. You get a small time-window, about 30 seconds or so, depending on how large the viewing area is.

How to Schedule Changes to Your Facebook Page Cover Photo

Facebook’s current layout, the so called Timeline, features a prominent, large cover photo that some people are using in a lot of different creative ways. Timeline is also available for Facebook Pages that people can use to promote their website or business or event. Although you can change the cover photo as often as you like, it’s meant to be static – something which you design and leave it for at least a few weeks or months like a redesigned website. However, there are times when you may want to change the cover photo frequently and periodically to match event dates or some special promotion that you are running or plan to run. So, here is how you can do that.

Diagram 101: Different Types of Diagrams and When To Use Them

Diagrams are a great way to visualize information and convey meaning. The problem is that there’s too many different types of diagrams, so it can be hard to know which ones you should use in any given situation. To help you out, we’ve created this diagram that lays out the 7 most common types of diagrams and when they’re best used: