Archive for June 12th, 2013

Excel Tip: Custom Conditional Formatting June 12th, 2013

Vinod Kumar

I hope you are enjoying the Excel Tips series I planned for this week. I got feedback from people to show some of the old tips I show them with Excel 2007 or Excel 2010 version. So here is a simple blog post which will show how to use custom Conditional Formatting with Excel.

So for this blog post, we will use a simple table. We have 3 columns and some values to play with.

Conditional Formatting

Highlight cells from C3-D4 and select out out-of-box conditional formatting options. In the below figure we have selected, Home Tab –> Conditional Formatting –> Data Bars. Hover over the various options to check how it looks in our table.

In below example, I have shown how it looks using “Orange Data Bar”.

When you want to have a gradient of colors, select the “Color Scales” option and choose one of the options. Below I have shown how data will look in a “Green-Yellow-Red” color scales. This is one of the easiest color coding where Red means Low values and Green mean high values.

Now these are basic out-of-box features. I hope you have used them in the past already.

Custom Conditional Formatting

With out-of-box options available, what if we wanted to color code our rows based on our requirement. Let me describe my requirement, I want to highlight rows who’s Sales value is greater than 60.

To achieve this, Select “Conditional Formatting” –> “New Rule …”.

This will bring up the rules dialog box. Here select “Use a Formula to determine which cells to format”.

As per the requirement we want the “Sales column” value to be greater than 60. So in our “Formula” bar, enter value of “=$C3>60”. Next Select the Format button.

This brings up the Format dialog as above. I have selected the “Fill” tab to fill the cells with Green color. Feel free to use your own color coding. Now the final – “New Formatting Rule” dialog looks like below.

Clicking “OK”, the table now highlights the rows with Sales value greater than 60.

How cool is that? Formatting can be quite powerful and we can add interesting extensions to this. Maybe I will write the same next. Do let me know if you learnt something new today. Thanks for reading this far.

Continue reading...