1.6 Conditional Formatting

Conditional formatting allows you to automate the formatting of a cell depending on its contents or other factors. For example, you can choose to change the color of the text in the cell, or highlight the cell with your selected fill color if a certain condition is true. In the image below, assume a fruit vendor is filling 6 boxes with randomly selected tropical fruits from her inventory. The vendor wants to highlight the number of Coconuts in each box, to quickly identify cases where there may be too many coconuts in any given box.

The vendor would begin by placing the cursor in any cell within the spreadsheet, and then clicking on the "Conditional Formatting" option in the Styles section of the Home tab. Then, click on "New Rule." This brings up a list of options that the vendor could use to create her formatting rule. Since the vendor wants to highlight only those cells containing the word "Coconut" she would click on the "Format only cells that contain" option. Then, use the dropdown menu in the far left field to select the "specific text" option, and type the word Coconut in the far right box. After completing the formatting rule, you will need to tell Excel what to do to those cells that contain the word Coconut. Do this by clicking on the "Format" box, and under the "Fill" tab, select the color of fill you would like Excel to place in the relevant cells. Finish this step by clicking "ok."

In order to apply the rule you just created to all cells in the table, ensure that your cursor is in the same cell where you clicked prior to creating the rule. Then select the "Conditional Formatting" option in the Styles section of the Home tab. This time click on the "Manage Rules" option. Notice that the cell you placed your command into is listed in the "Applies to" box. Click the up arrow and highlight all of your cells (cells B2:G16 in the example below), so the rule you created will apply to all of these cells rather than only to the cell where the conditional formatting rule is created. Then click "ok" to complete this task. Notice that every instance of the word Coconut should be highlighted in your selected color at this point. Congratulations! You are done with the task and can now easily identify the number of coconuts in each box.