Overview
- Conditional formatting is a useful and powerful feature in Microsoft Excel that you must master
- We will analyze 5 handy conditional formatting tricks here and we'll show you how you can use them in your analysis projects.
Introduction
Let me share a brief history of one of my recent analytical projects.. I was working in Excel with a large dataset that had a lot of numerical characteristics (mostly sales figures for different products). I wanted to quickly highlight some values that any leadership team or decision maker would instantly consider..
This type of visualization or analysis is highly valued in the industry. If you can take a bunch of numbers and show exactly what you want to show your audience, that's where a business analyst, a data analyst or even a data scientist stands out from the rest. And Excel is the perfect tool for extracting and highlighting information using a function called conditional formatting..
I quickly moved on to the Conditional Formatting options, I chose the color scales I wanted and Excel instantly highlighted the highest and lowest sales numbers for each region and product. With just a few clicks (And an understanding of conditional formatting in Excel!!), You can easily prepare and present a powerful and effective analysis to your audience.
Personally, I use conditional formatting a lot, as it helps me stay organized and helps me understand the data much more intuitively.
But conditional formatting can seem a bit overwhelming if you are new to Excel. There are a lot of options to choose from and choosing one is not that simple.. So here, I have compiled a list of 5 Excel tricks to become a champion in conditional formatting. These tricks are easy to use for beginners, so even if you use Excel to record your expenses, they will definitely make you more productive.
I encourage you to check out the resources below if you are a beginner in Excel and business analysis.:
Table of Contents
- Conditional formatting trick n. ° 1: highlight cell values greater than average
- Conditional formatting trick n. ° 2: highlight multi-format sales data
- Conditional formatting trick n. ° 3: highlight entire row if condition is met
- Conditional formatting trick n. ° 4: highlight rows that have duplicate values
- Conditional formatting trick n. ° 5: highlight rows that have multiple conditions
Conditional formatting trick n. ° 1: highlight cell values greater than average
Let's start with an easy but powerful conditional formatting trick. As the name suggests, this helps us to highlight the cell values that satisfy a certain condition. Excel does not provide a simple option to highlight a cell based on a condition applied to another cell, so let's see how we can do this.
We will take a statement of the problem in which we are given the grades of the students who take the preliminary round of an exam. Students must score above the average grades of all students to advance to the next round. Our job is to highlight students who meet this condition.
We will solve this problem step by step, so let's get started.
Paso 1: Conditional Formatting Rules Manager
To start with the analysis, abra Conditional format -> Manage rules.
You are now in the Conditional Formatting Rules Manager. All your conditional formatting rules will be present here. Let's see how to make our first conditional formatting rule:
Paso 2: select a new rule type
To create a new conditional formatting rule, go to New rule. All types of rules will be present there. How we will create our own formula to select the column, we will choose “Use a formula to select which cells to format“:
Paso 3: enter custom formula
Finally, we will enter our custom formula to highlight cells in students whose values in the Round preliminary marks columns is more than the average of all student grades:
Paso 4: choose an appropriate format
Here, I choose to highlight student cells in verde whose grades are above average. It is up to you and you can also select any other formatting style:
Paso 5: select the desired cells to format
Select the column to which you want to apply the format. In this case, is he students column. press the request button and watch the magic unfold:
We have successfully completed the first problem statement!!
Conditional formatting trick n. ° 2: highlight multi-format sales data
In the above trick, we apply a single format rule. Now, let's see how we can apply multiple formats on our sheet taking another example.
Let's take a problem statement where we have sales data for the years 2019 Y 2020. This data is from a company that sells a variety of products. We must highlight the products that have increased sales with green and the products that have decreased sales with red. Let's start!
Let us begin:
Select the cells you want to highlight, in this case, Products 1 – Product 7 ($ D $ 4: $ D $ 10). After that, follow the steps 1 Y 2 in the previous section to open the Conditional Formatting Rules Manager.
Paso 3: enter the first rule
We will enter our first rule, namely, to highlight the product cells that have Sales_2020 but big that Sales_2019. We will use green fill to highlight these cells:
Paso 4: enter the second rule
Now is the time to apply the second rule, namely, highlight the product cells that have Sales_2020 less than Sales_2019. We will use red fill to highlight these cells:
Paso 5: apply the format
We are ready with the two formatting rules. Make sure you have selected the correct range of desired cells. Just hit Apply and you're done!! You have applied various formats!
Conditional formatting trick n. ° 3: highlight entire row if condition is met
In real world scenarios and analysis projects, you will find datasheets spread over many columns and rows. In these conditions, it is convenient to format the entire row instead of just one cell.
To do this, let's take a very interesting problem statement. The information of the passengers entering the Delhi airport is provided to us. We must highlight the history of people not wearing a mask so that further action can be taken against them.
We will start by applying the Steps 1 Y 2 in the first section to open the Conditional Formatting Rules Manager.
Paso 3: apply the formula
This is the easy part. Select the formula $ H5 = “No” and select the appropriate format. This will select all the clients that are not using a skin and format them as Red in this case:
Paso 4: select range and apply
Finally, we will select the desired cell range. How we need to highlight entire rows, we select all the cells and then apply the format:
Conditional formatting trick n. ° 4: highlight rows that have duplicate values
One of the most important steps in data analysis is detecting duplicate values in your data.. Excel provides an ultra-fast option to detect and visualize duplicate values in our data sets.
Let's delve into the statement of our problem. Here we have the order details from an e-commerce company. Our job is to highlight clients who have made multiple transactions. Let's see what is the fastest way to do it.
You already know the exercise, head to the step-by-step breakdown below.
Paso 1: select range
We will start by choosing a preferred range to which we want to apply our format, in our case, Customer identification:
Paso 2: highlight duplicate values
Then, we will do the mentioned highlighting of duplicate values. Go to Conditional format -> Highlight cell rules ->Duplicate values:
Paso 3: select an appropriate format
Two drop down menus will be provided to you. In the first drop-down menu, we will select “Double” since we need to format only the columns that have duplicate values, and then we'll select an appropriate highlighting format:
That is all!
Conditional formatting trick n. ° 5: highlight rows that have multiple conditions
Up to now, we have highlighted columns based on a single condition. Now, I want to take things to a higher level. We will apply multiple conditions and highlight the records accordingly.
Again we are going to return to an interesting problem statement. Here, we have a list of students in the last year of their graduation. University authorities must return security deposits to their students, but there is a problem: the deposit will be given only to those students who have received approval from all departments, namely, Library, Sports department, Y Laboratory.
We will start by selecting the entire range of cells and repeat the steps 1 Y 2 of the first section.
Paso 3: apply the conditions
According to our condition, we will make a custom formula. We will use the AND operator as we need to make sure that all departments have given their approval to the student.
We will use Green Fill as the highlight color here:
Paso 4: apply the format
Let's apply the format and we have completed our fifth problem statement. Impressive!
Final notes
In this article, We cover five Excel tricks for conditional formatting. Hope these tricks help you with your day-to-day niche tasks and save you a lot of time..
Have your own conditional formatting tricks for sharing? Or any other Excel trick, in general, you would like the community to know? Share them in the comment section below!!
