Excel 2010: Flagging Data by Applying Conditional Formatting

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.

Reservation numbers
Reservation numbers


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:

Conditional Formatting Menu
Conditional Formatting Menu

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.

New Formatting Rule dialog box
New Formatting Rule dialog box

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:Choices in describing cells to formatFor 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.

Format Cells dialog box
Format Cells dialog box

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:

Cells highlighted based ontheir values
Cells highlighted based on their values

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!!

One Comment

  1. Tom P.

    I have a spread sheet I use as a calendar. There is a list of 23 names in Column C. Of the 23 names there are 9 that do a similar job and I can not schedule more than 4 off on any one day. For example this year July 5th was a Friday and it was Extremely coveted Day Off creating a 4 day weekend.

    The names are in random order in the Name Column C. Is there a Conditional Format or Formula that will give a stop warning when I try to add the 5th name from the restricted group in Column C to Column H ( Friday July 5th) .

    Tom P.

Leave a Reply

Your email address will not be published. Required fields are marked *