Conditional formatting in Excel allows users to highlight data that matches certain criteria. Sometimes there is no need to create a pivot table or subtotals, conditional formatting is just the trick.
Excel 2007 Conditional formatting has many more preset layouts, colors, and options than previous versions of Excel.
To add conditional formatting in Excel 2007, follow these steps:
Open the spreadsheet containing the data you want to apply conditional formatting.
The “Conditional formatting” function is located on the “Home” tab in the “Styles” group.
Highlight the data you want to format based on the condition you choose to apply.
For example, if the data is in Column C, highlight Column C.
Click on “Conditional formatting” and select any of the preset formatting.
Let’s cover each of these briefly.
1) Highlight Cell Rules
This option allows users to format data that is “greater than,” “less than,” or “equal to” a number which you provide. It also permits selecting and formatting data that is “between” two numbers, “date occurring” at a certain time (today, yesterday, etc), and allows formatting of “duplicates.”
Duplicates can be found using a few other functions, but this is a great time saver for users that I’ve trained. It may be the number one use of conditional formatting in Excel 2007.
In selecting each of these options, users will then be provided a window asking for the specific number or choice for formatting.
2) Top / Bottom Rules
This option would be used for statistical analysis. For example, if you have a list of class scores and you want to highlight the top 10% of the scores, then you would select “Top / Bottom Rules” and then select “Top 10%.
Within these choices, users can change the selection to an alternative to the main default.
In the example above you may have wanted the top 5%. In this case, select the same option and change the number in the “Top 10%” window to 5. Once you click the “OK” button, only the top 5% will be formatted.
With either of the options above, simply change the formatting or coloring by clicking on the “With” drop down. The default formatting is “Light red fill with dark red text.”
3) Data Bars
Selecting the “Data Bars” option only gives the user a choice of colors. The length of the bar then shows the value as it relates to the rest of the data. A longer bar is towards the highest values. A shorter bar would then be towards the lower values.
4) Color Scales
Selecting the “Color Scales” option is similar to the “Data Bars” selection. However, the “Color Scales” will do just that, scale the color you select amongst the data. Therefore, if you select the “Blue Yellow Red” color scale, the highest numbers will appear shaded blue and the scale will fade as the values are lower, with the lowest values appearing shaded red.
5) Icon Sets
The “Icon Sets” option will format the cells with the icons selected. If you are working with numbers, and select the “3 Colored arrows” format, the lower values will show a Red Arrow pointing down, a Yellow Arrow pointing to the side, and a Green Arrow pointing upwards.
6) Manage Rules
The last option we will cover in more detail within a future training article. However, I’ll provide an overview here.
The “Manage Rules” option gives the users the opportunity to select more than one criteria. Let’s say you have the same class scores as the example above. If you want to use color to show grades of A, B, C, etc, then you would use the “Manage Rules” option.
In the “Manage Rules” window, click on the “New Rule” button.
Next, select the “Rule type” in the upper portion of the window.
NOTE: Depending on the “Rule type” selected, the window below will provide different selections.
With our “Grades” example, we would select the “Cells that contain” rule type.
In the window below, we select “Cell value” between “70” and “79.” (This is the format we are setting for the “C” grades.
Click on the “Format” button and use the “Formatting window” to select the font style, size, color, etc, along with any “fill” color you want. Click on the “OK” button when you have completed the formatting selections.
Click on the “OK” button to accept the “New Rule” and continue creating more rules within the “Manage Rules” window.
Again with the grades example, create a new rule with different formatting for grades between 80 and 89. And, then create another rule for grades between 90 and 100.
Once you have completed setting up each of the rules, click on the “OK” button within the “Manage Rules” window and all formatting is applied.
HINT: In this example, it might be a good idea to create a “Key” and place it at the top of the window so that anyone viewing the spreadsheet knows which colors or fonts match what grade level. In my example, I placed the letter “C” in one cell and formatted it with the same formatting I applied in the “Manage Rules” window. I did the same for “B” and “A.”
IMPORTANT: When working with “Conditional formatting,” to ensure you do not apply multiple formatting (when you do not want to), click on “Conditional formatting” and select “Clear Rules.” Then, select whether you want to clear the rules for the selected (highlighted) area or for the entire spreadsheet.
See the picture above a screenshot that will help explain.
Excel 2007 conditional formatting can make it easy for anyone to provide a professional analytical view of data. Practice with the conditional formatting options and your spreadsheets can provide visual appeal to viewers.
