In this post, I’ll show you how I like to use Conditional Formatting in my Excel models to add a disabled look to some cells based on a criteria. With this technique, you will get clear visual indications that will simplify inputs and avoid errors.

The context

As an example, you need to enter monthly sales volumes for a product that is yet to be launched. The spreadsheet is layed out with one column for each month. The goal is to have the input cells shaded for the months previous to the launch date.

Of course, you could do that manually, but that wouldn’t be fun nor very flexible. Instead I’ll show you how to setup conditional formatting to have it done automatically without resorting to macros.

Model Layout

The solution

1. Setup Data validation

To make sure the launch date inputs are valid, you need to add data validation with a drop down list of all months next to each product.

2. Setup Conditional Formatting

The next picture demonstrates how the conditional formatting is set, with L$6 being the evaluated month and $D11 being the launch date.



Conditional Formatting setup

3. Adjust formulas

Since this technique only changes formats and leaves the input in the shaded cells, you need to adjust the formulas that are connected to the inputs. This insures that your results are not affected by inputs in the shaded cells. You can see how I have adjusted the annual sums and the revenue calculation to consider the launch date by downloading the example.

Final thoughts

As it is setup in the example, you have to adjust the inputs each time you change the launch date. The conditional formatting acts only as visual help for inputs. However, it could also be interesting to have formulas that adjust with the launch date instead of the inputs.

Download:

Conditional Format Controls – Part Deux.xls (size 124Kb)

Other related post:

Conditional Formats Control – The Warm-Up