How to increase number of items in drop down list of Excel

I have installed the Microsoft Office 2007 on my Windows XP laptop and I want to have some more choices in an Excel. There is a drop down list in the Excel sheet where I am getting 8 default options. I want some more options at the place of these default one. Is there anyway by which I can add up some more options there ?

Microsoft Excel provides several controls for dialog sheets. You can use these controls on worksheets to help select data. For example, drop-down boxes, list boxes, spinners, and scroll bars are useful for selecting items from a list. By adding a control to a worksheet and linking it to a cell, you can return a numeric value for the current position of the control. You can use that numeric value in conjunction with the INDEX function to select different items from the list.

In order to use the forms controls in Microsoft Office Excel 2007, You have to first enable the Developer tab for which you have to start the Microsoft Office first and then click on the Excel Option. Here you would be having a Popular Tab where you have to click for the Show Developer tab in the Ribbon check box, and then click OK. Once you have done this then you may start with applying the formula as per your needs.

Here I have provided the example by following which you would be able to set up the list, the cell link, and the index in the MS Excel 2007. To start with it. You have to open the new worksheet and then type the number of items in the range.
1. For Eg: I have typed the following items in the range H1:H20:

• H1 : Item 01
• H2 : Item 02
• H3 : Item 03
• H4 : Item 04
• H5 : Item 05
• H6 : Item 06
• H7 : Item 07
• H8 : Item 08
• H9 : Item 09
• H10: Item 10
• H11: Item 11
• H12: Item 12
• H13: Item 13
• H14: Item 14
• H15: Item 15
• H16: Item 16
• H17: Item 17
• H18: Item 18
• H19: Item 19
• H20: Item 20

Now in In cell A1, type the following formula:

=INDEX(H1:H20,G1,0)

For drop down list on an Excel spreadsheet you have to select the list in sheet1 + few cells (if you add the products). Now go to the "Insert" menu "name" set. Give a name to your list for example product. Go to the Sheet2 in A1 then click on the "Menu" and then "data validation". Roll area "allow" then choose "list". Click in the "source" then pat = product (the name you gave to your list). You have a dropdown list containing all your products. Now check that it really works or not.

To create a dropdown list from a range of cells, use the built-in data validation in the Data Tools group on the Data tab.
1. To create a list of valid entries for the dropdown list, type the entries in a single column or row does not contain empty cells. For example:
2. Note You can sort the data in the order you want them to appear in the dropdown.
3. If you want to use another worksheet, type the list in the spreadsheet, then set a name for the list.
4. Select the cell in which to create the list box.
5. Data tab in the Data Tools group, click Data Validation, Box Data Validation dialog appears.
6. Click the Settings tab.
7. In the Allow box, click List.
8. To specify the location from the list of valid entries, do one of the following:

• If the list is in the active worksheet, enter a reference to your list in the Source box.
• If the list is in another worksheet, enter the name that you defined for your list in the Source box.

9. In either case, make sure that the reference or name is preceded by an equal sign (=). Enter, for example = ServicesValides.
10. Make sure the checkbox list dropdown in the cell is activated.
Indicating that the cell can be empty, select or deselect the Ignore blank check.
11. Display possibly an input message when cell is being one click.
12. Indicate how Microsoft Office Excel to respond when invalid data.

You have to create a long list or must be changed regularly. Following procedure will allow you to create a list on a new sheet that you can modify to your taste. Important: If you want to edit this list, add elements inside and not the end.

*First step: Name the List

• Select the data list;
• Go to the Formulas tab> Set a name;
• In the "New Name", name;
• Important: Begin with a letter and do not put spaces.
• Click OK.

If you want to modify the list later, you must insert data inside the beach and not at the end of it.

*Step Two: Create your combo

• Select the cell where the drop-down menu will appear
• Go to the tab Data> Validation;
• In the "Options" under "Allow", select "List";
• In the text box "Source" box, type the equal sign followed by the name of your list, Example: = Nameofemployee
• Then click OK.

I hope this helps, if you have any questions, please!

You should always keep the following things in your mind.

1. The width of the combo box is determined by the width of the cell in which data validation is performed. You may be required to adjust the width of the cell to prevent the valid entries larger than the drop-down list are truncated.
2. A combo can accommodate up to 32,767 entries.
3. If the validation list is in another worksheet and you want to prevent users from seeing or making changes, consider hiding and protecting that worksheet.

You may have noticed, it is impossible to point the "source" field to another Excel sheet, which forces us to place the items in the list box on the worksheet page. This system is unsightly and gives everything to everyone to change the content at least to use the device quite heavy lock cells. To circumvent this problem..follow the given procedure.

1. Second sheet in the workbook, enter your combo;
2. Assign it a name: Enter the name in the "Name", over the headers of columns to the left of the formula bar and push the enter key;
3. Select the first worksheet using the menu "Window"
4. Select the cells that contain drop-down list (see " Implementation ");
5. Select the menu "Data" option "Data Validation";
6. Click the Options tab, then in the "Allow", select "List";
7. In the "Source" simply enter the name of your list before the equal sign (eg = mylist) and confirm!