Today we are going to discuss about conditional format in excel for Intersection of row and column. Before getting into the topic, let me give you the insight about our requirement and the desired result.
Here, we do have drop down in B2 cell to allow the user to select region and drop down in C2 cell to select Month.
Based on the selection of region and month, conditional formatting needs to be applied in row and column and in the intersection of both. Lot to learn, let's dive in.
To access Conditional Formatting,
Menu Bar -> Home Tab -> Styles group ->Conditional Formatting
Click on the drop down as shown above, you could see the hundreds of options to format.
To accomplish our task, we are going to use simple formula's which will deliver Boolean values (TRUE or FALSE) as output. For those cells which are resulting in TRUE's, conditional formatting will be applied, Others will be ignored.
To start from the scratch,
Select the range, where we need to apply the conditional formatting. Ensure that, the active cell is the Top Left Cell of the selection. Click on the Conditional Formatting option as shown in above picture, Click on the 'New rule' option at the bottom of the selection box.
In below dialog box, click on the last option "Use a formula to determine which cells to format" and in the formula section type as shown below.
After entering the formula, click on the Format option. Now format cells dialog box will open, in that click on the Fill tab. Select your desired color and click OK.
In 'New formatting rule' dialog box click OK. You can see that all the cells in that row is formatted based on your selected color. Conditional formatting formulas are there in the memory of excel but not in the cells. So, we do not need to bother about circular reference.
For the column formatting, again do the same steps, use the above formula and select the color based on your preference and click OK. Now, in conditional formatting rules manger dialog box click apply and OK.
For the row and column intersection formatting, again do the same steps, use the Below formula and select the color based on your preference and click OK. Now, in conditional formatting rules manger dialog box click apply and OK.
Now change the region and month in B2 and C2 cell respectively, you can see that the formatting changed to the respective row and column and in the intersection.
Comments