David Hager gave this valuable comment about how to reference a table name in conditional formatting formulas:


Watch this video to learn more

You can also use this technique in data validation lists. I used it in the three drop down lists below. The formulas are in C13:C15.

Reference table headers in a drop down list, cell B13:


Read more about cell references to Excel defined tables (structured references):

Become more productive – Learn Excel Defined Tables

An Excel table allows you to easily sort, filter and sum values in a data set where values are related.

Reference a table column in a drop down list, cell B14:

=INDIRECT("Table1[First Name]")

Recommended article

INDIRECT function

INDIRECT(ref_text,a1) Arguments Ref_text is a reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as […]

Reference a table row in a drop down list, cell B15:


Recommended article:

INDEX function explained

Fetch a value in a data set based on coordinates.

Read more about Drop Down Lists:

Add new items to a drop down list automatically

A drop down list in excel prevents a user from entering an invalid value in a cell. Did you know that you […]

Create dependent drop down lists containing unique distinct values

Here is a list of order numbers and products. We are going to create two drop-down lists. The first drop […]

Table names in conditional formatting formulas

I used the same technique in the following example:

A row is highlighted if a record in cell range A13:C22 is equal to a record in the excel defined table (A1:C11)

Conditional formatting formula, cell range A13:C22:

=COUNTIFS(INDIRECT("Table2[First Name]"),$A13,INDIRECT("Table2[Last Name]"),$B13,INDIRECT("Table2[Company Name]"),$C13)

Read more about the COUNTIFS function:

COUNTIFS function

Checks multiple conditions against the same number of cell ranges and counts how many times all criteria are met.

This article is about comparing records:

Compare two tables: Filter common records

I will in this blog post describe how to extract common records from two data sets in Excel. I have […]

Recommended article:

Highlight closest number

This post decribes how to highlight records with closest value to a criterion. You can also choose to highlight the […]

Download excel *.xlsx file

Reference a table in a data validation list or conditional formatting formula.xlsx