1. Select the cell for which the dropdown menu is to be applied.
2. once the cell is selected goto DATA menu select VALIDATION.Data validation dialog box will be displayed.
3. Select SETTINGS tab from the dialog box appeared.In the ALLOW field select LIST. In the SOURCE field fill in the values to be displayed in drop down menu.Separate the values to be listed in drop down menu by COMMAS. Even SPACE can be used instead of commas
4. Make sure the IN-CELL DROPDOWN checkbox is being CHECKED. After finishing the above said process click OK button.
5. Now you can see your selected cell becomes a dropdown menu with the values you specified.
6. Now to specify the range of values you have listed in cell present in excel sheet, follow the same process upto step 3. In step 3 in SOURCE field, click and drag the cells in which the values are displayed, in the SOURCE field the RANGE address will be dispalyed by itself and then click OK.
7.Once you change the values in the range, its corresponding values are also changed in the respective dropdown menu.
8. To add the values in dropdown menu from other EXCEL SHEETS, first click and select the values which is to be listed in dropdown in another excel sheet.
9. To name the range of values goto INSERT menu, select NAME->DEFINE and specify the name you neeeded for the range of values you selected and click OK.
10. Then goto to the cell for which dropdown menu to be applied.then click on DATA->VALIDATION->SETTINGS(tab)->ALLOW field select LIST, in SOURCE field type = and name of the range you have specified in the other excel sheet and then click OK. The corresponding range of values are listed in the respective cell.
Want to master Microsoft Excel and take your work-from-home job prospects to the next level? Jump-start your career with our Premium A-to-Z Microsoft Excel Training Bundle from the new Gadget Hacks Shop and get lifetime access to more than 40 hours of Basic to Advanced instruction on functions, formula, tools, and more.