Enhance Your Excel Reports with Conditional Formatting

Conditional formatting enables you to enhance your reports and dashboards in Excel by dynamically changing the formatting of a value, cell, or range of cells based on a set of conditions you define. Conditional formatting adds a level of visualization that allows you to look at your Excel reports and make split-second determinations on which values are “good” and which are “bad,” simply based on formatting.

To get a first taste of what you can do, click the Conditional Formatting button found on the Home tab of the Ribbon, as shown in this figure.

image0.jpg

Create Data Bars in Excel reports

Data Bars fill each cell you are formatting with mini-bars in varying length, indicating the value in each cell relative to other formatted cells. Excel essentially takes the largest and smallest values in the selected range and calculates the length for each bar. To apply Data Bars to a range, do the following:

  1. Select the target range of cells to which you need to apply the conditional formatting.

  2. Choose Data Bars from the Conditional Formatting menu on the Home tab, as demonstrated in this figure.

    image1.jpg

    As you can see in this figure, the result is essentially a mini-chart within the cells you selected. Also note that by default the Data Bars scenario accounts for negative numbers nicely by changing the direction of the bar and inverting the color to red.

    image2.jpg

Apply Color Scales in Excel reports

Color Scales fill each cell you are formatting with a color varying in scale based on the value in each cell relative to other formatted cells. Excel essentially takes the largest and smallest values in the selected range and determines the color for each cell. To apply Color Scales to a range, do the following:

  1. Select the target range of cells to which you need to apply the conditional formatting.

  2. Choose Color Scales from the Conditional Formatting menu on the Home tab. See the figure.

    image3.jpg

    As you can see in this figure, the result is a kind of heat-map within the cells you selected.

    image4.jpg

Use Icon Sets in Excel reports

Icon Sets are sets of symbols that are inserted in each cell you are formatting. Excel determines which symbol to use based on the value in each cell relative to other formatted cells. To apply an Icon Set to a range, do the following:

  1. Select the target range of cells to which you need to apply the conditional formatting.

  2. Choose Icon Sets from the Conditional Formatting menu on the Home tab. As you can see in this figure, you can choose from a menu of Icon Sets varying in shape and color.

    image5.jpg

    This figure illustrates how each cell is formatted with a symbol indicating each cell’s value based on the other cells.

    image6.jpg
blog comments powered by Disqus
Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win $500. Easy.