There are a lot of times when you have a set of data in Excel, and you need to highlight certain figures, numbers, or text based on their values.
This could be the case when you need to highlight the range of numbers larger or smaller than some certain value, or anything similar.
Whatever the case may be, the perfect tool to manipulate data in this way is Conditional Formatting.
For this example, we created a table of banking loans, with columns that describe loan amount, the month of approval, and loan duration.
Our table has 40 rows in total, but we presented only a couple of them, just for the preview.
Now, suppose that somebody says to us that he/she wants all the loans that were approved in May highlighted in green.
For this, we can use Conditional Formatting.
Conditional Formatting is a feature in many spreadsheet applications that allows you to apply specific formatting to cells that meet certain criteria.
Conditional Formatting is located in the Home tab, in the subtab Styles.
This Excel function has some pre-defined data formatting sets:
- Data Bars are horizontal bars added to each cell, much like a bar graph.
- Color Scales change the color of each cell based on its value. Each color scale uses a two- or three-color gradient.
- Icon Sets add a specific icon to each cell based on its value.
Furthermore, we can define our own rules and define virtually anything we want. We have more predefined rules that can help us as well and they are located in Highlight Cells Rules and Top/Bottom Rules.
Format Cells Based on Their Content
For our example, we will select column B, go to Conditional Formatting– Highlight Cells Rules– Text that Contains:
Once we click on that, we will be presented with a window in which we will input our text (in this case May), and choose the fill of that text. We will choose green fill with dark green text. As seen in the picture below, we can always custom-format our range and choose the colors that we like.
When we click OK, our changes will be implemented in the table:
Format Cells Based on the Condition
Now let us assume that we want to highlight only the loans that were approved for a period longer than 36 months. For this, we will select our C column, and then go to Conditional Formatting- Highlight Cells Rules– Greater Than.
We will then input the number 36 in our window and choose to fill these cells with yellow and with dark yellow text.
Our original table now looks like this:
The one thing that you might have noticed is that Excel also changed the format of our first row in column C, although it is not a number. Excel will always do this when there are cells with text and we want to format the cells based on some number condition. So, be careful about that.
Manipulate the Formatting
If you want to change your formatting rules, for whatever reason, you have to go to Conditional Formatting– Manage Rules. When you select it, a window will appear, as in the picture below. You can choose to manage rules either in the current selection or your current worksheet.
If you want to clear all the rules, you just go to Conditional Formatting– Clear Rules. You can clear the rules either from selected cells or from the entire sheet.