7 Things To Do with Conditional Formatting in Excel

Microsoft Excel, quite a simple yet powerful tool. Loaded with many features from basic to advanced like Conditional Formatting, Ms Excel has now become an irresistible part of our daily work profiles.

 

Conditional Formatting 

A special tool that allows you to format your data with conditions wherein you don’t need to go through the complete data set for analysis and to identify a particular set of data.

Suppose you have an important data set. How would a simple table full of numbers on top of each other look? Confusing!

Now imagine the same table is visually formatted, wherein all the numbers are colour-coded.

Here in this post, we will discuss in brief about a few formatting features which will enhance your reports and skills. 

Let’s get started.

 

1. Highlight Duplicate Values

Suppose you need to identify a few duplicate values in a data set.

The first thing that comes to your mind is Count/Count IF formula. But trust me there is an easier way to do so without applying any formula.

You are just a few clicks away from the magic of Conditional Formatting.

Yes, you heard me right. Conditional Formatting.

Let’s Start.

 

  • First of all, select the Data Set, Column, Row or Complete table by pressing Ctrl+A.

Data

 

  • Then, go to the Home tab, and click Conditional Formatting.

Ribbon Conditional Formatting

 

  • Now, select the Highlight Cells Rules Duplicate values.

Ribbon Conditional Formatting

 

  • Here, you will get a dialogue box opened, Select Duplicate in the drop-down list and choose the kind of colour and font you wish to use for highlighting the duplicate values.

Ribbon Conditional Formatting

 

  • Click OK.

Conditional Formatting

 

Now, all the duplicate values are highlighted with the colour you choose. I chose the default combination of light and dark red.

Tip: – You can filter the data based on colour formatting by using the “Filter by Color” option.

 

 

2. Apply Data Bars

This option adds a visual appeal to your data and makes it easier to analyze the numbers.

Let us go through a simple example to understand it better. Here are two data tables with and without formatting:

Without formatting

With Formatting

                             

In Table 1 we need to go through each and every number to analyze the performance of different cities, but in Table 2 the data bars are self-explanatory.

Now, follow the steps below to apply data bars to your data:

  • First of all, select the column where you want to apply the Data bars.
  • Now, go to Conditional formatting Data Bars.

Data Bars

 

  • Here you will find two types of options to highlight your data Solid/Gradient Fill.
  • Click on either of the two and choose the colour relevant to your data set.

 

Solid fill applies colour uniformly into the cell whereas, in Gradient Fill, the colour gradually fades away towards the end.

Sample Gradient Solid Fill

 

More Rules:

In case you are not satisfied with Single colour data bars, hold on we have another interesting option called More Rules.

Data Bars

 

  • Here you can edit the Rule in Format Style from Single colour to 2-Color or 3-Colour. We will discuss this in brief in Section “Applying Color Scales” of this Article.

New Formatting Rules

 

 

3. Using Icon Sets

Still not impressed? there is something more interesting thing which you can do to your data i.e. Icons.

Yes, you can add icons to your cells representing the values.

Icon Sets Example

 

  • First of all, select the column where you want to apply the Icon Sets.
  • Now, go to Conditional formatting Icon Sets.
  • Here you have many options Directional, Shapes, Indicators and Rating.

Icon Sets

 

  • Select the icon of your choice and we are done.

 

You can change the scale of colour distribution according to your data by clicking on Conditional Formatting Icon Sets More Rules

Icon Sets

 

 

4. Applying Colour Scales

While applying the Colour Scales, Excel assigns a colour code to the highest and the lowest value. Other values are a blend of both the colours forming a pattern and visual effect.

This makes your data visualization better.

In case you have a large data set, your sheet might look colourful. In such a case you can choose your colour scale between 2-Scale or 3- Scale.

Also, the colour combination can also be changed depending upon the type of data.

Let’s go through an example.

Color Schemes 3 Color ScaleColor Schemes 2 Color Scale

 

Here are a few steps to apply colour scales: 

  • Select your data set and go to Conditional formattingColour Scales.
  • Here you will find inbuilt rules with different colour combinations.
  • Choose the combination that suits your data type.

Color Scale

 

For choosing the different colour scale, click on More Rules. Here you can customize your rule colour on the basis of certain Value, Percent, Formula or Percentile.

 

 

5. Using Top Bottom Rules

Till now, we have been discussing the formatting of the data set based on its value. Now there is something more to do.

Using Top Bottom Rules, you can identify the numbers based on some statistical conditions like identifying the top/Bottom numbers based on their value, percentages, Average.

Ribbon Top Bottom

 

Here you have below options to identify and format your numbers: 

  • Top/Bottom 10 items: This helps you to identify the top and bottom values among the data set. You will find that the count of 10 is editable and you can reduce or increase the number as per your requirement.

Top10

 

   Here we have a list of few students with marks obtained. Select the column with marks and go to Conditional Formatting Top/Bottom Rules Top 10 Items OK

Example Top 10

Example Bottom 10

 

  • Top/Bottom 10%: Top/Bottom 10% highlights the top/bottom 10% of the values within your data set.

Top 10%

Bottom 10%

 

  • Above Average: This helps you to highlight the values which are above the average value of the data set.
  • Below Average: This helps you to highlight the values which are below the average value of the data set.

 

 

6. Using Formulas to Highlight a Cell

Until now, we have been formatting cells based on some inbuilt options. But what if you have some specific criteria to identify or highlight a few cells.

We have the solution. It’s again Conditional Formatting.

Yes, you heard me right. We can highlight the cells based on some specific formulas as well.

  • First of all Select the Data or the column that contains your data.
  • Now, Go to Conditional FormattingNew Rule.

New Rule

 

  • A pop-up window appears.
  • Click on the “Use a formula to determine which cell to highlight” from the Select Rule Type menu.

New Rule Formula

 

  • Now define your formula in “Edit description rule” and choose to format as per your requirement.
  • Click Ok.

 

Here in our example, we have defined the passing marks of 55. Let’s apply the rule to highlight all marks above 55.

  • Select the Column B i.e MarksConditional FormattingNew Rule

Example Formula

 

  • Now, Select “Use a formula to determine what cells to highlight”

Formatting Rule Formula

 

  • Enter the formula “=B2>55” and click on Format.

Formatting Cells

 

  • Select the format and click ok.

Formula Example Result

 

 

7. Edit and Remove Conditional Formatting from a Cell

Once you have applied the formatting to the data, it becomes important to know the editing and deleting of the formatting.

Here we go:

For deleting the Conditional formatting 

    • First of all, Select the table or data range and Go to Conditional Formatting.
    • Now, from the drop-down menu, click Clear Rules.

 

  • Here you will have 2 options: Clear rules from Selected cells and Clear rules from Entire sheet.

Clear Rules

 

  • Now, choose among both the options and the formatting is removed.

 

Now, let’s begin with the Editing of the rules. This is quite useful when you have applied multiple rules in the same data table.

  • For Editing, Select the data table and then Click on Conditional Formatting.
  • Now, Select manage Rules.

Manage Rules

 

  • A Pop-up window “Conditional Formatting Rule Manager will be opened” with all the formatting rules you have applied to your data.
  • Now Select the rule you want to edit and click on “Edit Rule”.

Rules Manager

 

  • A pop-up window appears.

Edit Formatting Rule

 

  • Now, here you can customize the rule and click OK.

 

Related Article: Top 12 Essential Beginner Books for Algorithmic Trading

Aprajita Sharma

Aprajita is the Co-Founder of ExcelChamps. She has an MBA in Sales and Marketing and has been using Microsoft Excel for the last 8 years. Her journey started from learning a basic pivot table from Google which made her fall in love with Excel.

You may also like...