Google Sheets Drop Down List - Data Validation Tutorial, Dynamic Lists from Ranges

Introduction

This tutorial will guide you through creating dynamic drop-down lists in Google Sheets using data validation. Drop-down lists allow users to select options from a predefined list, helping to maintain data integrity and reduce errors.

Step 1: Create a Basic Drop-Down List

1. Select the cell where you want the drop-down list (e.g., B5).
2. Go to the menu and click on Data.
3. Select Data validation.
4. In the Criteria section, choose List of items.
5. Enter your options separated by commas (e.g., Product A, Product B, Product C).
6. Choose whether to show a warning or reject invalid input (select Reject input).
7. Optionally, add a help message to guide users.
8. Click Save.

Now, clicking on the cell will display the drop-down list with your specified options.

Step 2: Create a Drop-Down List from a Range

1. Select the cell for the drop-down list.
2. Navigate to Data and select Data validation.
3. This time, choose List from a range.
4. Click on the Select data range option and select your data from another sheet (e.g., from the Data Sample tab, select B2:B14).
5. Ensure Show dropdown list in cell is checked and select Reject input.
6. Click Save.

Your drop-down list will now reflect the values from the specified range, updating automatically as you change the data in that range.

Step 3: Make a Dynamic Drop-Down List for Unique Items

1. Create a new tab and name it Unique.
2. In the first cell of this tab, enter the following formula to retrieve unique items:

   =UNIQUE(Data Sample!A2:A)
   

3. Go back to the original tab where you want the drop-down.
4. Select the cell and open Data validation.
5. Choose List from a range and select the unique items from the Unique tab.
6. Make sure to select a larger range to accommodate future additions (e.g., A2:A100).
7. Set the validation to Reject input and click Save.

Now, your drop-down list will only show unique entries from the specified range, ensuring that users can select each option only once.

Step 4: Using the Drop-Down for Dynamic Calculations

1. In the cell where you want to display calculations (like average price), enter the formula:

   =AVERAGEIF(Data Sample!A:A, SelectedCell, Data Sample!B:B)
   

   Replace SelectedCell with the cell reference of your drop-down.
2. This will calculate the average based on the selected item from your drop-down list.

As you select different items from the drop-down, the average price will update automatically.

Conclusion

You've now learned how to create various types of drop-down lists in Google Sheets, including basic lists, lists from ranges, and dynamic unique lists. These tools can enhance your spreadsheets by improving data entry accuracy and facilitating calculations based on user selections. Next, consider exploring additional data validation features or integrating these lists into more complex calculations for your projects.