How to auto-populate other cells when selecting values in Microsoft Excel drop-down list
By
SpreadCheaters
In this tutorial, we will learn how to auto-populate other cells when selecting values in Microsoft Excel drop-down list. Although there are various methods to auto-populate cells, the VLOOKUP function built-in Excel is the most frequently used. It empowers users to efficiently populate other cells based on the values selected from a drop-down list.
Suppose we have a dataset that displays the hierarchy of employees in a company, including their department, experience level, and office number in the sheet labeled “Data. Our goal is to generate a drop-down list that presents the various designations in “Sheet 4”, and once a designation is selected, all the related information pertaining to that designation’s employee is retrieved.
Autopopulating in Excel means automatically filling in data or information in one or more cells based on pre-defined rules, formulas, functions, or data entered in other cells. This feature can be very useful for saving time, reducing errors, and improving the accuracy and consistency of your data.
Step 1 – Choose an Empty Cell
– Choose an empty cell to create the drop-down list.
Step 2 – Locate and Perform a Click on the Data Validation Button
– Locate the Data Validation button in the “Data tools” section of the Data tab.
– Perform a click on the Data Validation button.
Step 3 – Choose the List Option from the Allow Drop-down Menu
– Choose the “List” option from the “Allow” drop-down menu.
Step 4 – Input the Source and Hit the OK Button
– Input the source for the drop-down list i.e. “Data!$A$1:$A$21”
– Hit the OK button to create the drop-down list.
Step 5 – Utilize the VLOOKUP Function to Auto-populate Cells
– Choose another empty cell in which the function will return the Employee’s name.
– Enter the VLOOKUP function.
– The structure of the function will be:
VLOOKUP(A2,Data!A1:E7,2,FALSE)
– The first parameter is the “lookup_value”, the lookup value must be in the first column of the table_array.
– The second parameter is the table_array and the third parameter represents the col_index_num i.e. the column from which the value is to be returned.
– The fourth parameter specifies the function to look for an exact match.
Step 6 – Now Utilize Autofill to Apply the VLOOKUP Function in Each Column
– Now utilize Autofill to apply the VLOOKUP function in each column i.e. the Department, Experience, and Office number.
– Whenever the lookup value is changed utilizing the drop-down list, all the cells are auto-populated.
Step 7 – Check If Other Cells Get Auto-populated when Selecting Values from the Drop-down List
– Now, select values from the drop-down list i.e. the Designation drop-down list, and check whether the cells showing the details are auto-populated.
– Perform a click on the drop-down arrow next to the cell under the “Designation” header.
– Choose the required value i.e. Designation.
