Working with Conditional Formatting formulas
In this post I am going to try to explain formula basics in conditional formatting. It is really good if you have some knowledge about absolute and relative cell references but it is not necessary. I think the examples shown here explains a lot when it comes to cell referencing.
Conditional formatting is extremely volatile and is even recalcualted every time you scroll the sheet. Remember this if you already work with cpu instensive workbooks.
When creating conditional formatting formulas, I think you will be using mostly comparison operators (=<>) but you can also use functions. Functions I recommend: COUNTIF(), COUNTIFS(), SEARCH(), FIND()
Table of Contents
- How to create conditional formatting
- Your first conditional formatting formula
- Format entire row
- Compare a cell value to a column
- Column to column comparisons
- Highlight unique rows (COUNTIFS function)
How to create conditional formatting
- Select a cell range where you want to apply conditional formatting.
- Go to "Home" tab
- Press with left mouse button on "Conditional Formatting" button
- Press with left mouse button on "New Rule.."
- Press with left mouse button on "Use a formula to determine which cells to format:"
- Type formula in "Format values where this formula is true:"
- Press with left mouse button on "Format..." button
- Choose formatting
- Press with left mouse button on OK
- Press with left mouse button on OK
Your first conditional formatting formula
Let´s create your first conditional formatting formula. Here is a table with some random data.
Select cell range A1: D21 Repeat above steps and use this formula:
I used a grey formatting color.
A1 is a cell reference to the uppermost, leftmost cell in your selected cell range. Relative cell reference changes depending on which cell is calculated. Using cell A1 in the conditional formatting formula, applied to cell range A1:D21 makes it compare each cell in the cell range to the value "Poland".
Example 1,
In cell A1:
Conditional formatting formula:
=A1="Poland"
becomes
="Company Name"="Poland"
and returns False. Cell A1 is NOT formatted.
Example 2,
In cell B9:
The conditional formatting formula changes automatically to:
=B9="Poland"
becomes
="Poland"="Poland"
and returns True. Cell B9 is formatted.
Format entire row
Let´s say you are interested in highlighting rows where cell value in column B is equal to "Poland".
Conditional formatting formula:
Example 1,
In cell A1:
Conditional formatting formula:
=$B1="Poland"
becomes
="Country"="Poland"
and returns False. Cell A1 is NOT formatted.
Example 2,
In cell A9:
The conditional formatting formula changes automatically to:
=$B9="Poland"
becomes
="Poland"="Poland"
and returns True. Cell A9 is formatted. In fact, the entire row is highlighted. The $ makes the cell reference "locked" to column B.
Compare a cell value to a column
Every time you want to change the value you are searching for you must first select the cell range, change the conditional formatting formula and then press OK. Boring repetitive actions, but if you change the value in the conditional formatting formula to an absolute cell reference things start to become more useful. Let me show you how:
Conditional formatting formula:
=$B4=$B$1
Cell value in B1 is compared to every cell value in column B. If the conditional formatting formula returns TRUE the entire row is highlighted.
With the same technique you can highlight an entire column, example: Search with conditional formatting
Column to column comparisons
Now I am going to show you how to use conditional formatting to easily identify companies with increasing sales.
Highlight increasing sales years
Conditional formatting formula:
=C2>B2
Highlight decreasing sales years
Conditional formatting formula:
=C2<B2
Remember to use the uppermost leftmost cell reference in the selected cell range when constructing conditional formatting formulas. In this example, I have applied CF to cell range C2:E21 and C2 is the first cell in that range to be compared to the previous year.
Highlight unique rows
This example demonstrates how to highlight unique distinct rows.
Conditional formatting formula:
Here is an explanation of how the countifs function works.
Links to related articles
Highlight unique distinct values
Highlight duplicate rows (COUNTIFS function)
Compare two lists of data: Highlight records existing in only one list
Compare two lists of data: Highlight common records
How to create a unique list using conditional formatting
Highlight common values in two lists using conditional formatting
Cf basic formulas category
This article demonstrates how to apply Conditional Formatting formula to a cell range, it finds cells that are in close […]
The conditional formatting formula applied to cell range F3:F13 highlights all cells containing text. Here is how I did it: […]
Table of Contents Highlight empty cells Explaining CF formula How to apply conditional formatting Formulas returning nothing Cells with hidden […]
Conditional formatting category
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]
The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
Question: How do I highlight dates that meet criteria using conditional formatting? Table of contents Highlight values in a column […]
This post demonstrates how to highlight records with the closest value to a criterion, you can also choose to highlight […]
The image above shows rows highlighted based on value in column C being the largest or smallest in that particular […]
This article shows you how to easily identify duplicate rows or records in a list. What's on this webpage Highlight […]
The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]
Here is how to highlight every other row using conditional formatting. Conditional formatting formula: =ISEVEN(ROW())*OR($B3:$D3<>"") Alternative CF formula: =EVEN(ROW())=ROW() This […]
Question: I have a list that I keep adding rows to. How do i create a border that expands as […]
The image above shows you how to highlight rows with multiple criteria using OR logic. The criteria are found in […]
In this article, I will demonstrate how to search a table using conditional formatting. The criteria highlight matching column and […]
Today I am going to show you how to quickly compare two tables using Conditional Formatting (CF). I am going […]
The image above demonstrates a conditional formatting formula that highlights duplicate items based on date. The first instance is not highlighted, […]
A conditional formatting formula highlights values in column B that also exist in column D. =COUNTIF($D$3:$D$7,B3) The same thing happens […]
The image above shows conditional formatting highlighting unique distinct values, duplicates are not highlighted. Conditional Formatting Formula: =COUNTIF($B$3:B3, B3)=1 The […]
This article demonstrates how to apply different cell formatting to a cell range based on a Drop Down list, column […]
In this blog post I will demonstrate a conditional formatting formula that will highlight common records in two lists. The […]
The image above demonstrates a conditional formatting formula that highlights records that only exist in one table. There are two […]
Functions in this article
More than 1300 Excel formulas
Conditional Formatting categories
Excel categories
3 Responses to “Working with Conditional Formatting formulas”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
I have two formulas I need to combine - might be a nested if statement but I can't seem to get it to work for me...
The first being:
=If(And(len(F2)>0,E2="Yes"),"Cabling Scheduled","Requested")
The second being:
=If(Today()>J2,"Complete","Scheduled")
What I am looking to do is combine both of these to basically output 3 values. If the date of the cabling is greater than the current date, and if cabling is required, and if there is a cabling completed number, it should show as Complete. Otherwise it should show as Requested or scheduled. Requested would be F2 is blank or E2 is no/pending. Scheduled would be if E2 is Yes, and F2 has anything greater than 0 but the date is less than J2.
Awesome stuff, thank you! You just saved my day.
Bob,
thanks.