Conditional Formatting in Excel
Conditional Formatting is a feature in an Excel spreadsheet. It is used to easily maintain the status of the result. It is most often used as color-based formatting to highlight, emphasize, or differentiate among data and information stored in a spreadsheet.
Types of Conditional Formatting:
Attention reader! Don’t stop learning now. If you are an Excel beginner (or an intermediate) and want to learn Excel, Geeksforgeeks brings the perfect course for you to start, Diving Into Excel
- Data bars
- Icon sets
- Sort filter, etc.
Steps to identifying duplicate values:
Step 1: Select the cells where you want to identify duplicate values. For example: select the column TOT as shown in the figure.
Step 2: Click on the Conditional Formatting and click on new rules. Now a window appears, select ‘format only unique and duplicate values’ and click on the format.
Step 3: Select the Fill option in the tab and choose the background color and click ok.
Step 4: Now the duplicate values as shown in the figure.
2. Cell Highlighting with Value Greater or Less than a Number:
Follow the below steps for highlighting cells with a value greater or less than a given number:
Step 1: Select the cells where you want to highlight with Greater or less than a number.
Step 2: Click on the conditional Formatting and click on new rules. Now select ‘Format all cells based on their values. In the Minimum and choose the value you want to identify the lesser values and choose the color. Similarly, In the Maximum and choose the value you want to identify the lesser values and choose the color. And click ok.
Step 3: In the below figure the values with green color indicate greater values and the values with red color indicates fewer values.
3. Highlighting Top or Bottom N Items:
To highlight the top and/or the bottom N items select the cells where you want to highlight top and bottom N items and click on the conditional formatting->Top/bottom cells->Top 10 Items. Choose a value in format cells that rank at the top and choose the format and click ok.
In the below figure ,red color indicates the top 5 values in rank.