< Go Back

Highlight row and column intersection exact match

General Formula

= OR ($A1 = row value, A$1 = Column value)       

Explanation – What is Conditional Formatting?

Using conditional formatting, highlighting intersecting row(s) and column(s) with exact matching is easy to carry out. You can do this using the ‘OR’ function which is a formula based on mixed references.

Example

Figure 1: Highlighting Intersecting Rows and Columns with Conditional Formatting

 

In the above example, the formula used in applying conditional formatting is:

=OR ($B8 = $J$6,E$5 = $J$7)

Conditional Formatting Formula – How it Works

Evaluating Conditional formatting is based on the range of cell(s) it covers, and it usually begins from active cells within the array of cells selected. In this scenario, cell B8 (for row) and E5 (for the column).’

To select  identical rows, the  expression below can be used :

$B8=$J$6

Since you are beginning with the row, keep it unlocked while the column remains locked such that values in column B alone, are likened to the car in cell ‘B8’ by this, reference to cell ‘J6’ is mixed. On the other way round, the reference to ‘J6’ is absolute since this will avoid alterations when applying conditional formatting to every other cell within B5:G10. In the above example, this expression will cause ‘TRUE’ to be displayed for cells in any   row where the car is ‘Benz’

  1. To select identical columns, the logical expression stated below will be very useful:

E$5=$J$&

Again, reference to E5 is mixed, however this time, around the row is sealed and the column-free such that values in row 4 alone can be compared with the day value in cell ‘J7’. Note that the reference to cell ‘J7’ is complete such that it won’t change conditional formatting is operational on the array of cells in B5:G10. In the above example, this logical expression will display ‘TRUE’ for each cell in a column where row 4 is “Wednesday”.

You can now use the ‘OR’ function to wrap the above logical expression for both the column and the rows since you are applying identical conditional formatting (blue fill) to both the column and the row. Where one or both logical expressions display ‘TRUE’, the rule is activated and conditional formatting is made operational

  1. Selecting Intersection Only

Simply replace the ‘OR’ function with the ‘AND’ function to select intersection only

= AND($B8=$J$6,E$5=$J$7)

If you want to select the interconnecting rows and columns within selected data set B5:G10 using formula, apply the conditional formatting formula. Where either or both logical expressions in both the row and columns displays ‘TRUE’, the rule is activated and conditional formatting is applied.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar