{"id":437,"date":"2022-11-02T15:00:47","date_gmt":"2022-11-02T15:00:47","guid":{"rendered":"https:\/\/www.bizinfograph.com\/resource\/?p=437"},"modified":"2024-04-27T21:17:04","modified_gmt":"2024-04-27T21:17:04","slug":"how-to-create-a-drop-down-list-in-excel","status":"publish","type":"post","link":"https:\/\/www.bizinfograph.com\/resource\/how-to-create-a-drop-down-list-in-excel\/","title":{"rendered":"How to create a drop-down list in Excel?"},"content":{"rendered":"

Drop-down list in Excel is a valuable feature that can greatly improve data entry and validation in your spreadsheets. By learning how to create a drop-down list in Excel, you can enhance data accuracy, simplify user interaction, and create user-friendly forms. Whether you’re managing inventory, conducting surveys, or organizing data, this skill allows you to take control of your Excel workbooks. Embrace the versatility of drop-down lists to excel in data management, making your spreadsheets more efficient and user-centric. It’s an essential tool for anyone looking to streamline data input and enhance data quality in Microsoft Excel.<\/p>\n

Create a drop-down list in Excel from Data Validation<\/h2>\n

We will create the Month name drop-down in cell D1. First, keep the cursor on cell D1 to show the drop-down list there.<\/p>\n

\"Drop-down<\/p>\n

From Data Tab select Data Validation<\/p>\n

\"Drop-down<\/p>\n

In the first method we will create drop-down list through writing the month name directly in the \u2018Source\u2019 box, see the below:<\/p>\n

From the Allow section select List from the down arrow and then write the month name separated by a comma in the Source box, then click OK.<\/p>\n

\"Drop-down<\/p>\n

Month names will appear in the drop-down arrow in cell D1, select the month from the list.<\/p>\n

\"Drop-down<\/p>\n

In the second method we will create drop down in Excel worksheet by referencing the source cells in the \u2018Source\u2019 box, see the below<\/p>\n

We will create Month name dropdown list excel in cell D1. First keep the cursor on cell D1 to show the drop-down menu (list items) there.<\/p>\n

From the Allow section select \u2018List\u2019 from the down arrow and then click on the Source box to refer the source data and then click OK, see below:<\/p>\n

\"Drop-down<\/p>\n

You will the see the drop-down arrow in cell D1, select the month from the list.<\/p>\n

\"Drop-down<\/p>\n

Add new item or delete item from the excel drop down list<\/strong><\/h2>\n

To add or remove items to drop-downs, follow the below steps.<\/p>\n

To Add new item in the drop-down list<\/strong><\/h3>\n

If you follow to write directly in the \u2018Source\u2019 Box then write new item after the existing items. Here we added new month Jul after the existing month names. The new month name appeared in the drop down list accordingly.<\/p>\n

\"create<\/p>\n

To delete item from the drop down list in Excel<\/strong><\/h3>\n

Just delete the item (here we delete Jan and Feb) from the Source box. You see the result in drop-down list in below image that Jan and Feb are no more in the drop-down list.<\/p>\n

\"create<\/p>\n

Add item through reference the source in the \u2018Source\u2019 Box<\/p>\n

First add the item in the reference source, see the below that we added new month name Jul at the bottom of the table<\/p>\n

\"Drop-down<\/p>\n

In the Source box select the source range from A1:A8 to add new month i.e., Jul in the drop-down list<\/p>\n

\"create<\/p>\n

If you use cell reference as source to create drop-down, to delete the item you just remove the cell\/cells from the reference source and drop-down will updated automatically.<\/p>\n

Say, we want to remove Jan and Feb from the drop-down list<\/p>\n

Select the Jan and Feb cells, then right click and select Delete. From the Delete pop up select \u2018Shift cells up\u2019. Jan and Feb month deleted from the table.<\/p>\n

\"Drop-down<\/p>\n

The drop down is update automatically<\/p>\n

\"Drop-down<\/p>\n

See the source reference in the Source Box, source reference adjusted to range A2:A6 before which was A2:A8<\/p>\n

\"Drop-down<\/p>\n

Dynamic drop-down in Excel – how to create drop down menu in Excel?
\n<\/strong><\/h2>\n

Select the cell (here D1) where you want to create the drop-down<\/p>\n

In Data Validation window write following OFFSET formula in Source<\/p>\n

=OFFSET ($A$1,0,0, COUNTA ($A: $A),1)<\/p>\n

And click OK.<\/p>\n

\"Drop-down<\/p>\n

How OFFSET function works in Excel?<\/strong><\/h3>\n

Syntax of OFFSET function: OFFSET (reference, rows, cols, [height], [width])<\/p>\n

Total 5 arguments are used in OFFSET function. In our example arguments are:<\/p>\n

Reference: A1 is the reference cell<\/p>\n

Rows: number of rows up or down. We move 0 rows up or down<\/p>\n

Cols: number of calls to left or right. We are using single col so 0 col<\/p>\n

Height: we use COUNTA function in col A:A to count the values in col A that are not empty.\u00a0 When you add one item in the list COUNT(A:A) considers it accordingly.<\/p>\n

Width: here width is 1<\/p>\n

Now if you add two more months in our monthly list say add Aug and Sep then drop-down will be adjusted dynamically.<\/p>\n

\"Drop-down<\/p>\n

 <\/p>\n

Aug and Sep are added in the month list which are reflected in the drop-down list.<\/p>\n

Now we delete Jun, Jul, Aug, and Sep from the month table, in the right-side drop-down list updated accordingly<\/p>\n

\"Drop-down<\/p>\n

How to use Excel Table to create dynamic drop down list?<\/strong><\/h3>\n

To create Excel Table, click any cell of Month list and press CTRL+T then Create Table pop up appears and click OK<\/p>\n

\"Drop-down<\/p>\n

Here our Excel Table name is \u2018Table1\u2019 and column name is \u2018Month\u2019<\/p>\n

Now use the Excel Table reference in Data Validation Source to make the dynamic drop-down. We will use INDIRECT function to create the reference.<\/p>\n

\"Drop-down<\/p>\n

Drop-down list looks like below<\/p>\n

\"Drop-down<\/p>\n

Now if you add July in the table, the drop-down will be adjusted dynamically.<\/p>\n

\"Drop-down<\/p>\n

If you delete any month form the table, you need to adjust the excel table accordingly.<\/p>\n

Drag the bottom right corner icon to last month\u2019s cell.<\/p>\n

\"Drop-down<\/p>\n

Use a drop-down list in Excel Workbook<\/strong><\/h2>\n
    \n
  1. Data Validation<\/strong>:\n