The use of conditional formatting is great for presenting data via worksheet cells in a "chart" form. A simple model is probably the best way to illustrate this concept. Assume you're working in a business with a high degree of seasonal variation. In this example, a clinic in Phoenix, Arizona currently runs three MRI scanners. The manager of the Radiology Department believes that they need another one but can't tell when. Volumes are high in the winter months because patients from the northern U.S. go to the southwest during the winter. The volumes taper off in the summer months because those same patients go back home to beat the desert heat. Each scanner can handle six exams per workday. However, downtime due to maintenance makes each scanner dependable at approximately 65% capacity. There are an average of 22 workdays per month and the expected annual exam growth is 3%.

Given the assumptions above, a simple model can be built that visually shows the seasonal variations.

By applying conditional formatting to the data table, you can easily see when the volumes exceed the average expected exams per month. The conditional formatting formula for the data table monthly spread looks like this:

The average annual expected volumes in column Q use the same conditional formatting formula except the formatting is set to red as opposed to tan.

The conditional formatting allows you to easily see that the demand for Radiology exams will exceed the capacity of the three machines starting in February of 2006. On an average annual basis, the total demand for exams will exceed the total machine capacity in 2010.

Given the visual that the conditional formatting provides, a more educated decision can be made concerning when to purchase the next machine.