Conditional Formatting in Excel
Excel Conditional Formatting is a handy feature that helps you visualize data and understand your spreadsheets better. Consider a worksheet that has tens of thousands of rows of data. Simply by looking at the raw data – patterns and trends would be very difficult to spot. By applying formatting rules to cells, anyone can spot patterns easily and changes in your data. With a range of preset formatting options and customization choices, conditional formatting provides flexibility as well as efficiency in analyzing and data presentation.
What is Conditional Formatting in Excel
Conditional Formatting is a feature in an Excel spreadsheet. It is used to maintain the status of the result easily. It is most often used as color-based formatting to highlight, emphasize, or differentiate among data and information stored in an Excel spreadsheet.
When it comes to applying alternative forms to data that fit particular criteria, Excel conditional formatting is a highly useful feature. It can make it easier for you to draw attention to the key details in your spreadsheets and quickly identify differences in cell values.
Types of Conditional Formatting Visualisation
You can use preset rules, including Color scales, Data Bars, Icon Sets, Sort filters, etc. to conditionally format your data, or you can construct your own rules that specify when and how the selected cells should be highlighted.
Data Bars
Similar to a bar graph, data bars are horizontal bars that are added to each cell
Color scales
Alter each cell’s color depending on its value. A two- or three-color gradient is used for each color scale
Icon Sets
Based on the value of each cell, icon sets assign a distinct icon to each cell.
Where is Conditional Formatting in Excel
Navigate to the Home tab, access the Style group, and select the dropdown arrow adjacent to Conditional Formatting. Then, opt for Top/Bottom Rules. It’s important to note that you can find Conditional Formatting in the same location across Excel versions from 2010 to Excel 365.
Quick Tip: Go to Home tab > Select Conditional formatting > Select Rule
How to Use Conditional Formatting in Excel
Step 1: Select the Cells
Select the cells you want to format in the Spreadsheet
Step 2: Click on Conditional Formatting
Click on Conditional Formatting, On the Home tab, in the Styles group
Step 3: From a Set of Preset Rules, Pick the Required One
From a set of preset rules, pick the one that best serves your needs
Step 4: Enter the Value and Select the Chosen Format from the Drop-Down List
Enter the value in the box on the left of the dialogue box and select the chosen format from the drop-down list on the right (with Default Light Red Fill with Dark Red Text)
Likewise, you have the flexibility to choose a different rule type that better suits your data. For instance, you can opt for:
- Greater than or equal to
- Between two values
- Text that contains specific words or characters
- Date occurring in a certain range
- Duplicate values
How to Use a Preset Rule with Custom Formatting
You can select any other color for the background, text, or borders of the cells if none of the standard layouts appeals to you. This is how:
Step 1: Pick Custom Format
Pick Custom Format, In the preset rule dialogue box, from the drop-down list on the right
Step 2: Edit the Format Cells and Click Ok
Change between the Font, Border, and Fill tabs in the Format Cells dialogue window to select the preferred font style, border style, and background color, respectively, click OK
Step 3: Click OK to Apply the Custom Formatting of your Choice
How to Create a New Conditional Formatting Rule in Excel
You can create a new Conditional Formatting Rule in Excel from scratch. To do so, follow these steps:
Step 1: Select the Cell or Cells you Want to Format
Step 2: Navigate to the Home Tab and Select Conditional Formatting and Click New Rule
Step 3: Select the Rule Type and Click Ok
Now select the rule type, in the New Formatting Rule dialog box
Step 4: Click the Format button, Choose the Color, and Click Ok
Step 5: Preview Result
How to Edit Conditional Formatting Rules in Excel
To make any changes in an already existing Formatting Rule in Excel, follow these steps:
Step 1: Select the Cells
Select any cell to which a rule already applies.
Step 2: Navigate to the Home Tab and Click on Manage Rules under Conditional Formatting
Step 3: Select the Rule you Want to Edit
This will open the Rules Manager dialog box, select the rule you want to modify, then Click on Edit Rule.
Step 4: Make the Changes and Click Ok
Make the required changes in the Edit Formatting Rule dialogue window, Click on Ok
Excel Conditional Formatting Formula Examples
Here are some Scenario-Based Examples of Conditional Formatting in Excel:
How to Find Duplicates in Excel
Step 1: Select the Cells in which you want to Find Duplicates
Select the cells where you want to identify duplicate values. For example: select the column TOT as shown in the figure.
Step 2: Navigate to the Home Tab and Click on Cell Formatting
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: Choose the Color
Select the Fill option in the tab and choose the background color and click ok.
Step 4: Preview Duplicates
Now the duplicate values as shown in the figure.
How to Highlight Cells with Value Greater or Less than a Number in Excel
Follow the below steps for highlighting cells with a value greater or less than a given number:
Step 1: Select the Cells
Select the cells that you want to highlight with Greater or less than a number.
Step 2: Navigate to the Home Tab and Click on Conditional Formatting
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: Preview Result
In the below figure, the values with green color indicate greater values and the values with red color indicate fewer values.
How to Highlight Top or Bottom values in Excel
Step 1: Select the Cells where you want to Highlight Top and 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 .
Step 2: Navigate to the Home tab and Click on the Conditional Formatting
Step 3: Choose Top/Bottom Cells
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.
Step 4: Preview the Result
In the below figure, the red color indicates the top 5 values in rank.
How to Copy Conditional Formatting to Another Cell in Excel
You won’t need to start over when applying a conditional format you’ve already established to different data. To transfer the current conditional formatting rule(s) to another data collection, just utilize Format Painter.
Step 1: Choose the cell whose Conditional Formatting you want to Copy
Step 2: Click on Format Painter under Home
Click on the first cell in the range you want to format, then drag the paintbrush down to the last cell to paste the copied formatting
How to Remove Conditional Formatting in Excel
There are two easy ways to delete conditional formatting rules
Step 1: Select the BestChoice after choosing the desired cell range
Step 2: Click Conditional Formatting then Select Clear Rules
Remove Conditional Formatting Rules using Conditional Formatting Rules Manager
Step 1: Select the Rule
Step 2: Navigate to the Home Button and Select Conditional Formatting
Step 3: Select Conditional Formatting Rules Manager and Select Delete Rule
Under the Conditional Formatting Rules Manager
Keyboard Shortcut to Remove Conditional Formatting
To remove Conditional formatting rules from the selected range of cells use ALT + H + L + C + S
Note: To use the keyboard shortcut provided above, begin by highlighting the cells from which you wish to remove the Conditional Formatting. Afterward, press the designated keys.
Excel Conditional Formatting Best Uses
- Highlighting Top and Bottom Values: You can use Conditional Formatting to highlight the highest and lowest values in a Dataset so that you can easily identify significant data points.
- Data Bars and Colors Scales: Represent data visually using data bars or color scales to create intuitive maps, making patterns and trends more apparent.
- Icons Sets: Apply Icon Sets to cells, by using symbols such as arrows or checkmarks, to quickly assess data against preset conditions.
Advanced Conditional Formatting Techniques
- Formulas in Conditional Formatting: You can use custom formulas to create complex conditions for formatting. This can help you for more precise control over the formatting rules.
- Cell Reference in Conditional Formatting: You can apply Conditional Formatting dynamically based on values in other cells using Cell References.
- Managing Rules: You can efficiently Manage and organize your conditional formatting rules through the “Conditional Formatting Rules Manager”.
FAQs
Here are some of the most frequently asked questions on Excel Conditional Formatting
What are the 3 conditional formatting options?
Three categories have been established for Conditional Formatting in Excel.
- Similar to a bar graph, Data bars are horizontal bars that are added to each cell
- Color scales alter each cell’s color depending on its value. A two- or three-color gradient is used for each color scale
- Icon Sets, based on the value of each cell, icon sets assign a distinct icon to each cell
How to Copy Conditional Formatting to other cells in Excel?
Yes, we can copy Conditional Formatting to other cells in Excel. There are multiple ways to copy the conditional formatting to other cells, such as –
- Simple copy-paste
- Copy and paste conditional formatting only
- Using the format painter
How do I remove Conditional Formatting from cells in Excel?
To remove the selected range conditional formatting, please follow the below-mentioned steps:
- Select the range that you want to remove the conditional formatting from
- Click Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells
Can I use icons or data bars in Conditional Formatting?
Yes, you can use Icons or Data bars in Conditional Formatting in Excel. To use either of these, follow the below steps:
- Select the range that you want to apply the conditional formatting to
- Click on Conditional Formatting on the Home Tab
- Point to Data Bars, and choose from gradient fill or solid fill or Point to Icon sets and choose your desired icons
How to apply Conditional Formatting based on another cell’s value in Excel?
You must use a formula if you wish to format an entire row depending on the value of a single cell or apply conditional formatting based on another cell.
Contact Us