How to Use Conditional Formatting in Google Sheets to Highlight Important Data

When you look at a large spreadsheet full of numbers, it can be nearly impossible to spot patterns, outliers, or trends at a glance. A column of 500 sales figures all looks the same until you start manually scanning row by row. Is revenue trending upward? Are there any months where performance dropped significantly? Which regions are underperforming?

Conditional formatting in Google Sheets solves this by automatically applying visual formatting — background colours, text colours, and colour gradients — to cells based on their values. Cells that meet specific conditions are highlighted, making patterns instantly visible without any manual effort.

How to Access Conditional Formatting

1. Select the range of cells you want to format.
2. Click Format in the menu bar.
3. Select Conditional formatting.

The conditional formatting panel opens on the right side of the screen, showing the selected range and a dropdown for the formatting condition.

Single Colour Rules

Single colour rules apply a specific background colour or text colour when a cell meets a condition. These are the most straightforward type of conditional formatting.

Common Conditions

• Greater than / Less than: Highlight cells with values above or below a threshold. For example, highlight all sales figures above £10,000 in green.
• Equal to: Highlight cells that contain a specific value. Useful for finding all instances of “Overdue”, “Failed”, or “Pending” in a status column.
• Text contains: Highlight cells that contain specific text. For example, highlight all cells containing “urgent” in red, regardless of the full cell content.
• Text does not contain: Highlight cells that are missing specific text.
• Is empty / Is not empty: Highlight blank cells (useful for identifying missing data) or cells that have been filled in.
• Date is before / Date is after: Highlight dates that are overdue or upcoming.

Setting Up a Single Colour Rule

1. Select your data range.
2. Open Format > Conditional formatting.
3. Under Format cells if, choose a condition (for example, “Greater than”).
4. Enter the threshold value (for example, 10000).
5. Under Formatting style, choose a background colour, text colour, or both.
6. Click Done.

The formatting applies immediately and updates automatically when cell values change.

Colour Scale Rules

Colour scales apply a gradient across a range of cells based on their values. Instead of a binary “highlight or not”, colour scales create a smooth visual spectrum where the lowest values get one colour, the highest values get another colour, and everything in between gets a proportionally blended shade.

To create a colour scale:

1. Select your data range.
2. Open Format > Conditional formatting.
3. Click the Colour scale tab at the top of the panel (next to “Single colour”).
4. Choose a preset colour scale or customise the minimum, midpoint, and maximum colours.
5. Click Done.

Colour scales are ideal for:

• Heat maps: Visualise sales performance across regions, with high-performing areas in dark green and low-performing areas in red.
• Grade distributions: Show student scores with a gradient from red (low) to green (high).
• Financial data: Visualise profit margins where deep green indicates high margins and deep red indicates losses.
• Temperature or environmental data: Create intuitive visual representations of numerical data.

Using Custom Formulas for Advanced Rules

For conditions that go beyond the built-in options, Google Sheets supports custom formulas in conditional formatting. Under “Format cells if”, select Custom formula is and enter a formula that returns TRUE or FALSE.

Highlight Entire Rows Based on a Cell Value

One of the most common advanced use cases is highlighting an entire row when one specific cell in that row meets a condition. For example, highlighting the entire row when the “Status” column contains “Overdue”.

1. Select the entire data range (for example, A2:F100).
2. Open conditional formatting.
3. Choose Custom formula is.
4. Enter: =$E2="Overdue" (assuming “Status” is in column E).
5. Choose a red background colour.
6. Click Done.

The dollar sign before the column letter ($E) locks the formula to column E, while the row number (2) is relative, so it adjusts for each row. This means the formula checks column E for every row, but applies the formatting to the entire row.

Highlight Duplicate Values

To highlight duplicate values in a column:

1. Select the column (for example, A2:A100).
2. Choose Custom formula is.
3. Enter: =COUNTIF($A$2:$A$100, A2) > 1
4. Choose a yellow background colour.

Every cell that has a duplicate value elsewhere in the column will be highlighted.

Highlight Weekends

If you have a column of dates and want to highlight weekends:

1. Select the date column.
2. Choose Custom formula is.
3. Enter: =OR(WEEKDAY(A2)=1, WEEKDAY(A2)=7)
4. Choose a light grey background.

Managing Multiple Conditional Formatting Rules

You can apply multiple conditional formatting rules to the same range. Rules are evaluated in order from top to bottom in the conditional formatting panel. The first matching rule takes precedence.

For example, you might apply three rules to a sales column:

1. Values above 20,000 — dark green background.
2. Values between 10,000 and 20,000 — yellow background.
3. Values below 10,000 — red background.

You can drag rules to reorder them and delete rules by clicking the bin icon. To stop processing further rules after a match, ensure your conditions are mutually exclusive, or enable the “Stop if true” option when using Google Apps Script.

Practical Use Cases

• Task management: Colour-code tasks by status (green for complete, yellow for in progress, red for overdue).
• Budget tracking: Highlight expenses that exceed their budget allocation.
• Inventory management: Flag stock levels below reorder thresholds.
• Student grades: Apply a colour scale to show performance distribution at a glance.
• Data validation: Highlight cells containing unexpected values, blanks, or duplicates before importing data into another system.
• Sales dashboards: Create visual heat maps showing performance across time periods, products, or regions.

Tips for Effective Conditional Formatting

• Use a consistent colour language: Green for good, yellow for caution, red for bad. Do not change colour meanings between different sheets in the same workbook.
• Do not over-format: If every cell has a different colour, the formatting loses its meaning. Use conditional formatting sparingly and purposefully.
• Test with edge cases: Make sure your rules handle empty cells, zero values, and text strings correctly.
• Name your ranges: When applying rules to large ranges, make sure the range covers all data, including rows you might add later.
• Use colour scales for continuous data: Gradients are far more informative than binary colour rules when working with numerical data that spans a wide range.

Conditional formatting turns a static spreadsheet into a visual dashboard. Once set up, it requires zero ongoing effort — the formatting updates automatically as your data changes, ensuring that important patterns and outliers are always visible.