Excel makes it easy to track mountains of complex data. But just because the data is organized neatly in a spreadsheet, this doesn’t necessarily mean that the user can quickly and easily identify the intended meaning of the data set. Conditional Formatting is one of Excel’s tools that can flag specific cells, based on certain criteria, by applying various formatting techniques to help the user visualize data.
In the example I will use here, the spreadsheet lists reservations that have been received for a banquet. Tables in the banquet hall seat six persons each. Some guests have indicated that they are coming alone, and some reservations are for large groups that will have to be split up. In my data set I want to be notified when any reservation is for a single person or for a group of more than six persons so I can take extra care in accommodating those guests.
I will set a rule for the cells in column D so that any value that is equal to one or is greater than 6 will trigger special formatting in that cell.
To access Conditional Formatting options, on the Home tab of the Ribbon, in the Styles group, click Conditional Formatting. You can see in the menu a large number of automatic starters for describing cells to format:
Each of the first five categories of conditional formatting options has a fly out menu with further choices for describing what should be highlighted. At the bottom of the menu (circled in red in the graphic) are choices that you can use to create a new rule on your own, to delete or clear rules, or to manage existing rules. I’ll use the New Rule… option to highlight cells where the values are either equal to one or are greater than six.
But first I will select the cells I want to test for highlighting. Then when I click New Rule… in the Conditional Formatting menu, the New Formatting Rule dialog box opens with a list of Rule Types at the top and related editing tools in the lower pane. I will choose the Rule Type: Format only cells that contain.
The lower panel’s rule description is set up by default to allow a range of values.
But notice the drop down arrows in the first two fields of the description. Alternate choices exist in each of those fields to best describe the cells to format:For the example in the banquet scenario, I can choose rule settings for “cell value not between” 2 and 6. That way a cell value of one and anything greater than 6 will be targeted for the special format I select.
Now that the cells have been identified, the easy part is just to choose a look to give the cells to make them stand out. To proceed, toward the bottom of the New Formatting Rule window, click the Format… button.
The Format Cells dialog box opens to show tabs to access options for changing the format of the cell’s Number, Font, Border, or Fill.
Choose a fill color and click OK.
In the New Formatting Rule dialog box, click OK again.
Back in the spreadsheet, you should see formatting applied to cells that meet the conditions of the rule:
In this example, I have shown just one approach to identifying cells to highlight, but you can see there are so many more available in the Conditional Formatting menu. Explore them all to find the best way to describe the cells you need to highlight!!