Microsoft Excel, quite a simple yet powerful tool. Loaded with many features from basic to advanced like Conditional Formatting. Before we dig further into conditional formatting, make sure to combine excel files first if you have multiple excel files. 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.
- Then, go to the Home tab, and click Conditional Formatting.
- Now, select the Highlight Cells Rules → Duplicate values.
- 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.
- Click OK.
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:
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.
- 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.
More Rules:
In case you are not satisfied with Single colour data bars, hold on we have another interesting option called More Rules.
- 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.
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.
- 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.
- 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
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.
Here are a few steps to apply colour scales:
- Select your data set and go to Conditional formatting→Colour Scales.
- Here you will find inbuilt rules with different colour combinations.
- Choose the combination that suits your data type.
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.
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.
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
- Top/Bottom 10%: Top/Bottom 10% highlights the top/bottom 10% of the values within your data set.
- 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 Formatting→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.
- 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 Marks→Conditional Formatting→New Rule
- Now, Select “Use a formula to determine what cells to highlight”
- Enter the formula “=B2>55” and click on Format.
- Select the format and click ok.
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.
- 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.
- 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”.
- A pop-up window appears.
- Now, here you can customize the rule and click OK.
Related Article: Top 12 Essential Beginner Books for Algorithmic Trading
