Excel 2010: Conditional formatting

Updated 2011.02.24

Objectives:

 

Use this spreadsheet for all of the examples.

Conditional formatting: Data Bars

Using data bars is perhaps both the easiest AND most effective way to do conditional formatting. Excel will draw a colored bar in the same cell as the numeric values that you want to conditionally format. High values will get long bars, and low values will get short bars. To use data bars:

·         Select the cells that you wish to apply conditional formatting to.

·         Click on the Home tab.

·         In the Styles group, click on the Conditional Formatting button (below).

·         From the drop-down list, click on Data Bars.

·         From the sub-menu, click on the desired bar color (defaults are blue, green, red, orange, light blue, and purple). Excel will automaticaly add the colored bars (below right).

              

Conditional formatting: Color Scales

Another conditional formatting option is Color Scales. With this option, Excel uses a gradient color scale to format the cells. I don't think this is as effective as the data bars because the viewer needs to know which colors represent which values, and this isn't obvious at first glance. To use color scales:

·         Select the cells that you wish to apply conditional formatting to.

·         Click on the Home tab.

·         In the Styles group, click on the Conditional Formatting button (below).

·         From the drop-down list, click on Color Scales.

·         From the sub-menu, click on the desired color scale. Excel will automaticaly add the appropriate fill color to each cell (below right).

                 

Conditional formatting: Icon Sets

Another conditional formatting option is Icon Sets. With this option, Excel adds an icon beside the number to reflect its value relative to the rest of the numbers. To use icon sets:

·         Select the cells that you wish to apply conditional formatting to.

·         Click on the Home tab.

·         In the Styles group, click on the Conditional Formatting button (below).

·         From the drop-down list, click on Icon Sets.

·         From the sub-menu, click on the desired icon set. Excel will automatically add the appropriate icon to each cell (below right).

               

Conditional formatting: Highlighting Cells

Sometimes you may want to apply your own conditional formatting rules. When you want to do this, you can use the highlight cells rules feature of conditional formatting. For example, if you are creating a gradebook spreadsheet, and you want to identify all of the grades below a certain point, you can have Excel do this for you by applying conditional formatting to the cells. To highlight specific cells:

·         Select the cells that you wish to apply conditional formatting to.

·         Click on the Home tab.

·         In the Styles group, click on the Conditional Formatting button (below).

·         From the drop-down list, click on Highlight Cells Rules.

·         The New Formatting Rule dialog box will appear:

·         At the top, click on Format only cells that contain.

·         In the Edit the Rule Description  section:

·         In the first drop-down box, click on Cell Value.

·         In the second drop-down box, click on the down-arrow and select the appropriate relationship. In this case, I chose less than.

·         In the last box, enter the desired value. In this case, I entered 60.

·         Click on the Format… button. You will see the Format Cells dialog box (below).

·         Select the desired formatting. In this case, I have selected a Font Style of Bold and a Color of red.

·         Click on OK. When the dialog box closes, all of the numbers that are less than 60 will be red and bold (below right).

            

Conditional formatting – formatting cell backgrounds

You can also format cell backgrounds. Let's assume that we also want to be able to identify the top scores by setting the background color to light bue.

·         Select the scores again.

·         Click on the Home tab.

·         Again in the Styles group, click on the Conditional Formatting button (below).

·         Click on Highlight Cells Rules.

·         Click on More Rules…

·         The New Formatting Rule dialog box will appear (below).

·         In the Edit the Rule Description  section:

·         In the first drop-down box, click on Cell Value.

·         In the second drop-down box, click on the down-arrow and select the appropriate relationship. In this case, I chose greater than or equal to.

·         In the last box, enter the desired value. In this case, I entered 90.

·         Click on the Format… button. You will see the Format Cells dialog box (below).

·         Click on the desired background color. Note that you can also select Fill Effects, More Colors, a Pattern Color, and a Pattern Style if you wish.

·         Click on OK.

·         You will be returned to the New Formatting Rule dialog box.

·         Click on OK.

 

Note that in addition to the Font tab and the Fill tab, there is a Number tab and a Border tab.