How to use Conditional Formatting in Excel 2007

Conditional Formatting is probably one of my favorite things in Excel – the possibilities are probably nearly endless.

This post and video will explain two uses for Conditional Formatting:

1. How to have cells recolored automatically based on the text in the cell
2. Automatically alternate row colors using a macro and formula

In the video the sample water and sewer billing spreadsheet for a 200+ unit apartment building. Since tenants come and go we want to easily be able to visually see what units are vacant. I have set up Conditional Formatting to automatically color any cell that reads “vacant”.

The other use of Conditional Formatting in this same sheet is using a macro to automatically alternate the color on each row of data. (primary purpose is to just make the sheet easier on the eyes)

**IMPORTANT** prior to running any Conditional Formatting you must highlight the desired range of data

Steps to set up coloring of cells based on specific text

• Home tab>Conditional Formatting
• New Rule
• Format only cells that contain **(in bottom section of the screen fill out your desired criteria and be certain to chose a color – which is the “format”)
• Select OK

**In this example I chose “Specific Text”>Containing>Vacant

Steps for Automatically Alternating the Row Color is explained on this previous post. If you plan on using this frequently it would be beneficial to record a macro so that you do not have to remember the formula listed below.

• Home tab>Conditional Formatting
• New Rule
• Use a formula to determine which cells to format (again, be sure to chose your desired format)
• Select OK

The formula to be used is: =MOD(ROW(),2)=0

How to record a macro in Excel 2007:

• View Tab>Macros
• Record Macro (record using the steps outlined above)
• Stop Recording

**Be sure to save the Macro with an easy to remember name and / or shortcut and to save it in your personal macro workbook.

What are your favorite uses for Conditional Formatting?

About Michelle Mangen

Michelle Mangen is a single-mom of a son who always makes her laugh (when he's not arguing with her) and owner of Your Virtual Assistant in Neenah, WI. Your Virtual Assistant provides virtual services to small business owners who realize that not only can they not do it all in their business but also realize that their time is better spent on the tasks that provide them with the greatest return on their invested time. She can be found on Twitter as @mmangen