Some years ago, we saw how one can highlight a row in an Excel table based on a cell value. Today, we are going to look into how to highlight a column instead of a row. It is quite similar, so I will only focus on the change required and explain the functions used.

To start off, follow the steps here (see details of steps from previous post):

1. Select the cells (including header) you want to highlight;
2. Add a “New Rule” under the “Conditional Formatting” option;
3. Click on “Use a formula to determine which cells to format”;
4. Type in the formula below and change <row> and <value> to reflect your own. =INDIRECT(ADDRESS(<row>,COLUMN()))=<value>
5. Select the highlight formatting you want to do;
6. And you’re done! Click “Ok”.

Example

The screenshot below shows a typical example, where I want to highlight the scenarios I want to test depending on the project status, the value in cell C4.

In my example, the conditional formula looks like:

=INDIRECT(ADDRESS(6,COLUMN()))=$C$4

Let’s take a closer look to each function in the formula. The most straightforward function is COLUMN(). This returns the column value where the function is being executed. That is, if I have the value of cell B1 set to =COLUMN(), then the computed value of cell B1 will be 2, since, column B is the second column (see Figure 2).

For the INDIRECT() function to work, we need to construct a proper cell address, that is, a letter followed by a number. Thus, we need to convert the column value into a letter. To do this, we can use the ADDRESS() function. This function takes two integer values, a row and a column, and returns a cell address in column letter and row number format. For example, the function =ADDRESS(1,2) returns $B$1 (see Figure 3).

Finally, we combine these functions to get the address of our required cell and use this formula to conditionally highlight our column. You can download the sample Excel sheet from my github repository.