✦Template Printable Ideas Insights

Mastering Google Sheets: Creating Drop-Down Lists

By

Ever wished to organize a plethora of data in Google Sheets in a user-friendly, drop-down manner? A dropdown list, or a dependent list, can drastically enhance the usability of your spreadsheet. Here's a step-by-step guide on how to create one in Google Sheets.

Let's assume you're creating a sales report with a region-dependent sales target. A dropdown list for regions will make the task more manageable and efficient.

Creating the Dropdown List

A dropdown list is essentially a Data Validation list. So, let's start by creating that.

How to Color a Drop-Down List in Google Sheets
How to Color a Drop-Down List in Google Sheets
the words how to create a drop down list in google sheets on a green background
the words how to create a drop down list in google sheets on a green background
How to Add a Drop-Down List in Google Sheets and Excel - Make Tech Easier
How to Add a Drop-Down List in Google Sheets and Excel - Make Tech Easier

Here's the first step: Select the cells (A1 to A10, for instance) where you want the dropdown lists to appear. Then, click on 'Data' in the menu bar, and select 'Data validation'.

Setting Up the List

In the 'Data validation' box, under the 'Settings' tab, choose 'List of items'. Now, in the 'List of items' box, input your desired list, one item per line.

How to Add a Drop Down List in Google Sheets (Step-by-Step)
How to Add a Drop Down List in Google Sheets (Step-by-Step)
Dropdown Columns in Google Sheets
Dropdown Columns in Google Sheets
Quick tutorial: How to create dropdowns in Google Sheets (the easy way!)
Quick tutorial: How to create dropdowns in Google Sheets (the easy way!)

Example: North, South, East, West

Formatting the List

Choose 'Show dropdown list' in the 'Input message' section if you want your users to see a dropdown arrow in the cell. To make the dropdown list appear like a regular input box, select 'None' or leave both options unchecked.

Pro Tip: Use 'Reject input' to create an automatic alert for users if they try to input anything that isn't in your list.

How to Create a Google Dashboard
How to Create a Google Dashboard
Google Sheets Data Validation – How to create dependent drop down list in Google Sheets
Google Sheets Data Validation – How to create dependent drop down list in Google Sheets
Master Google Sheets Dynamic Named Ranges
Master Google Sheets Dynamic Named Ranges
How To Create Checklists with Google Sheets
How To Create Checklists with Google Sheets
Google Sheets - Drop Down List, 2 Dependent Dropdown Lists
Google Sheets - Drop Down List, 2 Dependent Dropdown Lists
Google Sheets Checklists
Google Sheets Checklists

Using the Dropdown List

Now that you've set up the dropdown list, let's see it in action. Each cell in your selected range now has a dropdown list containing the items you've added.

Select a cell, click on the dropdown arrow, and choose an item. Suddenly, creating a thorough, organized spreadsheet just got a lot easier!

自己修改数据

需要为列表添加或更改项时,单击‘Data’并在弹出的菜单中选择‘Data validation’。在页签的‘Data validation rules’区域,如果你想添加一个新项目,就键入并从‘List of items’dropdown中选择它。要更改一个项目,在‘Data validation rules’区域,选择要更改的项目,并键入新的文本。

Google Sheets - Create Multiple Dependent Drop-Down Lists
Google Sheets - Create Multiple Dependent Drop-Down Lists
How To Make A Checklist In Google Sheets For Daily Tasks
How To Make A Checklist In Google Sheets For Daily Tasks
Google Sheets - Drop Down List, 2 Dependent Dropdown Lists
Google Sheets - Drop Down List, 2 Dependent Dropdown Lists

iker验证

可以设置列表项目之外的输入被拒absolutely始博。要这样做,在‘Criteria’区域,选择‘Custom formula ARIA Compliance Issues interrupted ratiois’then enter a formula e.g., "=COUNTIF($A$1:$A$10, A1)=1" to reject any input that isn't in your list.

And there you have it! You've just learned how to create and manage dropdown lists in Google Sheets. This powerful tool can save you time, reduce errors, and bring your spreadsheets to life. Keep experimenting with Google Sheets to discover more of its great features!