Excel Conditional Formatting (with Examples)

Take into account a worksheet that has tens of 1000’s of rows of knowledge. Just by trying on the uncooked information – patterns and traits can be very tough to identify. Conditional formatting presents an additional methodology of visualizing information and enhancing worksheet comprehension, very like charts.

What’s Conditional Formatting in Excel?

Conditional Formatting is a function in an Excel spreadsheet. It’s used to simply preserve the standing of the outcome. It’s most frequently used as color-based formatting to spotlight, emphasize, or differentiate amongst information and knowledge saved in an Excel spreadsheet.

In terms of making use of various varieties to information that matches specific standards, Excel conditional formatting is a extremely helpful function. It will possibly make it simpler so that you can draw consideration to the important thing particulars in your spreadsheets and rapidly establish variations in cell values.

Sorts/Conditional Formatting Presets

You should use preset guidelines, together with Shade scales, Knowledge Bars, Icon Units, Type filter, and many others. to conditionally format your information, or you possibly can assemble your individual guidelines that specify when and the way the chosen cells ought to be highlighted.

• Knowledge Bars, Just like a bar graph, information bars are horizontal bars which might be added to every cell

• Shade scales alter every cell’s coloration relying on its worth. A two- or three-color gradient is used for every coloration scale

• Icon Units, based mostly on the worth of every cell, icon units assign a definite icon to every cell.

The place To Discover Conditional Formatting in Excel

Conditional formatting is obtainable from Excel 2010 by Excel 365 in the identical location.

Residence tab > Types group > Conditional formatting.

The way to Use Conditional Formatting in Excel

Steps to make the most of Excel conditional formatting are as follows:

Step 1. Choose the Cells

Choose the cells you need to format within the Spreadsheet

Step 2. Click on on Conditional Formatting

Click on Conditional Formatting, On the Residence tab, within the Types group

Step 3. From a Set of Preset Guidelines, Decide the Required One

From a set of preset guidelines, choose the one which greatest serves your wants

Step 4. Enter the Worth and Choose the Chosen Format from the Drop-Down Checklist

Enter the worth within the field on the left of the dialogue field and choose the chosen format from the drop-down record on the appropriate (with Default Gentle Purple Fill with Darkish Purple Textual content)

Different rule sorts that could be acceptable to your information, reminiscent of:

• Larger than or equal to
• Between two values
• Textual content that incorporates particular phrases or characters
• Date occurring in a sure vary
• Duplicate values

The way to Use a Preset Rule with Customized Formatting

You may choose another coloration for the background, textual content, or borders of the cells if none of the usual layouts appeals to you. That is how:

Step 1. Decide Customized Format, Within the preset rule dialogue field, from the drop-down record on the appropriate 

Step 2. Change between the Font, Border, and Fill tabs within the Format Cells dialogue window to pick out the popular font type, border type, and background coloration, respectively, click on OK

Step 3. Click on OK to use the customized formatting of your alternative

The way to Create a New Conditional Formatting Rule in Excel

You may create a brand new Conditional Formatting Rule in Excel from scratch. To take action, observe these steps:

Step 1. Choose the cells to be formatted and click on Conditional Formatting > New Rule.

Step 2. Choose the rule kind, within the New Formatting Rule dialog field

Step 3. Click on the Format button, and select the colour you need to fill.

Step 4. Click on OK and your New conditional formatting is completed!

The way to Edit Conditional Formatting Guidelines in Excel

To make any modifications in an already present Formatting Rule in Excel, observe these steps:

Step 1. Choose any cell to which a rule already applies, Click on on Handle Guidelines beneath Conditional Formatting

Step 2. This can open the Guidelines Supervisor dialog field, choose the rule you need to modify, then Click on on Edit Rule. 

Step 3. Make the required modifications within the Edit Formatting Rule dialogue window, Click on on Okay 

Excel Conditional Formatting Examples:

Some State of affairs-Based mostly Examples of Conditional Formatting in Excel:

State of affairs 1. Figuring out Duplicates

Steps to figuring out duplicate values:

Step 1: Choose the cells the place you need to establish duplicate values. For instance: choose the column TOT as proven within the determine.

Step 2: Click on on the Conditional Formatting and click on on new guidelines. Now a window seems, choose ‘format solely distinctive and duplicate values’ and click on on the format.

Step 3: Choose the Fill possibility within the tab and select the background coloration and click on okay.

Step 4: Now the duplicate values as proven within the determine.

State of affairs 2. Cell Highlighting with Worth Larger or Lower than a Quantity

Observe the under steps for highlighting cells with a price higher or lower than a given quantity:

Step 1: Choose the cells that you just need to spotlight with Larger or lower than a quantity.

Step 2: Click on on the conditional formatting and click on on new guidelines. Now choose “Format all cells based mostly on their values“. Within the ‘Minimal’ and select the worth you need to establish the lesser values and select the colour. Equally, Within the Most and select the worth you need to establish the lesser values and select the colour. And click on okay. 

Step 3: Within the under determine, the values with inexperienced coloration point out higher values and the values with crimson coloration point out fewer values.

State of affairs 3. Highlighting High or Backside N Objects

To spotlight the highest and/or the underside N objects choose the cells the place you need to spotlight high and backside N objects and click on on the conditional formatting->High/backside cells->High 10 Objects. Select a price in format cells that rank on the high and select the format and click on okay.

Within the under determine, the crimson coloration signifies the highest 5 values in rank.

The way to Copy Excel Conditional Formatting

You received’t want to start out over when making use of a conditional format you’ve already established to completely different information. To switch the present conditional formatting rule(s) to a different information assortment, simply make the most of Format Painter.

Step 1. Select the cell whose conditional formatting you need to duplicate

Step 2. Click on on Format Painter beneath Residence

Step 3. Click on on the primary cell within the vary you need to format, then drag the paintbrush right down to the final cell to stick the copied formatting

Step 4. To cease utilizing the paintbrush, press Esc.

The way to Delete Conditional Formatting Guidelines

There are two straightforward methods to delete conditional formatting guidelines

1. Select the only option after deciding on the specified cell vary and clicking Conditional Formatting > Clear Guidelines

2. Choose the rule and click on the Delete Rule button, Underneath the Conditional Formatting Guidelines Supervisor

FAQs on Excel Conditional Formatting

Listed below are a few of the most steadily requested questions on Excel Conditional Formatting

1. What are the three conditional formatting choices?

Three classes have been established for Conditional Formatting in Excel.

• Just like a bar graph, Knowledge bars are horizontal bars which might be added to every cell
• Shade scales alter every cell’s coloration relying on its worth. A two- or three-color gradient is used for every coloration scale
• Icon Units, based mostly on the worth of every cell, icon units assign a definite icon to every cell

2. Can I copy Conditional Formatting to different cells in Excel?

Sure, we are able to copy Conditional Formatting to different cells in Excel. There are a number of methods to repeat the conditional formatting to different cells, reminiscent of – 

• Easy copy-paste
• Copy and paste conditional formatting solely
• Utilizing the format painter

3. How do I take away Conditional Formatting from cells in Excel?

To take away the chosen vary conditional formatting, please observe the below-mentioned steps:

• Choose the vary that you just need to take away the conditional formatting from
• Click on Residence > Conditional Formatting > Clear Guidelines > Clear Guidelines from Chosen Cells

4. Can I take advantage of icons or information bars in Conditional Formatting?

Sure, you should use Icons or Knowledge bars in Conditional Formatting in Excel. To make use of both of those, observe the under steps:

•  Choose the vary that you just need to apply the conditional formatting to
• Click on on Conditional Formatting on the Residence Tab
• Level to Knowledge Bars, and select from gradient fill or stable fill or Level to Icon units and select your required icons

5. Can I apply Conditional Formatting based mostly on one other cell’s worth in Excel?

You should use formulation in the event you want to format a whole row relying on the worth of a single cell or apply conditional formatting based mostly on one other cell.