Lists in Excel or Google Sheets

Creating drop-downs or lists in Excel or Google Sheets is fairly simple. It allows you to control the kind of data that users can input, therefore bringing consistency.

It also makes it easier to enter repeated data in a sheet.

Lists in Excel or Google Sheets

Creating drop-downs or lists in Excel or Google Sheets is fairly simple. It allows you to control the kind of data that users can input, therefore bringing consistency.

It also makes it easier to enter repeated data in a sheet.

A desk with a keyboard, phone, and a chart

The implementation of lists is similar in Excel and Google Sheets. Wherever there is a difference it has been called out specifically.

In the case of a similar implementation, we have used images from Excel. However, wherever there is a significant difference, we will use images from both Excel and Google Sheets.

You can create a dropdown using multiple methods.

Method 1: Entering List Items

EXCEL

Select the cells where you want the dropdown to appear
Go to the Data tab and click on Data Validation
In the ‘Data Validation’ dialog box and under the ‘Settings’ tab, select the ‘List’ option for ‘Allow’
Type the options in 'Source', separating them with a comma. In the image below, the options mentioned are Not Started,In Progress,Completed
Click OK

Enter the values in Source to define the contents of a list

GOOGLE SHEETS

Select the cells where you want the dropdown to appear
Go to the Data menu and click on Data Validation
In the ‘Data Validation’ dialog box and under Criteria, select 'List of items'
Type the options in the text box adjacent to it. In the image below, the options mentioned are Not Started,In Progress,Completed
Click Save

Enter the values in the text box adjacent to List of items to define the contents of a list

Method 2: Referencing a Range

EXCEL

Select the cells where you want the dropdown to appear
Go to the Data tab and click on Data Validation
In the 'Data Validation' dialog box and under the 'Settings' tab, select the 'List' option for 'Allow'
In the 'Source' text box, select the range where the list items are defined. In the image below we have given reference of the range $I$6:$I$8
Click OK

Enter the range in Source to define the contents of a list

GOOGLE SHEETS

Select the cells where you want the dropdown to appear
Go to the Data menu and click on Data Validation
In the ‘Data Validation' dialog box and under Criteria, select 'List from a range'
Specify the range that contains the list items in the text box adjacent to it. In the image below we have given the reference of the range C44:C48
Click Save

Enter the range in the text box adjacent to List from a range to define the contents of a list

Method 3: Using Defined Names

EXCEL

Select the cells where you want the dropdown to appear
Go to the Data tab and click on Data Validation
In the ‘Data Validation’ dialog box and under the 'Settings' tab, select the 'List' option for 'Allow'
In the 'Source' text box, give a reference to the defined name for the range. In the image below, the defined name mentioned is =definednameforlist
Click OK

Enter the defined name range in Source to define the contents of a list

All Excel & Google Sheets Resources

Lists in Excel or Google Sheets

Lists in Excel or Google Sheets

Method 2: Referencing a Range

Method 3: Using Defined Names

CONTENT

COMPANY

LEGAL

CONTENT

COMPANY

LEGAL