In the previous article, I told you how to add a drop-down list in an Excel cell. In this article, I will tell you how to create a drop-down list from an Excel table column.
1. How To Create A Drop-Down List From Excel Table Column.
1.1 Create The Source Data Table Column.
- Below is the example cell data column.
A 1 Excel 2 Word 3 PPT - Select the cell range A1:A3, then click the Home tab —> Format as Table drop-dow arrow in the Styles group, and select a table style to convert the column data to a table as below.
A 1 Column1↓ 2 Excel 3 Word 4 PPT - Select the above table, and click the Table Design tab ( this tab only appears when you select a table ), then get the table name from the Table Name text box in the Properties group.
1.2 Create A Drop-Down List From The Source Excel Table Column.
- Select cell C1, and click the Data tab —> Data Validation↓ —> Data Validation menu item to open the Data Validation dialog.
- Click the Settings tab, and select the item List from the Allow drop-down list.
- Input the formula =INDIRECT(“Table-name[Column1]”) in the Source input text box.
- Click the OK button to save the dialog settings.
- Now when you select cell C1, you can see a down arrow (↓) beside the cell.
- When you click the down arrow, it will list all the cell data in Column1 of the source table.
- You can right-click a cell in the source table, then click the menu item Insert —> Insert Rows Above to add a cell in the table column, and then input the cell data ( Numbers ) in it.
- Now when you click the down arrow (↓) of the drop-down list in cell C1, you can find the newly added table column cell data in the list.
C Excel↓
Word
PPT
Numbers
References