In this tutorial, we will see the steps required to change the background colour of cell based on text entered in another cell using conditional formatting in Excel 2011 for Mac. Let us considered the following sample data with name of students and their Grade. Now based on the text entered under grade, you want to provide a background colour to the corresponding cells in G,
Step 1: Select cells under column G and and click Conditional Formatting option under Format section.
Step 2: Select Highlight Cells Rules then More Rules from the Conditional Formatting drop down list.
Step 3: In the New Formatting Rules, select Use a formula to determine which cells to format.
Step 4: Now enter the formula as shown below for Grade “A”
Step 5: In the Format with option, select custom format and specify the background colour for the cell. For example, in the above screenshot background colour is set to green.
Step 6: Click OK to confirm and create this rule.
Step 7: Now repeat the steps from 2 to 6 for other grades.
The conditional formatting rules will be applied for all the selected cells. So if you have added a new entry the corresponding grade colour will be automatically populated.