Tip #56 - Excel Conditional Formatting

100 Computer Tips in 100 Days

Tip #56 - Excel Conditional Formatting

For those of you who work with Excel, I think you’re going to really love today’s tip.

Conditional Formatting, as the name implies, allows you to automatically format a cell in a way that you design, based on the contents of the cell. When the entry in a cell meets the criteria that you have defined, then the format of that cell changes automatically.

I’m in charge of a yearly event and keep track of the responses in an Excel file. Some people send me checks, others want me to charge the event to their club account and still others will give me money at the door. Depending on which option they elect, I want the color of the cell to change. Here’s how I did this.

I select the column that is going to have the special format by clicking on the letter at the top of the column. From the Home Tab, Styles group I click on Conditional Formatting and chose New Rule… In Apple it’s on the Format menu. I choose “Format only cells that contain” at the top of the box and in the lower part of the dialog box choose “Cell Value” “equal to” and in the third box I typed “club charge”. Now the fun begins, I click the Format button.  I use the Number, Font, Border and Fill options to make my entry as distinct as it needs to be and click OK and then OK again. Suddenly all of the “club charge” entries conform to the format I specified. When I changed the entry to “at the door” the format changes automatically again. As you can see from the screen shot, a column can have many conditional formats assigned to it.

Can you imagine using this for an accounts receivable worksheet? When the elapsed time from the date of the invoice reaches 60 days it becomes one color, when it reaches 90 days it displays another color format. The applications for this are endless.

My book, 100 Amazing Computer Tips - Shortcuts, Tricks, and Advice to Help Everyone from Novice to Professional, is available NOW at Amazon.com and Barnes & Noble in both print and ebook formats. There's also a handy link on top right side of this window.

Happy computing!

Diane

Don't forget to click the Subscribe to 100 Computer Tips in 100 Days via email link on the right side of the screen so you get email alerts when Diane adds a new post.