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.

Comments(0) Filed in category: Excel, Excel table

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 […]

Comments(0) Filed in category: Excel

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.

Comments(14) Filed in category: Cross reference table, Excel, INDEX function

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 […]

Comments(9) Filed in category: Drop down lists, Excel

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 […]

Comments(113) Filed in category: Dependent drop down lists, Excel

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.

Comments(1) Filed in category: Excel

This article is about comparing records:

Compare two lists of data: Filter common row records in excel

This blog post describes how to extract common rows (records) from two lists in excel 2007. Sheet: List 1 Sheet: […]

Comments(7) Filed in category: Compare, Excel, Records

Recommended article:

Highlight closest value in excel

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

Comments(10) Filed in category: Conditional formatting, Excel

Download excel *.xlsx file

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