Tuesday, June 24, 2008

Using Conditional Formatting in Excel to Quickly Spot Trends




Conditional Formatting Helps You Identify
Groups and Trends Easily

Conditional formatting in Excel lets you identify trends at a glance by formatting cells according to certain pre-set conditions – hence the name.


So, for example, let’s say you run a pet food company and you want to make sure you re-stock when inventory is low. You might set up a spreadsheet like this, which tracks the total number of cases in each of your six warehouses.

Let’s suppose your definition of “low inventory” is anything under 2000 cases. You can use conditional formatting to quickly identify all those warehouses containing fewer than 2000 cases.


First, select the column you want the formatting to apply to. Then go to the Format menu and select Conditional Formatting.

The Conditional Formatting menu contains a series of drop-downs. Select “Cell Value Is”, “Less than”, and then type 2000 into the field. Click the Format button and select how you would like all cells with values under 2000 to be formatted. We chose to make them bold and red. Then click OK.

You’ll see the Conditional Formatting menu with everything filled in. Click OK again. Now the chart will have all inventories under 2000 rendered in bold, red type. Time to start restocking in Cleveland, Des Moines, and Essex!


No comments: