How to Make a List in Excel (All Things You Need to Know)

In this Excel tutorial, you’ll learn how to
– Make a drop-down list in Excel (dependent, editable, and dynamic)
– Input a message and error alert in the list
– Make a unique list in Excel
– Make a numbered and bulleted list
– And remove a list from Excel

We are using Microsoft 365 in this article. The methods we’ll discuss here will be applicable in other versions of Excel.

Making a list is important for data entry and management. By creating a list, you can organize your data and enhance your workflow. You can use the list for inventory management by making a list of products. It is easy to select a value from a list instead of typing it.

In the following overview image, we select a range as the source in the Data Validation dialog to create a drop-down list.

⏷Ways of Making Drop-Down List in Excel
 ⏵Insert List Values Manually
 ⏵Insert List Values from a Range of Cells
 ⏵Apply Name Manager
 ⏵Combine Table Tool and INDIRECT Function
 ⏵Apply OFFSET Function
⏷Copy and Paste a Drop-Down List to Other Cells
 ⏵Use Keyboard Shortcut
 ⏵Apply Paste Special Command
⏷Make a Dependent Drop-Down List
⏷Make an Editable Drop-Down List
⏷Make a Dynamic Drop-Down List
 ⏵Use OFFSET and COUNTA Functions
 ⏵Use OFFSET and COUNTIF Functions
⏷Add or Remove Items from a Drop-Down List Without Opening Data Validation
 ⏵Add Items Using Insert Command from Context Menu
 ⏵Remove Items Using Delete Command from Context Menu
⏷Input a Message in a Drop-Down List
⏷Put an Error Alert in Excel Drop-Down List
⏷Protect Your Drop-Down List
⏷Make a Custom List in Excel
 ⏵Use Pre-Existing List
 ⏵Manually Create a List
⏷Make a Unique List
⏷Import a List in Excel
 ⏵Import a Custom List from Another Worksheet
 ⏵Import a Drop-Down List from Another Workbook
⏷Make a Numbered List in Excel
 ⏵Use Auto Fill Feature (Static Numbered List)
  ⏵Use Fill Handle Tool
  ⏵Use Fill series Option
 ⏵Use ROW Function
 ⏵Use ROW Function in an Excel Table (Dynamic Numbered List)
 ⏵Use ROWS and SEQUENCE Functions (Dynamic Numbered List)
⏷Make a Bulleted Number List
 ⏵Use Keyboard Shortcuts
 ⏵Use Symbol Command
 ⏵Use Custom Format
 ⏵Copying and Pasting from Word
 ⏵Use CHAR Function and Ampersand Operator
 ⏵Use Wingdings Font
⏷Remove a List from Excel
⏷Things to Remember
⏷Frequently Asked Questions
⏷Make List in Excel: Knowledge Hub

How Many Ways Are There to Make a Drop-Down List in Excel?

There are 5 ways in Excel to make a drop-down list. We’ll use the Data Validation feature to make the drop-down list in all of the methods. This feature allows us to control the type of data we enter into a cell. We can apply it in a single cell or a range of cells to create a drop-down list in Excel.

1. Insert List Values Manually in Data Validation to Make a Drop-Down List

• Select all the cells from C5:C12 and go to Data > Data Tools > Data Validation.

• In the Data Validation dialog box, select List under the Allow menu.
• Write the values manually separated by commas under the Source menu, like Call, Email, Discord, Whatsapp.
• Then press the OK button.

• The list is ready. Select any cell and press the Drop-Down button beside the cell, you’ll find the list. When you choose any item from the list, the item will be shown in the corresponding cell.

2. Insert List Values from a Range of Cells in Data Validation to Create a Drop-Down List

We’ll insert values from the range of cells B15:B18 to create a Drop-Down List.

• Select all the cells from C5:C12.
• Click on the Data Validation option under the Data Tools menu of the Data tab.

• In the Data Validation dialog box, select List under the Allow menu.
• In the Source box, type:

• Then press the OK button.

The list is ready to use.

3. Apply Name Manager in Data Validation to Make a Drop-Down List

Instead of directly putting the cell reference in the Data Validation dialog box, you can apply a range name through the Name Manager tool.

• Select all the cells B15:B18 and give them a name via Name Box. Here we name them Communication.

• Open the Data Validation dialog box from the Data tab by selecting cells C5:C12.

• Select List under the Allow menu and type the below name in the Source box:

• Then press the OK button.

The list is now available.

4. Combine Table Tool and INDIRECT Function in Data Validation

We’ll use the Table tool and the INDIRECT function to create a Drop-Down List. If your dataset is in a Table format, it is very useful. Because, when you add or remove any value from the Table, the drop-down list will update automatically.

• To insert a Table, select the cells B15:B18 and click on the Table option from the Insert. Or, you can just press Ctrl + T from the keyboard.

• Press OK in the Create Table dialog box.

• Select any cell on the Table and go to the Table Design tab.
• Under the Table Style Options menu, untick the options: Header Row and Banded Rows.

• Select cells C5:C12 and go to the Data Validation dialog box.

• Choose List from the Allow menu and write the following formula as the Source:

• Next, click the OK button.

You’ll see the list of the values from the Table in your desired place.

5. Apply OFFSET Function in Data Validation to Make a Drop-Down List

We want to make a drop-down list in cells C5:C12 with the items from cells B15:B18. We can use the OFFSET function for this.

• Select all the cells from C5:C12 and go to the Data Validation dialog box.
• Choose List from the Allow menu and write the following formula as the Source:

• Next, click the OK button.

The drop-down list is ready.

How to Copy and Paste a Drop-Down List to Other Cells in Excel

We want to copy the drop-down list in cells C5:C7 and paste it to cells C15:C17. We can do this by using keyboard shortcuts and the Paste Special command.

1. Simply Copy and Paste Using Keyboard Shortcut

• Select cells C5:C7 and press Ctrl + C to copy the list.
• Select cell C15 and press Ctrl + V to paste the list.

2. Use Paste Special Command

• Select cells C5:C7 and press Ctrl + C to copy the list.
• Select cell C15 and right-click.
• Click on the Paste Special option from the Context menu.

• The Paste Special dialog box will open.
• Choose Validation and click OK.

The drop-down list is pasted in cell C15.

How to Make a Dependent Drop-Down List in Excel

You can make a dependent drop-down list in Excel using the Name Manager and the INDIRECT function. Below we have a dataset having some food name and their different dishes. We want to make a list of food first. Then, we’ll make a dependent drop-down list of dishes based on the food items.

• Select all the cells B5:B7 and give them a name via Name Box. Here we name them Food.
• Similarly name the other ranges like this.
• Cells C5:C8 as Pizza, D5:D6 as Pancakes, and E5:E7 as Chinese.

• Select cell C10 and go to the Data Validation dialog box.

• Choose List from the Allow menu.
• Write the following formula as the Source:

• Next, click the OK button.

• The list of food is ready.

• Similarly, select cell E10 and go to the Data Validation dialog box.
• Choose List from the Allow menu.
• Write the following formula as the Source:

• Next, click the OK button.

The dependent drop-down list is ready. First, select Pizza from the first list. You’ll see only pizza-related dishes in the second list.

How to Make an Editable Drop-Down List in Excel

Whenever you make a Drop-Down list in Excel, it is uneditable. So, when we write the value Instagram in a cell, we find a warning message like that.

• To make it editable, go to the Data Validation dialog box by selecting all cells from C5:C12.

• Go to the Error Alert tab and untick the option Show error alert after invalid data is entered.
• Finally, press the OK button.

Now, you can edit your list.

How to Make a Dynamic Drop-Down List in Excel

When you make a list using some values from a range of cells, the list is fixed. We can also make it dynamic by applying 2 methods. The methods are using the OFFSET and COUNTA functions or the OFFSET and COUNTIF functions. The advantage is that when we enter a new value in the range, the list will update automatically.

We want to make a drop-down list in cells C5:C12 with the items from cells B15:B19. We keep the cell B19 empty. We’ll put a new item in that cell to see if the list is dynamic or not.

1. Use OFFSET and COUNTA Functions

• Select all the cells from C5:C12 and open the Data Validation dialog box.
• Choose List from the Allow menu and write the following formula as the Source:

• Next, click the OK button.

To check whether it is dynamic or not, we add a new value: Instagram in cell C19. We’ll see that the list gets updated automatically.

2. Use OFFSET and COUNTIF Functions

• Select all the cells from C5:C12 and open the Data Validation dialog box.
• Choose List from the Allow menu and write the following formula as the Source:

• Next, click the OK button.

To check whether it is dynamic or not, we add a new value: Instagram in cell C19. We’ll see that the list gets updated automatically.

Can You Add or Remove Items from a Drop-Down List without Opening Data Validation in Excel?

Yes, you can add or remove items from a drop-down list without opening the Data Validation dialog box. For this purpose, you have to use the Insert and Delete command from the Context menu.

Case 1. Add Items to Drop-Down List Using Insert Command from Context Menu

We have a drop-down list. The source is from cells B15:B18.

• Select cell B16 and right-click.
• Select the Insert option from the Context menu.

• Choose the option Shift cells down and press OK.

• Add item, Instagram in cell B16. This way, you can add the item to the list.

Case 2. Remove Items from Drop-Down List Using Delete Command from Context Menu

• Select cell B16 and right-click.
• Select the Delete option from the Context menu.

• Choose the option Shift cells up and press OK.

• This way, you can remove an item from the list.

How to Input a Message in an Excel Drop-Down List

• To show a message in the list you should go to the Input Message tab of the Data Validation dialog box.
• Tick the option Show input message when cell is selected.
• Write the Title of the message and the message separately and press OK.

Whenever you select a cell in the list, you can see the corresponding message.

How to Put an Error Alert in an Excel Drop-Down List

You can put an error alert message in the drop-down list through the Data Validation dialog box. So, when anyone tries to edit the list, he’ll get an error alert.

• Open the Error Alert tab of the Data Validation dialog box.
• Tick the option Show error alert after invalid data is entered.
• Write the Title of the message and the message separately and press OK.

• We write the value Mobile in a cell and find an error alert like that.

How to Protect Your Drop-Down List in Excel

We have the following drop-down list. The source range for this list is from cells B15:B18. Now, we’ll protect this drop-down list using the Protect Sheet option from the Review tab. After protecting sheet, no one can edit the drop-down list without unprotecting it.

• Go to Review tab >> click the Protect Sheet option.

• The Protect Sheet dialog box will appear.
• Put a password. We put ‘list’.
• We can tick or untick any option based on your need. We keep the default settings.
• Click OK.

• Again put the same password and click OK.

• The sheet is now protected.
• Let’s try to change Discord to Call in the list.

• The following warning message pops up. Click OK to close this.

Click the image for a detailed view

How to Make a Custom List in Excel

You can make a custom list in Excel in 2 ways. Either modify the pre-existing list in Excel or manually create a custom list.

1. Use Pre-Existing List

There are some pre-existing lists in Excel.

We can find them by going to File >> Options >> Advanced >> General >> Edit Custom Lists.

• Now put January in cell B5 >> use the Fill Handle tool. You’ll get a custom list automatically.

2. Manually Create a List As a Custom List

We can manually create a list as a custom list in Excel. Suppose, we will enter One in cell B5. And then we want to use the Fill Handle tool to create a list of numbers. To do this we have to create a custom list.

• To manually create a custom list click on the File tab.

• Click on the Options menu.

• In the Excel Options window, click on the Advanced menu.
• Click on the Edit Custom Lists button under the General section.

Click the image for a detailed view

• The Custom Lists dialog box will open.
• Select NEW LIST option from the Custom lists: menu >> type your list serially in the List entries: box >> click Add.

• The list will add on the Custom lists: menu.
• Click OK.

• Again, click the OK button.

Click the image for a detailed view

• Put One in cell B5 and use the Fill Handle tool. You’ll get the custom list.

How to Make a Unique List in Excel

We’ll use the SORT and UNIQUE functions to create a Unique List from the following names of clients.

• Type the following formula in cell D5:

• Select all the cells from F5:F8 and go to Data > Data Tools > Data Validation.

• In the Data Validation dialog box, select List under the Allow menu.
• In the Source box, type:

• Then press the OK button.

The unique list is ready to use.

Can You Import a List in Excel?

Yes, you can import a list from another worksheet or workbook in Excel. We have two cases to show how this works.

Case 1. Import a Custom List from Another Worksheet

We have a list of client names on another sheet of the same workbook titled “Another Worksheet” and we want to import this custom list from this worksheet to our original worksheet.

• Go to the original worksheet titled “Importing List” and click on the File tab.

• Click on the Options menu.

• In the Excel Options window, click on the Advanced menu and again click on the Edit Custom Lists button under the General section.

Click the image for a detailed view

• Type the following cell reference in the box and press the Import button:

• Click the OK button.

• Again, click the OK button.

Click the image for a detailed view

• Now, when you type the first name Jeffs and drag the Fill Handle tool, you’ll get the full list of names automatically.

Case 2. Import a Drop-Down List from Another Workbook

We have a list of communication methods in a different workbook titled “SourceFile”. We named it “CommList” using the Name Box feature. We’ll now import this list into our original workbook.

• In our original workbook, we go to Formulas >> Define Name to open the Name Manager.
• In the New Name dialog box, give a name such as Items >> type the following formula in the Refers to box and press OK.

Click the image for a detailed view

• Select cells C5:C12 >> open the Data Validation dialog box.

• Choose List from the Allow menu and write the following formula as the Source:

• Next, click OK.

You’ll find the list where you want it.

How to Make a Numbered List in Excel

We can make a numbered list in Excel by 4 methods.

Here we have the same dataset as before. We’ll put a numbered list in cells B5:B12 which will be serial numbers of entries.

1. Use Auto Fill Feature to Create a Static Numbered List

You can use the Auto Fill feature to create a static numbered list in 2 ways. You can use the Fill Handle tool or the Fill Series option.

1.1 Use Fill Handle Tool

• Type 1 and 2 serially in cells B5 and B6.
• Use the Fill Handle tool for the rest of the cells.

The numbered list is ready.

1.2 Use Fill Series Option

• Type 1 in cell B5.
• Use the Fill Handle tool for the rest of the cells.

• You’ll see 1 in all the cells. So, click on the Fill icon and select Fill Series.

The numbered list is ready.

2. Use ROW Function to Create a Numbered List

You can make the numbered list dynamic by using the ROW function.

• Put the following formula in cell B5:

• Use the Fill Handle tool for the rest of the cells.

The numbered list is ready.

3. Create a Dynamic Number List Using ROW Function in an Excel Table

You can put the ROW function in an Excel Table and make a dynamic numbered list.

• Select the whole dataset and press Ctrl + T to create a table.
• Click OK in the Create Table dialog box.

• Put the following formula in cell B5:

The numbered list is ready.

4. Make a Dynamic Numbered List Using ROWS and SEQUENCE Functions

We have the following dataset with a drop-down list in cell C14. We put the FILTER function in cell C17 to filter the clients names based on the selection in the drop-down list. The formula is:

We want to put a numbered list in cells B17:B20 and make it dynamic. So, we’ll use the ROWS and SEQUENCE functions.

• Put the following formula in cell B17:

The numbered list is ready.

If you change the value in cell C14, you’ll see that the numbered list will update.

How to Make a Bulleted List in Excel

We can make a bulleted list in Excel by 6 methods.

Here we have a dataset having clients’ names. We’ll make it a bulleted list.

1. Use Keyboard Shortcuts to Add Bullet Points

• Select cell A5 and press Alt + 7 or Alt + 0149 to insert a solid bullet point.
• Use the Fill Handle tool for the rest of the cells.

The bulleted list is ready.

2. Use Symbol Command to Add Bullet Points in Other Cells

• Select cell A5 and go to Insert >> Symbol.

• The Symbol window will open.
• Choose Unicode (hex) from the from: dropdown.
• Type 2022 as the Character code. You’ll find the desired bullet point.
• Click Insert.

• You’ll see the bullet point in cell A5. Click Close.

• Use the Fill Handle tool for the rest of the cells.

The bulleted list is ready.

Following is a list of character codes from Unicode (hex) and their symbols. You can use them in your list.

3. Use Custom Format to Add Bullet Points in Same Cells

You can add bullet points in the same cells before names. For this, use the Custom format from the Format Cells dialog box.

• Select all cells from B5:B12 and right-click.
• Click on the Format Cells options.

• The Format Cells dialog box will open.
• Select the Custom option and type the following custom format.

• Click OK.

The bulleted list is ready. Now they are in the same cell with names.

4. Create a Bulleted List by Copying and Pasting from Word

• Open Word document.
• Select the list and press Ctrl + C to copy.

• Open your Excel file and select cell B5.
• Press Ctrl + V to paste the list.

5. Use CHAR Function and Ampersand Operator

You can use the CHAR function and ampersand operator to make a separate bulleted list.

• Put the following formula in cell D5:

• Use the Fill Handle tool for the rest of the cells.

The bulleted list is ready.

6. Use Wingdings Font to Add Bullet Points

We can use the Wingdings font to add bullet points in other cells beside our dataset. It’ll make a bulleted list in Dataset.

• Type l in cell A5. Make sure it is in Calibri font.

• Choose Wingdings font from the Font drop-down. You’ll see that the character changes into a bullet point.

• Use the Fill Handle tool for the rest of the cells.

The bulleted list is ready.

Following is a list of Calibri font and their equivalent Wingdings font. You can use them in your list.

How to Remove a List from Excel

• To remove a list, open the Data Validation dialog box, select Clear All, and press the OK button.

Click the image for a detailed view

• If you select a cell now, there is no list available.

Which Things You Have to Remember?

• We are using Microsoft 365 for our article. But if you are using an older version of Excel, you won’t be able to use the UNIQUE, FILTER, and SEQUENCE functions.
• Try to use cells as a source instead of manually putting values. Because, when you change any values in the source cells, the list will update automatically.
• If you copy a cell over another cell that has a drop-down list, the drop-down list will lost. You’ll not get any warning or alert.
• The drop-down list can have a maximum of 32,767 values.
• You can not delete a drop-down list by using the Delete key from the keyboard. You have to go to the Data Validation dialog box to delete the list.

Frequently Asked Questions

1. How do I make a list from 1 to 100 in Excel

Answer: You can make a list from 1 to 100 very easily in Excel. Just put the starting value in a cell and the second value in the cell after. Select the 2 cells and use the Fill Handle tool to drag. You’ll easily get a list from 1 to 100.

2. Can Excel make a random list?

Answer: Yes, you can make a random list in Excel by combining functions like SORTBY, RANDARRAY, and COUNTA functions. Let’s say you have a list of values in cells A1:A10. In another column (let’s say column B), enter the following formula in cell B1:
=SORTBY(A1:A10,RANDARRAY(COUNTA(A1:A10)))
You will now have a random list of values in column B.

3. How do I make a list automatically in Excel

Answer: You can make a list automatically in Excel using the SEQUENCE function. This function is only available in Microsoft 365. Suppose you want to make a list of numbers from 1 to 10. Just put the following formula in any cell:
=SEQUENCE(10)
You will now have a random list of numbers from 1 to 10.

4. How do I create a yes/no drop-down in Excel

Answer: You can create a yes/no drop-down in Excel very easily. Select all the cells where you want to put the drop-down list. Go to Data > Data Tools > Data Validation. In the Data Validation dialog box, select List under the Allow menu. Write the values Yes and No manually separated by commas under the Source menu. Then press the OK button.

5. Do I need a formula to create drop-down lists?

Answer: No, you don’t need any formula to create drop-down lists in Excel. You have to use the Data Validation tool to create a drop-down list.

6. Is a drop-down list is same as data filtering?

Answer: No, a drop-down list is different from data filtering. We add a drop-down list to a cell or multiple cells in Excel to view a list of items. On the other hand, we add data filtering only to the headers of each column to filter data based on some categories.

Download Practice Workbook

Conclusion

In conclusion, you can now make a list in Excel by following the above methods. Excel provides various options to create lists, edit, and remove lists, making data entry and management more efficient. You can use some built-in tools and functions for this purpose. The tools include Data Validation, Name Manager, and Table. The functions include INDIRECT, SORT, UNIQUE, OFFSET, and COUNTA. These tools and functions make the task of creating and editing a list in Excel very quick and easy.

Make List in Excel: Knowledge Hub

• Generate List Based on Criteria
• Create a Unique List Based on Criteria
• Make a List within a Cell
• Make a Price List
• Create List from Range in Excel
• Make a To Do List
• Make a Numbered List
• Make a Bulleted List
• Make a Comma Separated List
• Create a Contact List
• How to Make Alphabetical List

<< Go Back to Excel Drop-Down List | Data Validation in Excel | Learn Excel