Conditional Formatting allows you to visually change the cells in your spreadsheet based on a set of rules you determine. You can set rules to highlight important cells or ranges of cells, emphasize unusual values, and visualize your data using the data bars, color scales, and/or icon sets. Now in Office 2010 you can also use conditional formatting with references on different worksheets within the workbook. Another improvement is the ability to create data bars to track in-cell progress charts.

Adding Conditional Formatting

Conditional Formatting allows you to apply styles to your cells or a range of cells to create a visual representation of your data, so you can analyze it quickly. You can use simple rules or you can do complicated data analysis with conditional formatting. Let’s take a look at a couple of examples.

Let’s say you want to quickly look at your sales for the year and see how each region is doing in comparison to each other.

1. Select the data that you want analyze.
2. On the Home tab, click on the Conditional Formatting button drop down arrow.
3. Choose New Rule.
4. In the New Formatting Rule dialog box, use the first rule type Format all cells based on their values.
5. In the bottom part of the dialog box you can choose the color, what value to base the color gradient on or even if you want to use data bars. Let’s just use the default, so click OK.

Solid Data Bars

We can also take the same set of data and use conditional formatting to create data bars representing the data. New to Office 2010 is the ability to use solid fill data bars.

1. Select the data that you want to analyze.
2. Click on the Conditional Formatting drop down arrow, mouse over Data Bars, and then click one of the Solid Fill options. (Note: the Live Preview will allow you to preview the various options.)

Icon Sets

Icon Sets allow you to organize and classify data into three to five categories. The icon sets can visually represent a range of values. In Office 2010, they have expanded the different types of icon sets you can use.

1. Select the data that you want to analyze.
2. Click on the Conditional Formatting drop down arrow, mouse over Icon Sets, and then click one of the Directional options. (Note: the Live Preview will allow you to preview the various options.

Cross-Sheet References

Another new improvement to Excel 2010 is the ability to create conditional formatting with references from different sheets within a workbook. This is very beneficial in creating switchboards that can visually show trends and represent data on various worksheets.

1. Click on the Conditional Formatting drop down arrow, mouse over Highlight Cell Rules, and then click one of the Greater Than options.
2.  Click on the   (RefEdit icon) and simply navigate to the sheet whose value you want to compare against.
3. Once you have selected the cell you want to use in the comparison click the RefEdit icon again, and then click OK.

Conditional Formatting is an Excel tool that provides you with the ability to implement your own set of rules to visually change cells.  Our hope is that by learning more about conditional formatting you will be able to increase efficiencies in the way in which you work.