Skip to content

Conditional Formatting in Excel 2010: Analyze and understand the Data at a Glance

Microsoft Excel is one of the very advanced spreadsheet applications that lets you easily create and manage complex spreadsheets. Imagine having a spreadsheet with large amount of data. Making some sense out of this data is a big task by itself. Scanning through rows and columns to read and interpret the data can be really difficult. For example, if we have a spreadsheet to capture the activities in a production line. Your employees may be feeding various data into this spreadsheet. If you’re required to make changes in your production line because the reject rate in the line has increased, but it was left unnoticed. What if Excel could highlight / stand out those important cells according to some preset values in other cells? In this case, if the value in the reject quantity cross certain number, a particular cell or series of cells will change its color to Red and the bold font, so that you notice these cell(s) quickly! That’s what conditional formatting in Excel can do for you.

In this article, I am going to show you how to create conditional formatting in Microsoft Excel 2010. The steps we follow here is same for Excel 2007 as well.

What is Conditional Formatting?

Conditional Formatting in Excel allows you to format a cell or a range of cells based on various predefined conditions. Conditional formatting is different from the normal formatting, because normal formatting is set to be static whereas a cell with conditional formatting changes according to the values in a call or a range of cells.

Example:

conditional formatting in ms excel

Creating Conditional Formatting

Creating conditional formatting in Excel 2010 is pretty easy.

Here’s a video tutorial on how to create Conditional Formatting in Excel 2010:

Step 1:

Select the cell(s) you want to create conditional formatting | In this case, I selected cells B10 to F10

Step 2:

Click on Home tab | Conditional Formatting in Styles group

Step 3:

Click New rule

conditional formatting in ms excel

Step 4:

Select the type of rule you want to apply | In this case, since I want Cells B10 to F10 to be highlighted if the value in any of these cells are greater than 30,000. The format of these cells will change to Red color background with white bold fonts.

Step 5:

Under Format only cells with | Cell value | Greater than | 30,000

Step 6:

Click on Format button | Change the font and background styles here Once done, if the cell value in any of the cells in the B10 to F10 range goes more than 30,000, the cell background will be red and the font will be in bold, white.

conditional formatting in ms excel

After Applying Conditional Formatting

conditional formatting in ms excel

conditional formatting in ms excel

Managing Conditional Formatting Rules

You can manage all your conditional rules by going to the Manage Rules window. You’ve an option to see all the rules applied to the current worksheet or the rules applied to the selected cells. You can also edit the rules from the Manage Rules window, by selecting the rule that you want to edit.

conditional formatting in ms excel

Do you find this information useful? Share it with your friends by on Facebook, Google+, Twitter or other Social Media. You can also follow me on Twitter @sarayoo.info or Google+ or Like me on my Facebook or on my LinkedIn forĀ  more updates, technology tips and tricks, iPhone, iPad, other iOS devices tips, iOS App Deals, Blogging tips, etc. Please leave your comments in the comment section or contact me if you have any other questions.