Quick Links

• Using a Column in a Formatted Excel Table

• Using a Column in an Unformatted Dataset

Microsoft Excel's Data Validation tool enables you to add a drop-down list to a cell based on existing data in a column. However, how this functionality operates depends on whether the source data is part of a formatted Excel table. Let's explore some examples.

Using a Column in a Formatted Excel Table

Suppose you have this formatted Excel table called "Scores," which includes player names, nations, and scores, and you need to extract some summary data. Specifically, in cell H2, you want to create a drop-down list of all the nations listed in column B, and in cells I2 and J2, display the player names and average score, respectively, according to the nation selected.

To create a drop-down list, select the cell where you want it to be (in this case, cell I2), and in the Data tab on the ribbon, click "Data Validation" in the drop-down option with the same name.

Related ##### How to Add a Drop-Down List to a Cell in Excel

It saves you the trouble of typing in the same options 200 different times manually.

PostsThen, in the Allow field of the Settings tab, select "List."

The next step involves entering the options you want to appear in the drop-down list into the Source field. To do this, you can type the options manually, select the relevant cells on your spreadsheet, or use a formula.

Ideally, you would be able to type:

<code>=Scores[Nation]</code>

into the Source field, just as you would when referencing that column in a formula, where "Scores" represents the table name, and "Nation" represents the column header. However, unfortunately, this returns an error message, as the data validation tool only recognizes cell references, formulas, and named ranges as data sources, not table column headers.

Luckily, there are two ways to get around this.

Method 1: Reference the Cells in the Table

First, activate the "Source" field in the Data Validation dialog box so that the cursor is flashing, and hover over the relevant column header in row 1 until you see a small, black down arrow. When you do, click once to select all the data cells in that column.

How to Use the FILTER Function in Microsoft Excel There’s more than one way to filter your data.

PostsFinally, use the AVERAGEIF function in cell J2 to display the average score of players from this nation:

<code>=AVERAGEIF(Scores[Nation],H2,Scores[Total])</code>

Related ##### How to Use the AVERAGEIF and AVERAGEIFS Functions in Excel

Be selective about what to include in your average calculations.

Posts### Method 2: Name the Range

The second way to create a drop-down list from a column in a formatted table involves naming the source range.

Before launching the Data Validation dialog box, select all the cells in the table that contain the values you want to include in the drop-down list, type a name for the range in the name box in the top-left corner of the Excel window, and press Enter.

I Always Name Ranges in Excel, and You Should Too Tidy up your Excel workbook.

PostsNow, as with method 1, you can use the FILTER and AVERAGEIF functions to complete the data extraction.

However, this time, the formulas can be more straightforward, as you can reference the range you named "Nation" without referencing the table the range is in.

So, for FILTER in cell I2, it's:

<code>=FILTER(Scores[Player],Nation=H2)</code>

and for AVERAGEIF in cell J2, it's:

<code>=AVERAGEIF(Nation,H2,Scores[Total])</code>

Using a Column in an Unformatted Dataset

Certain types of data—like spilled arrays—cannot be formatted as an Excel table, so there may be times when you need to find a way to create a drop-down list from a column within an unformatted dataset.

After selecting the cell that will contain the drop-down list and clicking "Data Validation" in the Data tab on the ribbon, select "List" in the Allow field.

Next, in the Source field, use the INDIRECT and COUNTA functions together to tell Excel where to find the options for the drop-down list.

In this case, typing:

<code>=INDIRECT("B2:B"&COUNTA(B:B))</code>

includes all the values in column B from cell B2 to cell B22 as the source.

Let's break this source formula down to see how it works.

• =INDIRECT(: This tells Excel that you want the source to be determined using a dynamic reference.
• "B2:B": Specifically, the dynamic reference starts at cell B2, and ends at another cell in column B.
• &COUNTA(B:B)): This counts all the cells in column B that aren't blank, and adds the total to the reference. In this case, 22 cells in column B contain values, so this turns B2:B into B2:B22.

Related ##### How to Use the INDIRECT Function in Excel

Use a text string to create a reference.

Posts 2 So, if you add another row of data at the bottom, the COUNTA function will pick this up and inform the INDIRECT reference in the data validation rule that the source has expanded downwards by an extra row.

Using a formula to determine which values to include in the data validation source—rather than simply selecting the whole column—is considered best practice as it prevents the header row and blank rows from being included in the drop-down list.

You can double-check this by selecting the cell that hosts the drop-down list, and reopening the Data Validation dialog box. When you select the Source field, even though the formula hasn't changed, the dotted line on the spreadsheet confirms that it picks up the new values in the added row.

Now that your drop-down list is ready to go, use the INDIRECT and COUNTA functions alongside the FILTER function in cell I2 and the AVERAGEIF function in cell J2 to complete the spreadsheet.

---

Drop-down lists added through the Data Validation tool are incredibly powerful and versatile, and can be used in many scenarios in Excel. For example, you can use drop-down lists to make regular charts dynamic—a surefire way to impress your friends and coworkers.

The above is the detailed content of How to Create a Drop-Down List from a Column of Data in Excel. For more information, please follow other related articles on the PHP Chinese website!