Conditional Formatting in Excel: Easy to follow guide!

A very easy and powerful feature in excel is Conditional Formatting. You can make your data and reports visually appealing using below features:

1. Highlight Cell Rules based on various conditions – date based, text based, duplicate values, greater than, less than etc.
2. Top and Bottom Rules – top 10%, bottom 10%, above average, below average etc.
3. Data Bars – Colored data bars with Gradient fills or Solid fills
4. Color Scales – Apply a color gradient to to a range of cells
5. Icon Sets – Directional Icons, various shapes, indicators and ratings etc.

This is the second part of our Excel Yellow Belt blog series. Continue learning excel courses with ease how to use different functions and formulas in MS Excel. Enhance your performance and efficiency with the program and grow professionally.

Excel spreadsheets have become the source of every kind of information, in just about every sector. Be it a factory inventory, time table schedules of a school or a payment roll of an office, everything gets done on excel spreadsheets today. Needless to say, it has become an imperative part of the skills that are to be mentioned on your C.V.  There are many excel courses available online these days, however, we have highly effective online lessons for all levels.

In our previous blog Ultimate Guide to Learning Excel for Beginners easy ways to use mathematical formulas for calculations in Microsoft Excel spreadsheets were discussed.

After being thorough with the various useful functions and calculation techniques of MS Excel, In this series of excel course you will learn the next vital function to master is the Conditional Formatting in Excel. 

What is Conditional Formatting?

This method allows you to easily add certain conditions to cells which meet a certain criteria. It is extremely useful to differentiate the data, highlight information and make the excel spreadsheet more presentable and easy to comprehend. It makes it easier to keep track of important variables in the huge amount of data that is presented on spreadsheets and it also makes it visually appealing. 

Excel has many conditions present in it. And what makes it a book for Microsoft Excel users is that you can easily use the formulas to create your own conditions. Below you will find easy instructions on how to use basic conditions provided in Excel. You can also click on our free demo video which is easy to follow and helps you pick up the tricks of conditions in a snap!

Checklist to use conditional formatting on Microsoft Excel

• Always select the data, the cells, for which you intend to use conditional formatting.
• In case the conditional formatting that you have applied on a cell range is not giving the expected results, check if any other conditional formatting has been applied to it.

How to use the highlight cells rule?

To highlight the data which is less than or greater than a chosen numeric value:

• Select the cell range.
• Go to the Home tab and click on Conditional Formatting.
• Choose  the Highlight Cells Rules from the dropdown menu.
• Select Greater than if you want values greater than a number or Lesser than if you want the data highlighted which is of lesser values than a number of your choosing.
• A box pops up on your screen. Type in the number as per your requirement. Click ok.
• The cells will get highlighted that have the value greater than or lower than the number of your choice.
• Use the Mange Rules from the drop down menu, in case you wish to apply multiple formatting rules to the same set of selected data.
• You have the option of Delete Rule, Edit Rule there. Select the option which you would like to.

How to use conditional formatting with dates data?

• While handling dates , select Conditional Formatting .
• Go to Date option.
• Here you will find an option to highlight dates which fell in the last one week, last month, which will fall the next month, etc. 
• You can choose whether you want to highlight the data with a certain colour or give it a border of a certain colour, among other things.

How to use conditional formatting with textual data?

• With textual data, select the text cells. Go to Conditional Formatting. 
• Select Highlight Cells rules.
• Under that, choose the option Text That Contains. Fill in the data or the word that you want to be highlighted. Click Ok.

Tip: Choose Custom option in case you wish to highlight the data in a different font or colour than the ones given in options. The borders can also be customised.

How to make the selected data more appealing?

You can use data bars, colour scales and icons  to correspond to specific variation in the data.

• Select the numeric value cell range.
• Click on Conditional Formatting.
• Choose the Data Based option.
• You will see various colour scales, icon sets available. Decide on one and click on it.Then click OK.

There is no better way to empower yourself and your working style than to use excel spreadsheets tricks and methods along with the short cuts mentioned in previous excel course blogs. In case you missed them, check out the links below.