How to Use Microsoft Excel Conditional Formatting?

Microsoft Excel has many wonderful features unlike other spreadsheet software which allow users to make their data organization work more expressive and efficient. Conditional Formatting in Microsoft Excel is one of those features. With the help of this feature, you can format cells with values in them, or the formulas embedded in cells with values.

Basically, conditional formatting feature allows users to highlight certain values under specific conditions or parameters which enable the searching of those values in large extended sheets more convenient and quicker.

For example, you want to highlight certain values which exceed the value you set as a benchmark in yellow; or all the dates prior to the current month in green or to use a shape or rating icon to locate the duplicate values. The options are endless, and so are the multiple applications this feature supports.

Here in this article we would specifically discuss about how to format cells with values greater or less than certain numbers in a list, or specific texts and dates in a list.

Continue reading to know more about them in details.

#1. Formatting cells with Dates within a range:

In this section, we would demonstrate you to find out dates within certain ranges. Kindly go through the steps mentioned to know it in details:

Step 1.Suppose the list of dates are mentioned in the column (F).

Step 2.Highlight the column (F) and then go to the “Home” tab.

Step 3.Select “Conditional Formatting” and click on it.

Step 4.From the drop down menu, choose “Highlight Cells Rules” and then click on “A Date Occurring”.

Step 5.”A Date Occurring” dialog box would appear.

Step 6.In the field box, enter the date range for example, Today, Yesterday, Tomorrow, Next Month, This Month, etc. and click ‘Ok”.

Step 7.Excel would automatically display the date range you selected in light red with dark red font.

#2. Formatting Values with GREATER THAN or LESS THAN instructions:

In this section, we would discuss about how to locate certain values which are either greater or less than the reference value you have set for any requirement.

• GREATER THAN Instruction:

Step 1.Run your excel software and open a sales totals spreadsheet, containing the sales total values for the first four months.

Step 2.First; highlight the column you want to search in, for example the JANUARY column.

Step 3.Go to “Home”, then “Conditional Formatting” and click on “Highlight Cells Rules” from the drop down box.

Step 4.Click on “Greater Than” option and a “Greater Than” dialog box would appear.

Step 5.In the field box, enter a number for example 5000 and click on “OK”.

Step 6.You would note that all the sales values in JANUARY column are highlighted in light green with dark green font.

• LESS THEN Instruction:

Step 1.Just like the Greater Than instruction, click on the “Less Then” option under the “Highlight Cells Rules” menu and again a “Less Than” dialog box would appear.

Step 2.Type in any value for example, 5000 again and click “OK”. Surprisingly all the sales total values below $5000 will be highlighted in red with dark red font.

#3. Formatting cells with Specific Texts:

In the similar manner like the previous example, you can highlight certain texts with colored fonts; this would help the user to find their required words or texts spontaneously in long lists. The methods are mentioned in steps below,

Step 1.Now in order to highlight texts, you must choose the column which contains texts or phrases alone, for instance highlight the column containing names of sales staff.

Step 2.Go to “Home” and select “Conditional Formatting”.

Step 3.From the drop down menu, select “Highlight Cells Rules” and then “Text That Contains”.

Step 4.On clicking the above mentioned option, a “Text That Contains” dialog box would pop up.

Step 5.In the field box under the “Format cells that contain the text” type in the phrase you want to highlight, for example Smith.

Step 6.You would see that all the phrases containing Smith in the Column (A) would be highlighted in red with dark red font.

NOTE: One of the interesting fact about Conditional Formatting feature is that the color that highlights the texts, phrases and everything can be changed from the menu list with down arrow followed with the symbol:-

Conclusion:

As you have seen that conditional formatting has got many applications to put in your spreadsheet data which makes your job much easier than ever before.

No matter you extended your datasheet is, it won’t be that difficult for you to locate any specific phrases, texts, dates, number values among the piles of data in rows and column. Moreover, if you want to make your own custom conditional formats, that too can be accomplished as per your requirements. The feature gives the liberty to enable users to customize their own conditional formats like they can choose phrases according to certain alphabets, or numbers within certain ranges and many more.

Indeed there is no end to their applications yet it has been demonstrated in the easiest possible way that even a novice can do the job by following the directions.

If you have found the article reliable and helpful please do share it on social media and subscribe to our website for more information.