3 ways to make a drop-down list in Excel

The Drop-down list is widely used in Excel. Making a Drop-down list in Excel can be made from data validation, by using a combo box in the form control and in the VBA Control Toolbox. Let's use a concrete example to illustrate (download the example file in XLS format at the end of the article). If we have a payroll every month, each employee's salary varies according to the number of days of attendance per month.

We need to make a "personal payroll" to see how each person pays each month, and then you can make a drop-down list of employee names in which to view the monthly salary of the assigned employee.

Method one: Using data validation

With data validation, you can provide a drop-down arrow in a cell, and clicking the drop-down arrow pops up the Drop-down list. Because the employee names are in the monthly payroll and the person's name is not in the personal payroll, we must first define the name to reference when setting the data validation in personal payroll.

The name is defined by clicking the menu insert → name and defining in the Definition Name dialog box, where the name area B3:b14 in the January payroll table is defined as "name," as shown in the figure.

1. If the Drop-down list is placed in the C1 cell of the personal payroll, select cell C1, click data → effectivity on the menu, select the Settings tab, and select sequence in the Drop-down list under allow in the validation criteria area.

2. Enter "= name" in the text box under "source". Click OK.

Triple Recommendation: Click for free Download the latest version of WPS Office software "to see more computer tutorials

3. The payroll data is associated with the VLOOKUP function in the data area c3:h14. For example, the 3rd Act of January Wages, you can enter a formula in the C3 cell

=vlookup ($C, ' January salary '! $B $: $H $14,2,0) "

Enter formulas in cell D3:

=vlookup ($C, ' January salary '! $B $: $H $14,3,0)

Enter formulas in cell C4:

=vlookup ($C, ' February salary '! $B $: $H $14,2,0)

The rest of the cells, and so forth. This allows you to view the payroll for all months of the month by selecting the employee name by clicking the Drop-down arrow to the right of the C1 cell.

Method Two: Use form controls

1. Right-click on the menu bar, select Form from the pop-up menu, pop-up the Form floating toolbar, click the combo box control.