---
title: "Conditional formatting formula practice: write the rules | PracticeExcel"
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://practiceexcelonline.com/practice/conditional-formatting"
locale: en
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://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~Q29uZGl0aW9uYWwgZm9ybWF0dGluZyBmb3JtdWxhIHByYWN0aWNlOiB3cml0ZSB0aGUgcnVsZXM%2Cdescription_~UHJhY3RpY2UgdGhlIGZvcm11bGFzIGJlaGluZCBjb25kaXRpb25hbCBmb3JtYXR0aW5nIGluIEV4Y2VsLiBIaWdobGlnaHQgd2hvbGUgcm93cyB3aXRoICQsIGZsYWcgb3ZlcmR1ZSBkYXRlcyBhbmQgZHVwbGljYXRlcywgY2hlY2tlZCBhcyB5b3UgdHlwZS4%2Ceyebrow_Excel%2Bpractice%2Bexercises%2Csite_practiceexcel%2Cprops_eyJncmlkIjp7ImNvbHMiOlsiQSIsIkIiLCJDIiwiRCJdLCJyb3dzIjpbeyJuIjoxLCJjZWxscyI6WyJSZXAiLCJSZWdpb24iLCJTYWxlcyIsIk92ZXIgMTAsMDAwPyJdfSx7Im4iOjIsImNlbGxzIjpbIkxhcnMiLCJOb3J0aCIsIjEyNTAwIiwiIl19XSwidGFyZ2V0IjpbMSwzXX19%2Cp_Ii9wcmFjdGljZS9jb25kaXRpb25hbC1mb3JtYXR0aW5nIg%2Cs_R9m4YAak9ucNUKVk.png"
  "og:image:height": "600"
  "og:image:type": image/png
  "og:image:width": "1200"
  "og:title": "Conditional formatting formula practice: write the rules | PracticeExcel"
  "twitter:image": "https://practiceexcelonline.com/_og/d/c_Page%2Ctitle_~Q29uZGl0aW9uYWwgZm9ybWF0dGluZyBmb3JtdWxhIHByYWN0aWNlOiB3cml0ZSB0aGUgcnVsZXM%2Cdescription_~UHJhY3RpY2UgdGhlIGZvcm11bGFzIGJlaGluZCBjb25kaXRpb25hbCBmb3JtYXR0aW5nIGluIEV4Y2VsLiBIaWdobGlnaHQgd2hvbGUgcm93cyB3aXRoICQsIGZsYWcgb3ZlcmR1ZSBkYXRlcyBhbmQgZHVwbGljYXRlcywgY2hlY2tlZCBhcyB5b3UgdHlwZS4%2Ceyebrow_Excel%2Bpractice%2Bexercises%2Csite_practiceexcel%2Cprops_eyJncmlkIjp7ImNvbHMiOlsiQSIsIkIiLCJDIiwiRCJdLCJyb3dzIjpbeyJuIjoxLCJjZWxscyI6WyJSZXAiLCJSZWdpb24iLCJTYWxlcyIsIk92ZXIgMTAsMDAwPyJdfSx7Im4iOjIsImNlbGxzIjpbIkxhcnMiLCJOb3J0aCIsIjEyNTAwIiwiIl19XSwidGFyZ2V0IjpbMSwzXX19%2Cp_Ii9wcmFjdGljZS9jb25kaXRpb25hbC1mb3JtYXR0aW5nIg%2Cs_R9m4YAak9ucNUKVk.png"
---

> Index of every page on this site: [llms.txt](https://practiceexcelonline.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://practiceexcelonline.com/practice/conditional-formatting/rule-is-a-test)

AND(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://practiceexcelonline.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://practiceexcelonline.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://practiceexcelonline.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://practiceexcelonline.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://practiceexcelonline.com/practice/conditional-formatting/find-duplicates)

## **Functions in this topic**

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

## **Read the guide**

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

```json
{"@context":"https://schema.org","@graph":[{"@id":"https://practiceexcelonline.com/#identity","@type":"Organization","name":"PracticeExcel","url":"https://practiceexcelonline.com/","logo":{"@id":"https://practiceexcelonline.com/#logo"}},{"@id":"https://practiceexcelonline.com/#website","@type":"WebSite","inLanguage":"en-US","name":"PracticeExcel","url":"https://practiceexcelonline.com/","alternateName":["Practice Excel Online","PracticeExcelOnline.com"],"publisher":{"@id":"https://practiceexcelonline.com/#identity"},"workTranslation":{"@id":"https://excelopgaver.dk/#website"}},{"@id":"https://practiceexcelonline.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: write the rules","url":"https://practiceexcelonline.com/practice/conditional-formatting","@type":["WebPage","CollectionPage"],"breadcrumb":{"@id":"https://practiceexcelonline.com/practice/conditional-formatting#breadcrumb"},"isPartOf":{"@id":"https://practiceexcelonline.com/#website"},"potentialAction":[{"@type":"ReadAction","target":["https://practiceexcelonline.com/practice/conditional-formatting"]}],"primaryImageOfPage":{"@id":"https://practiceexcelonline.com/#logo"}},{"@id":"https://practiceexcelonline.com/practice/conditional-formatting#breadcrumb","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","name":"Home","item":"https://practiceexcelonline.com","position":1},{"@type":"ListItem","name":"Excel practice exercises","item":"https://practiceexcelonline.com/practice","position":2},{"@type":"ListItem","name":"Conditional formatting formula practice: write the rules","position":3}]},{"@id":"https://practiceexcelonline.com/#logo","@type":"ImageObject","caption":"PracticeExcel","contentUrl":"https://practiceexcelonline.com/brand/practiceexcel/icon-512.png","inLanguage":"en-US","url":"https://practiceexcelonline.com/brand/practiceexcel/icon-512.png"}]}
```

## Sitemap

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