Conditional Formatting is a built-in feature in Excel and it enables you to visualize the data by adding some user-friendly formatting based on certain rule types. As you add some basic formatting to your data set to make it more appealing and colorful, like choosing the Font size, colors, headers etc, so when you want to choose or apply such formatting types based on some condition, and then it is called Conditional Formatting.
This feature in Excel enables you to view how a cell appears based on the value of that cell. For example, if you want to highlight a cell with a certain background color, say Red, only if the value in that cell is greater than a certain set value. In Excel, it is very easy to use conditional formatting because it contains some built-in formatting rules and styles, or you can create your own condition based on your rule or condition type.
Here you are going to use Excel built-in conditional formatting rules and styles for your data set. After opening your data sheet in Excel and selecting the cells range to apply conditional formatting, on the Home tab, in the Styles group, click on Conditional Formatting menu.
Now you can see the complete list of conditional formatting menu and can choose one of the built-in conditional formatting and styles options to apply.
Using Built-in Conditional Formatting Rules
Built-in conditional formatting rules section has two menu options to choose from to highlight cells as per your requirement.
- Highlight Cells RulesYou can conditionally highlight your data range by choosing one of the options from Highlight Cells Rules menu or you can create more rules to apply. This menu has some preset conditional formatting rules set and formatting types to apply with, which are as follows:
You need to choose one of the preset rules and formatting types to apply conditional formatting.
- Greater Than – Highlight cells that are greater than a value you set.
- Less Than – Highlight cells that are less than a value you set.
- Between – Highlight cells that have values between values you set.
- Equal To – Highlight cells that are exactly equal to a value you set.
- Text that Contains – Highlight cells that contain strings of text.
- A Date Occurring – Highlight cells based upon the date in a cell.
- Duplicate Values – Highlight cells which contain duplicate data values.
For example, if you want to highlight cells that are greater than a value you set of 20,000 in a selected data range, then you will choose Greater Than rule option and will enter 20,000 in a popup window and will choose a formatting type from the drop-down list as shown below.
It will highlight all the cells with “Light Red Fill..” that have value great than set value of 20,000.
- Top/Bottom RulesYou can easily conditionally highlight your data by choosing one of the Top/Bottom Rules options or you can create more rules to apply. This menu also has following preset conditional formatting rules set and formatting types to apply with.
For example, if you want to highlight cells in a particular month which values are above average, then you will select your data range and choose Above Average option from preset rules and choose to format with an option from popup window as shown below.
It will highlight all those cells with Light Red Fill…. that have values above the average of the selected data range.
Using Built-in conditional formatting Styles
If you want to try some more attracting conditional formatting options then Excel as some built-in styles to use for this purpose, like Data Bars, Color Scales, and Icons Sets.
- Data BarsYou can quickly place some colorful Data Bars inside of data cells to represent the size of your data in the selected data range. First, you need to select your data range and then click on Data Bars menu and choose one of the options to apply as shown below.
- Color ScalesColor scales highlight the highest and lowest values representing with distinguished color patterns in a selected data range. After selecting the data range, you need to choose one of the options from Color Scales menu. The shade of the color represents the value in the cell as shown below.
- Icon SetsThe Icon sets show changes in your data. They include arrows, flags, bars, and other shapes to represent the change in your data. Each icon represents a value in the cell as shown below.
These Icon sets change when the data changes in the selected range. When the Change% figure changes, Excel will automatically change the icon set accordingly.