Last updated on April 16th, 2023
Estimated reading time: 7 minutes
If you are new to excel spreadsheet read our article on VLOOKUP Function in excel – Full Stack Tutorials Hub .
What is Conditional Formatting in Excel?
Conditional formatting is prebuilt function in excel which highlight certain values or make particular cells easy to identify. This changes the appearance of a cell range based on a specified condition (or criteria). You can use conditional formatting to highlight cells that contain values which meet a certain condition. The conditions are rules based on specified numerical values or matching text.
How to Open Conditional Formatting?
- Open the Conditional Formatting dialogue box : In Excel Sheet Click on Home > Conditional Formatting > Select Any Type of Conditional Formatting / New Rule)
- Keyboard Shortcut Key : Alt + O + D.
- In the dialogue box, select ‘Use a Formula to determine which cells to format’ dialogue box.
Types of Conditional Formatting:
There are 5 types of conditional formatting visualizations available with different built-in conditions and appearances :
- Background Color Shading (of cells)
- Foreground Color Shading (of fonts)
- Data Bars
- Icons (which have 4 different image types)
- Values
How to Apply Conditional Formatting based on text in a cell:
- Select the cells you want to apply conditional formatting to. Click the first cell in the range, and then drag to the last cell.
- Click HOME > Conditional Formatting > Highlight Cells Rules > Select Any Type of Conditional Formatting / New Rule
- Select the color format for the text and click OK.
1. Background Color Shading (of cells)
In example, the Profit values of each product is formatted with a Color Scale:
- First : Select the range of Profit values C3:C15
- Second : Click on the Conditional Formatting icon
in the ribbon, from the Home menu - Third : Select the Color Scales
from the drop-down menu
- There are 12 Color Scale options with different color variations.
- The color on the top of the icon
will apply to the highest values.
- Forth : Click on the “Green – Yellow – Red Color Scale” icon
- Result : Now, the Profit value cells will have a colored background highlighting:
- Dark green is used for the highest values, and dark red for the lowest values.
- Milk has the highest Profit value (100) and Milk Sweet has the lowest Profit value (35).
- All the cells in the range gradually change color from green, yellow, orange, then red.
2. Data Bars
In example, the Profit values of each product is formatted with a Data Bars :
- First : Select the range of Profit values C3:C15
- Second : Click on the Conditional Formatting icon in the ribbon, from the Home menu
- Third : Select the Data Bars
from the drop-down menu
- There are 12 data bar options with different color variations.
- Longer bar size will apply for highest value
and smaller will apply to the lowest value. Bar Size will adjust automatically as per values.
- Forth : Click on the “Light Blue Data Bar” icon
- Result : Now, the Profit value cells will have a colored bars. Longer is used for the highest values, and Smaller for the lowest values. All the cells in the range gradually change the size of the bars from Longer to smaller as per values.
3. Icons Example (which have 4 different image types)
In example, the Profit values of each product is formatted with a Icons :
- First : Select the range of Profit values C3:C15
- Second : Click on the Conditional Formatting icon in the ribbon, from the Home menu
- Third : Select the Icon Sets from the drop-down menu
- There are 20 icon sets options with different colors and icon type variations.
- The icon type will change icons as per selected icon set for highest to lowest values.
- Forth : Click on the “5 Arrows (Colored)” icon
- Result: Now, the Profit value cells will have a Icons sets as per highest to lowest values.
Next Steps
Refer our article on How to Remove / Clear / Delete Conditional Formatting in Excel? – Full Stack Tutorials Hub .