Home->Tutorials->Google Sheet help->
Conditional formatting – Highlighting cells in a row based on value in another cell in the row in an excel spreadsheet
[Updated as on Jan 04, 2023]
Let’s see an example as shown in the table below to have a better understanding of the situation:
Given below is a question bank in excel file which contains multiple choice GK questions in each row. The option number of the correct answer is given at the right end.
Instead of looking at the right end of each question to identify the correct answer, it would be more convenient that the cell containing the correct answer is highlighted. This can be done using conditional formatting in excel. Let’s see step by step how to do this:
Select the cell range for which conditional formatting is to be applied. In the example, select range C2:F4. Now click conditional formatting under Home menu in the excel sheet containing the table. Select new rule in the drop down. Click ‘Use a formula to determine which cells to format’ option in the window appearing.
Type in the formula below in the box provided.
=COLUMN(C2:F2)=MATCH($G2,$A$1:$F$1,0)
The formula functions as follows. Using the match function, the position (precisely column number) of the correct option shown in Column G in each row is calculated by matching the option names in the first row. The cell falling in the column number so calculated for each row based on the correct option shown in column G is selected for highlighting.
In the next step, by clicking OK suitable format is selected and applied. Finally the result will be as follows:
Page views: