Excel Conditional Formatting
If you are diving into Data Analysis, conditional formatting in Excel is an amazing tool that you can use today.
Conditional formatting asks Excel to change the color, font or whole look of a cell based on a set of conditions and rules you need.
Let’s get started, in this example we are taking a standard budget and we are going to show you the different ways that you can quickly filter information by conditional formatting.
We are using Excel in Office 365 but this is available in versions In this budget, let’s say that I want to find expenses greater than 1500
Highlight the area quickly by clicking in cell B9 >> control shift right >> control shift down
Then go to the Home Tab, click on Conditional formatting towards the right, highlight cell rules >> Greater than 1500, you can select the highlight color, which is yellow with Dark Yellow Text, press OK
To clear the formatting go to conditional formatting >> clear rules from the selection (if you only need to clean a small portion of your worksheet or the entire sheet.
You can also check less than, equal to, and between 2 values, for example, let’s search expenses between 1000 and 1500. This time I am going to format the highlighted cells by going to Custom format, leave the same font that you can change here. Instead, we are going to the Fill Tab, pick a color, and even a gradient fill effect with 2 colors and shading styles, and press OK two times.
These two next examples have been very useful for us when cleaning up data, first is the text that contains, by just typing marketing in this case, we then choose the green fill shading and click ok.
If you are enjoying this video do not forget to like and subscribe to help us create more free content about technology, thank you
And the next one is the duplicate values, highlight the cells, this time I will press ctrl+a to select all, and let us see what is double.
Ok so Insurance is entered twice but let us say that I only want duplicates that are text only
So now let’s use conditional formatting based on a formula. In other words, if the text of any row is duplicated, I am highlighting only those rows.
First Excel needs to count by this condition using a Countif function, let’s first test it in a blank column and then we’ll turn it into a condition.
The count if has two parameters or questions for me: where am I counting and what am I looking for.
=COUNTIF($A$9:E20,$A9)
So let’s use a blank column and type =countif and then A9, we are using the absolute reference of putting dollar signs on the column because we want to enforce the counting on the column, and keep it steady, then colon E20 because we want to highlight in the entire row, not just the column, then we need criteria or what’s the condition by typing comma $A9, notice that there is no dollar sign on the 9 because when we drag down this formula, we need the rows to change and not stay in A9 only.
Now let’s add specify that we only want to highlight values that show up more than once by typing >1 so the final formula should look like
=COUNTIF($A$9:E20,$A9)>1
You can test it out first, by inserting a test column, copy and paste it there and auto-fill the rest of the rows so it looks like this. We highlighted in green the True values so you can see better that the formula works and is catching the Insurance and Marketing duplicates.
To finish the formatting by formula first copy the cell in J12, then go to conditional formatting,
And paste this entire formula as follows:
Conditional Formatting >> New Rule >> Use a formula option, then paste the formula we just copied, now go to format to pick any color you want and press Ok and ok.
You can also go to the manager to create a new rule, change, delete and duplicate an existing one. And you can select the priority level by clicking on the arrows to the right.
You can also experiment with the Data Bars, which are quick ways to show trends, then color scales to quickly see what the highest or lowest data points are, or the icon sets that you probably have seen in financial statements or stock information.
excel conditional formatting highlight row based on date
excel online conditional formatting entire row
conditional formatting to highlight row google sheets
conditional formatting not highlighting entire row
apply conditional formatting to entire column
excel conditional formatting based on another cell multiple values
highlight row if cell contains google sheets
excel highlight row based on cell value
conditional formatting not highlighting entire row
apply conditional formatting to entire column
excel conditional formatting based on another cell multiple values
excel conditional formatting > highlight row based on date
excel highlight row based on selected cell
excel conditional formatting highlight row based on date
excel conditional formatting highlight row if cell contains partial text
excel conditional formatting highlight row if cell is not blank
excel conditional formatting highlight row based on multiple conditions
excel conditional formatting highlight row based on today’s date
excel conditional formatting highlight row if cell contains any text
excel conditional formatting highlight row if cell contains date
excel conditional formatting highlight row if cell begins with
excel conditional formatting highlight row between two values
excel formula conditional formatting highlight row