Microsoft Excel Tables (Part 4)

It is very common to limit the choices for a selection with a simple drop-down list. We often notice drop-down list in web forms, surveys as well as polls because it helps to shortlist items. It is also possible to create a drop-down list in Excel as well. I believe many of us use drop-down lists from time to time and you may have face difficulties when you add or remove an item from the list. Consider the following as example:

Static Drop-down List without using Excel Table

Suppose, you have a list of brands of computer hardware in cells A2:A6. Now you want to create a drop-down list so that user can only select a brand from then list rather than manually input brand name. To create a drop-down in cell E1 follow the following steps:

  • Select cell E1. (you can choose any cell you want)
  • Navigate to Data tab in the Ribbon.
  • Choose Data Validation from the Data Tools area.
  • Choose List from the Allow option's drop-down list.
  • Click the Source control and drag to highlight the cells A2:A6. Alternately, simply enter the reference (=$A$2:$A$6).
  • Make sure the In-Cell Drop-down option is checked. If you unchecked this option, Excel still forces users to enter only list values (A2:A6), but it won't present a drop-down list.
  • Click OK.
data table 1 data table 2 data table 3

Now click on E1 cell and you can see the drop-down arrow right next to the cell. Clicking on the arrow will show you the list of items as shown in the below image.

data table 4

Everything looks fine. However, the main problem is when you want to add a new brand name or even delete a brand. Lets add a new brand name "Acer" in cell A7, but as you can see in the image below, that the drop-down list does not show the brand name.

data table 5

This is because, the source in the drop-down is upto cell A6 (as shown below).

data table 6

Therefore, every time you add a new brand name, you need to update the source in the drop-down list.

Dynamic Drop-down List using Excel Table

This is when Excel Tables comes in. The structured reference, as discussed in my last article, makes the drop-down list dynamic. Lets take a look.

  • Select A1 cell.
  • Hit Ctrl + T buttons.
  • Click Ok from Create Table window.
  • Navigate to Design tab Under Table Tools section in the Ribbon.
  • Change the name of the Table to "Brand_DB".
  • Click on any cell, in this case I selected cell C3, and type "=" in cell C3 and select the brand names from the table, in this case select cells A2:A6.
  • As soon as you select the brand names (excluding the header), you can see that rather than "=A2:A6" the structured reference of the Table, which is "Brand_DB[Brand]", is being automatically applied. Now cell C3 contains "=Brand_DB[Brand]" as formula.
  • We can use this structured reference in drop-down list in combination with INDIRECT function. So the formula should be "=INDIRECT("Brand_DB[Brand]")".
  • Copy the formula.
  • Replace the formula in the Data Validation source from "=$A$2:$A$6" to "=INDIRECT("Brand_DB[Brand]")" and click Ok.
data table 7 data table 8 data table 9 data table 10

Now even if add another brand name, I added "Acer", the drop-down list will automatically update (as shown below).

data table 11

This is the last part of Excel Data Tables. I will talk about other useful features of Excel in later Articles.

Recent Posts
Top tags
  • Business

  • Corporate

  • Finance

  • Excel