Conditional Formatting in Excel

Conditional Formatting in Excel applies format to selected cells based on user defined criteria.  You can apply Conditional Formatting either by selecting the pre-set rules that Excel provides or by creating your own set of rules based on the specific criteria you need.  You can also use Conditional Formatting to color code information and then filter and sort on the colors applied.  Conditional Formatting is found on the Home Tab in the Styles Group across Excel 2007 – 2013.

What is the point of Conditional Formatting?   It is an excellent way to add visual emphasis to a worksheet, making it much easier to identify important information.  It is the equivalent of going through your spreadsheet with a highlighter to identify the information you seek (and who has time to do that).  As an example, you may have a fairly large spreadsheet that contains sales figures for the year.  You want to identify all the sales that are over $4500 for the year.  By applying a Conditional Formatting rule, you can quickly identify all sales over $4500 and assign those sales to a specific format, making them easier to identify.

You can also layer Conditional Formatting rules over top of each other so that cell formatting changes based on the information entered.  As an example, I have a client who works in Risk Assessment for a major industrial company.  He layered Conditional Formatting rules to identify levels of risk found on the job site.  Any cell with a value of 1-50 was yellow, representing a low risk while cells with a value of 51-100 turned orange, representing a medium risk.  Cells with a value of 101 – 150 would turn red, representing a high risk.  Once the Conditional Formatting rules had been applied, cells automatically changed color depending on the data entered.  Not only was he able to quickly identify levels of risk, he could then sort or filter based on the formatting applied.

These are just two examples of how Conditional Formatting can be applied.  You can also create your own rules based on specific sets of data or format cells based on the formulas they contain.  Below are just a few of the options that are available through the Conditional Formatting feature in Excel.

Highlight Cells Rule Applies formatting based on cells that are greater than, less than, between or equal to a value or that contain specific text, dates or duplicate values
Top/Bottom Rules Applies formatting based on cells that are in the top or bottom percentile (you may specify the percentage)
Data Bars Fills cells with gradient color based on their value in regard to surrounding cells
Color Scales Fills cells with two or three color formats based on their value in regard to surrounding cells
Icon Sets Tiny graphics placed within a cell which indicate the value of the cell.
Format All Cells Based On Their Value/
Format Cells that Contain
Formats Cells based on specific, user defined criteria
Use a Formula to determine which Cells to Format Formats Cells where a formula is found to be true

Next time you need to quickly identify information within a spreadsheet, consider using this handy tool to streamline workflow and save time and energy.


This entry was posted in: MS Excel Tips.
"Hawthorn Training delivers expert and up to date software training solutions for your business."