---
title: "Conditional formatting formula practice | ExcelSavvy"
description: "Practice the formulas behind conditional formatting in Excel. Highlight whole rows with $, flag overdue dates and duplicates, checked as you type."
canonical_url: "https://excelsavvy.com/practice/conditional-formatting"
meta:
  "og:description": "Practice the formulas behind conditional formatting in Excel. Highlight whole rows with $, flag overdue dates and duplicates, checked as you type."
  "og:image": "https://excelsavvy.com/_og/d/c_Page%2Ctitle_Conditional%2Bformatting%2Bformula%2Bpractice%2Cdescription_~UHJhY3RpY2UgdGhlIGZvcm11bGFzIGJlaGluZCBjb25kaXRpb25hbCBmb3JtYXR0aW5nIGluIEV4Y2VsLiBIaWdobGlnaHQgd2hvbGUgcm93cyB3aXRoICQsIGZsYWcgb3ZlcmR1ZSBkYXRlcyBhbmQgZHVwbGljYXRlcywgY2hlY2tlZCBhcyB5b3UgdHlwZS4%2Ceyebrow_Excel%2Bpractice%2Bexercises%2Csite_practiceexcel%2Cprops_eyJncmlkIjp7ImNvbHMiOlsiQSIsIkIiLCJDIiwiRCJdLCJyb3dzIjpbeyJuIjoxLCJjZWxscyI6WyJSZXAiLCJSZWdpb24iLCJTYWxlcyIsIk92ZXIgMTAsMDAwPyJdfSx7Im4iOjIsImNlbGxzIjpbIkxhcnMiLCJOb3J0aCIsIjEyNTAwIiwiIl19XSwidGFyZ2V0IjpbMSwzXX19%2Cp_Ii9wcmFjdGljZS9jb25kaXRpb25hbC1mb3JtYXR0aW5nIg%2Cs_UMznKgMk9CmtypnS.png"
  "og:image:height": "600"
  "og:image:type": image/png
  "og:image:width": "1200"
  "og:title": "Conditional formatting formula practice | ExcelSavvy"
  "twitter:image": "https://excelsavvy.com/_og/d/c_Page%2Ctitle_Conditional%2Bformatting%2Bformula%2Bpractice%2Cdescription_~UHJhY3RpY2UgdGhlIGZvcm11bGFzIGJlaGluZCBjb25kaXRpb25hbCBmb3JtYXR0aW5nIGluIEV4Y2VsLiBIaWdobGlnaHQgd2hvbGUgcm93cyB3aXRoICQsIGZsYWcgb3ZlcmR1ZSBkYXRlcyBhbmQgZHVwbGljYXRlcywgY2hlY2tlZCBhcyB5b3UgdHlwZS4%2Ceyebrow_Excel%2Bpractice%2Bexercises%2Csite_practiceexcel%2Cprops_eyJncmlkIjp7ImNvbHMiOlsiQSIsIkIiLCJDIiwiRCJdLCJyb3dzIjpbeyJuIjoxLCJjZWxscyI6WyJSZXAiLCJSZWdpb24iLCJTYWxlcyIsIk92ZXIgMTAsMDAwPyJdfSx7Im4iOjIsImNlbGxzIjpbIkxhcnMiLCJOb3J0aCIsIjEyNTAwIiwiIl19XSwidGFyZ2V0IjpbMSwzXX19%2Cp_Ii9wcmFjdGljZS9jb25kaXRpb25hbC1mb3JtYXR0aW5nIg%2Cs_UMznKgMk9CmtypnS.png"
---

> Index of every page on this site: [llms.txt](https://excelsavvy.com/llms.txt)

# **Conditional formatting practice exercises**

Write the formulas behind conditional formatting: a test that returns TRUE or FALSE, a $ before the column so the whole row is highlighted, overdue dates, two conditions and duplicates.

[**Start the first exercise**](https://excelsavvy.com/practice/conditional-formatting/rule-is-a-test)

AND(COUNTIF(

## **How conditional formatting formulas work**

Conditional formatting colors a cell or a whole row when a rule you write is true. The rule is a formula that returns TRUE or FALSE, with no IF needed. You find it under Home, Conditional Formatting, New Rule, where you choose to use a formula. Write the formula for the first cell of what you selected, and Excel adjusts it for the rest.

`**=$C2>10000**` highlights every row where the sales in column C are over 10,000. The $ before C locks the column, so every cell in the row tests column C, while the row number follows along down the list. For overdue invoices, `**=AND($B2<$G$1,$C2="No")**` checks the due date against today's date in G1 and that C2 says No.

Most mistakes come down to the dollar signs. Without $, the rule slides sideways and colors the wrong cells. Lock the row as well, and every row tests the same row. Dates are numbers, so you can compare them with < and >. The exercises below cover a basic test, whole rows, overdue deadlines, two conditions with AND and duplicates with COUNTIF.

1. [**1****A rule is a test**Conditional formatting colors a cell when the rule's formula returns TRUE. In D2, write the rule for "sales over 10,000" as a test of C2, without IF.](https://excelsavvy.com/practice/conditional-formatting/rule-is-a-test)
2. [**2****Highlight the whole row with $****Premium**You selected A2:C20 and want to highlight the whole row when sales are over 10,000. In D2, write the rule so every cell in the row tests column C. Lock the column with $, but not the row.](https://excelsavvy.com/practice/conditional-formatting/highlight-whole-row)
3. [**3****Overdue deadlines****Premium**Highlight the row when the deadline has passed. Today's date is in G1. In D2, write the rule that tests whether the deadline in C2 is before G1. Lock column C and all of G1 with $.](https://excelsavvy.com/practice/conditional-formatting/overdue-deadlines)
4. [**4****Overdue and unpaid****Premium**Highlight the invoice when it is overdue and unpaid. In D2, write the rule with AND. The due date in B2 must be before G1, and C2 must say "No". Lock the columns and all of G1.](https://excelsavvy.com/practice/conditional-formatting/overdue-unpaid-invoices)
5. [**5****Find duplicates****Premium**Highlight the whole row when the customer ID appears more than once in A2:A6. In D2, write the rule with COUNTIF. Lock the whole range and the column in A2.](https://excelsavvy.com/practice/conditional-formatting/find-duplicates)

## **Functions in this topic**

- [**AND**](https://excelsavvy.com/functions/and)
- [**COUNTIF**](https://excelsavvy.com/functions/countif)

## **Read the guide**

- [**Conditional formatting in Excel: highlight cells and rows with rules and formulas**](https://excelsavvy.com/learn/conditional-formatting-excel)

## **Questions**

<details>

<summary>

### How do I highlight a whole row based on one cell?

</summary>

Select the whole range, such as A2:C20, then add a formula rule that locks the column with $ but not the row, for example =$C2&gt;10000. Every cell in the row then tests column C, so the whole row gets the same color. Without the $, the rule drifts to column D and beyond, and the colors come out wrong.

</details>

<details>

<summary>

### How do I highlight dates that are overdue?

</summary>

Compare the date with today's date. Use =$C2&lt;TODAY() to highlight rows with a deadline before today, or point to a cell that holds the date, as in =$C2&lt;$G$1. If you only want open items, add a second condition with AND, such as =AND($C2&lt;TODAY(),$D2&lt;&gt;"Done").

</details>

<details>

<summary>

### How do I highlight duplicates with a formula?

</summary>

Use COUNTIF with a locked range: =COUNTIF($A$2:$A$6,$A2)&gt;1 colors every row whose customer ID appears more than once in A2:A6. Excel also has a built-in rule for duplicate values, but a formula lets you color the whole row or add more conditions.

</details>

```json
{"@context":"https://schema.org","@graph":[{"@id":"https://excelsavvy.com/#identity","@type":"Organization","name":"ExcelSavvy","url":"https://excelsavvy.com/","logo":{"@id":"https://excelsavvy.com/#logo"}},{"@id":"https://excelsavvy.com/#website","@type":"WebSite","inLanguage":"en-US","name":"ExcelSavvy","url":"https://excelsavvy.com/","alternateName":["Excel Savvy","ExcelSavvy.com","PracticeExcel","Practice Excel Online"],"publisher":{"@id":"https://excelsavvy.com/#identity"},"workTranslation":[{"@id":"https://excelopgaver.dk/#website"},{"@id":"https://excelpraxis.de/#website"}]},{"@id":"https://excelsavvy.com/practice/conditional-formatting#webpage","description":"Practice the formulas behind conditional formatting in Excel. Highlight whole rows with $, flag overdue dates and duplicates, checked as you type.","name":"Conditional formatting formula practice","url":"https://excelsavvy.com/practice/conditional-formatting","@type":["WebPage","CollectionPage","FAQPage"],"breadcrumb":{"@id":"https://excelsavvy.com/practice/conditional-formatting#breadcrumb"},"isPartOf":{"@id":"https://excelsavvy.com/#website"},"mainEntity":[{"@id":"https://excelsavvy.com/practice/conditional-formatting#/schema/question/1"},{"@id":"https://excelsavvy.com/practice/conditional-formatting#/schema/question/2"},{"@id":"https://excelsavvy.com/practice/conditional-formatting#/schema/question/3"}],"potentialAction":[{"@type":"ReadAction","target":["https://excelsavvy.com/practice/conditional-formatting"]}],"primaryImageOfPage":{"@id":"https://excelsavvy.com/#logo"}},{"@id":"https://excelsavvy.com/practice/conditional-formatting#breadcrumb","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","name":"Home","item":"https://excelsavvy.com","position":1},{"@type":"ListItem","name":"Excel practice exercises","item":"https://excelsavvy.com/practice","position":2},{"@type":"ListItem","name":"Conditional formatting formula practice","position":3}]},{"@id":"https://excelsavvy.com/practice/conditional-formatting#/schema/question/1","@type":"Question","inLanguage":"en-US","name":"How do I highlight a whole row based on one cell?","acceptedAnswer":{"@type":"Answer","text":"Select the whole range, such as A2:C20, then add a formula rule that locks the column with $ but not the row, for example =$C2>10000. Every cell in the row then tests column C, so the whole row gets the same color. Without the $, the rule drifts to column D and beyond, and the colors come out wrong."}},{"@id":"https://excelsavvy.com/practice/conditional-formatting#/schema/question/2","@type":"Question","inLanguage":"en-US","name":"How do I highlight dates that are overdue?","acceptedAnswer":{"@type":"Answer","text":"Compare the date with today's date. Use =$C2\u003CTODAY() to highlight rows with a deadline before today, or point to a cell that holds the date, as in =$C2\u003C$G$1. If you only want open items, add a second condition with AND, such as =AND($C2\u003CTODAY(),$D2\u003C>\"Done\")."}},{"@id":"https://excelsavvy.com/practice/conditional-formatting#/schema/question/3","@type":"Question","inLanguage":"en-US","name":"How do I highlight duplicates with a formula?","acceptedAnswer":{"@type":"Answer","text":"Use COUNTIF with a locked range: =COUNTIF($A$2:$A$6,$A2)>1 colors every row whose customer ID appears more than once in A2:A6. Excel also has a built-in rule for duplicate values, but a formula lets you color the whole row or add more conditions."}},{"@id":"https://excelsavvy.com/#logo","@type":"ImageObject","caption":"ExcelSavvy","contentUrl":"https://excelsavvy.com/brand/practiceexcel/icon-512.png?v=3","inLanguage":"en-US","url":"https://excelsavvy.com/brand/practiceexcel/icon-512.png?v=3"}]}
```

## Sitemap

See the full [sitemap](https://excelsavvy.com/sitemap.md) for all pages.
