Microsoft Office 2010: How to auto color cells depending on their value in Excel

The goal of these instructions is to show you how to add a rule on a cell or a group of cells that will auto color them according to your needs. For example, perhaps you have a financial spreadsheet and want to auto color in red all the expenses and green the earnings.

First, you need to identify all the cells that will need the rule for auto color. Once you have them identified, select them.

Go to your tab menu “Home”, and then, on the “Styles” section, click on “Conditional Formatting” to drop down the menu.

Use the “Highlight Cell Rules” option to select the rule that better fits your idea, for instance “Greater than…>”. In the pop up box complete the fields with the conditions that will turn the cell into another color; continuing with the example, on the left field we will write “50” (without quotes) meaning that, whenever the cell contains a value over 50, the rule will apply. Once you have the condition set up, it’s time to set up the effect. On the right field, you have a list of “commonly used options” feel free to experiment with those, or try to “custom format” your rule. If you select custom format, it will bring the Format Cells window, where you can set up which Font, Fill and Border the cell will turn into if it meets the parameters in the condition you have set up.

Once you have your condition and the resultant effect set up, you just need to fill the cell with data. You can have several rules over the same cell as long as they don’t overlap with each other (if you have for instance 2 “Greater than” rules it may not work as you intended it to work). You can set up a “greater than”, a “lower than” and a “equals to” rule on the same cell.
The contents of the cells do not necessarily need to be straight data, you can have formulas instead of text or numbers and it will work just as fine.

Give this a try and let us know how it went. And, if you have a tip of your own that you would like to share, or a suggested problem to solve in next month’s newsletter, let us know. Leave us your comments.

This entry was posted in How-to tips. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>