Using Conditional Formatting to Save Time and Effort

If you are spending precious time trying to apply specific formatting to cells in your worksheets by hand, it can cost you. In a world where time=money, it is financially inefficient to do certain things manually that you could program to do automatically. Whether you’re collecting/tracking basic information while evaluating your competitors websites, or simply budgeting a Search Engine Marketing conference into the company budget, conditional formatting can help you provide a visual reference to data based on criteria that you set. Here are just a few very basic examples of how to use conditional formatting for your spreadsheets.

Alternating Row Colors

Your first, and perhaps most useful, conditional format option is to provide alternating row colors. Why is this useful? Well, if you are dealing with a multitude of columns, you might have a hard time matching up values from the same row, especially if you’re dealing with a large sum of data. This is something you see a lot of with regards to accounting ledgers. Honestly, even though it is functional, I think it looks professional. Here’s how you set the conditional formatting…
1. Select the cells you want to format.
2. Go to the Styles section of the Home Tab and select the drop down menu under Conditional Formatting. Select “New Rule” and select the last option which is “Use a formula to determine which cells to format”.
3. Enter the following formula into the field: (just copy/paste this into the formula field)

=MOD(ROW(),2)=0

4. Set formatting accordingly (Select the “Format…” button and make your formatting changes, such as setting the Fill color [or any other format by perusing the various tabs on the top of the format cells window] to a light gray like the example on the right). Then hit the “OK” button.

A SIDE NOTE ABOUT CONDITIONAL FORMATTING: If you need to go back to change the cells that you want to apply formatting to, go back to the Conditional Formatting drop down menu on the Home Tab, select “Manage Rules…”. This will take you to a window (dubbed the Conditional Formatting Rules Manager, or as I will refer to it as CFRM) where you can adjust the group of cells that each conditional format applies to, as well as allowing you to change the order/priority of rules (you’ll see little arrow buttons that will promote or demote the rules that you select). 

Please don’t forget to make sure that the drop down menu at the top of the CFRM window “Show formatting rules for:” is set to “This Worksheet” if you want to manage all of your rules. You can expand the range that you want the formatting to apply in the CFRM (under the Applies to field), or you can copy a cell with the conditional formatting of your choice, select the other cell you want it to apply to, and Paste just the cell’s format only. You can selectively paste a cell’s format only by using one of three methods. You can use the keyboard shortcut (my choice of methods) of hitting "ALT+E" then hit "S". Otherwise, you can go to the Paste drop down menu in the Home Tab, Select Paste Special, and select the "Formats" option. Or third, you can right click the cell, select Paste Special (not to be confused with regular ol’ Paste), which will then bring you to a window with an option to paste "Formats".  With Excel, there are several ways to skin a cat…no offense to my feline friends. However, if you keep copy/pasting formats, it can make your CFRM quite messy, so I recommend just adjusting the range of cells where the rule applies to whenever possible.

Highlight Blank Cells

Another helpful conditional format that can help save you time and eye strain while looking for missing information is to highlight blank cells. Let’s say you’re trying to make sure that you have data listed in every cell in a given group of data, and you need to find any blank cells. If you’ve got a lot of data to sift through, you can waste time looking for blank cells. Solution: Create a conditional format that highlights/formats the cells whose contents are blank. Here’s how…
1. Select the cells you want to format.
2. Go to the Styles section of the Home Tab and select the drop down menu under Conditional Formatting. Select “New Rule” and select the option “Format only cells that contain”.
3. In the first drop down menu (default value is “Cell Value”), select: “Blanks”.
4. Set formatting accordingly (Select the “Format…” button and make your formatting changes, For the example on the left, I set the fill color to a light reddish salmon color. Then hit the “OK” button.

Highlight Cells that Contain an Exact Value

Just like the previous step, you select the option "Format only cells that contain", but instead of selecting the “Blanks” option in the “Cell Value” drop down menu, keep it at “Cell Value” and change the second drop down from “between” to “equal to”. Here’s a rundown: 
1. Select the cells you want to format.
2. Go to the Styles section of the Home Tab and select the drop down menu under Conditional Formatting. Select “New Rule” and select the option “Format only cells that contain”.
3. In the first drop down menu (default value is “Cell Value”), select: “Cell Value”.
4. In the second drop down menu (default value is “between”) select “equal to”.
5. In the following field, include your value. This can be a number, date or text. I used the letter "D", which will highlight all instances of the letter "d" or "D".
NOTE: Excel very nicely puts the “=” before the value you specify, and if you listed text, it put quotation marks around it, so that you don’t have to put it into correct syntax. Isn’t that a time saver? You’ll also see that if you entered a date, it will convert it to its serial number form.
6. Don’t forget to set your formatting accordingly. In the example to the right, you will see that I selected the format of a light blue fill.

Highlight Cells in a Specific Range of Values

On a somewhat similar note, what if you were trying to highlight cells that contain values in a specific range that you specify (this includes dates, too). Here’s how you would do it…
1. Select the cells you want to format.
2. Go to the Styles section of the Home Tab and select the drop down menu under Conditional Formatting. Select “New Rule” and select the option “Format only cells that contain”.
3. In the first drop down menu (default value is “Cell Value”), select: “Cell Value”.
4. In the second drop down menu (default value is “between”) select “between”.
5. In the fields, include your values. Again, you can use dates, but keep in mind that Excel will convert your date to its serial number (e.g. 3/13 – March 13th – would be converted to 40250 ). I selected the range of 5 through 12 for the example on the left.
NOTE:  Like before, if you use dates in your values, Excel will automatically convert the dates to their serial values.
6. Set formatting accordingly (Select the “Format…” button and make your formatting changes. The format I chose for this example was a lime green fill color. Don’t forget to hit the “OK” button.

Play around… experiment! For more tips, check out the Excel help section at Microsoft Office’s website for some other great conditional formatting features for Microsoft Excel 2007. You can also use the default conditional formatting rules that come with Excel 2007 which you can customize in the CFRM. But, once again, I will have to elaborate upon that on a later post. Check back again later for more tips on how to utilize more features that Excel has to offer and look out for more advanced conditional formatting tips.