Conditional Formatting That Surfaces Problems: How to Make Spreadsheet Issues Impossible to Miss

Large spreadsheets often contain the information you need, but the problem is finding it quickly.

A late invoice may be hidden among hundreds of paid ones. A duplicated order number may look harmless until it causes a reporting error. A falling sales figure may be buried inside a column of otherwise normal results.

Conditional formatting helps by changing the appearance of cells when specific conditions are met. Instead of scanning every row manually, you can make exceptions, risks and unusual values stand out automatically.

Used well, conditional formatting becomes an early-warning system for your spreadsheet. Used badly, it creates a wall of colours that is harder to understand than the original data.

This guide explains how to use conditional formatting that surfaces problems, with practical examples for overdue dates, duplicated records, missing information, unusual values, performance thresholds and formula-based alerts.

What Is Conditional Formatting?

Conditional formatting automatically changes a cell’s appearance when its value meets a rule.

Depending on the spreadsheet application, it can apply:

• Background colours
• Font colours
• Bold text
• Borders
• Icons
• Data bars
• Colour scales

For example, you could configure a spreadsheet so that:

• Overdue invoices turn red.
• Missing customer names turn amber.
• Duplicate order numbers are highlighted.
• Negative profit figures use a warning symbol.
• Low stock levels stand out.
• Sales above target appear green.

The formatting changes automatically when the underlying data changes.

Why Conditional Formatting Is So Useful

A normal spreadsheet treats every row visually the same.

Conditional formatting adds meaning to the presentation.

It can help you:

• Identify urgent problems
• Spot incomplete records
• Detect duplicates
• Find unusual results
• Compare performance with targets
• Monitor deadlines
• Review large datasets faster
• Reduce manual checking
• Make reports easier to understand

The key is to highlight exceptions rather than decorate ordinary information.

Start With the Problem You Need to Find

Before creating a rule, ask:

What action should someone take when this formatting appears?

A useful rule should indicate something meaningful, such as:

• Contact the customer.
• Reorder the product.
• Investigate the transaction.
• Complete the missing field.
• Review the unusually high expense.
• Escalate the overdue task.
• Check whether the value is incorrect.

When a colour does not lead to a decision or action, it may not be useful.

Example Dataset

Imagine an invoice tracker containing:

This small table already contains several possible problems:

• One overdue unpaid invoice
• One missing customer name
• One duplicated invoice number
• Several upcoming due dates

Conditional formatting can reveal all of these immediately.

Rule 1: Highlight Overdue Invoices

An overdue invoice should usually meet two conditions:

1. Its due date is earlier than today.
2. Its status is not Paid.

A simple rule based only on the date may incorrectly highlight invoices that were paid late but are now complete.

Formula Example

Assume:

• Due Date is in column C
• Status is in column D
• The first data row is row 2

Use:

=AND($C2<TODAY(),$D2<>"Paid",$C2<>"")

Apply the rule to the full row range, such as:

A2:F1000

Choose a strong warning format, such as:

• Light-red fill
• Dark-red text
• Bold font

The rule evaluates each row and highlights only genuinely overdue unpaid invoices.

Why the Dollar Signs Matter

In the formula:

=AND($C2<TODAY(),$D2<>"Paid",$C2<>"")

The column letters are locked with $, while the row number remains relative.

This means:

• Every row checks its own date in column C.
• Every row checks its own status in column D.
• The rule can format the complete row.

Without the correct references, the formatting may shift to the wrong columns.

Rule 2: Highlight Invoices Due Soon

Not every warning needs to mean something has already failed.

You can create an amber warning for invoices due within the next seven days.

=AND($C2>=TODAY(),$C2<=TODAY()+7,$D2<>"Paid")

Apply an amber or yellow fill.

This creates a useful progression:

• Red: overdue
• Amber: due soon
• No highlight: not currently urgent

Keep the red rule above the amber rule so overdue items receive the stronger warning.

Rule 3: Find Duplicate Invoice Numbers

Duplicate identifiers can cause:

• Double counting
• Incorrect payments
• Reporting errors
• Import failures
• Confusing audit trails

Excel and Google Sheets both provide built-in duplicate-value rules.

In Excel:

1. Select the Invoice column.
2. Open Home > Conditional Formatting.
3. Choose Highlight Cells Rules > Duplicate Values.
4. Select a clear warning format.
5. Confirm the rule.

For more control, use a formula:

=COUNTIF($A:$A,$A2)>1

This highlights every invoice number that appears more than once.

Ignore Blank Cells When Checking Duplicates

A duplicate rule may highlight every blank cell because blank appears repeatedly.

Use:

=AND($A2<>"",COUNTIF($A:$A,$A2)>1)

This checks that the cell contains a value before counting duplicates.

Rule 4: Highlight Missing Required Information

Blank cells can indicate incomplete records.

Suppose Customer, Due Date, Status and Amount are required.

You could highlight each blank cell individually using the built-in Blanks rule.

However, highlighting the complete row may make the problem easier to review.

Use:

=OR($B2="",$C2="",$D2="",$E2="")

This flags any row missing at least one required field.

Use a softer warning colour than overdue invoices so the spreadsheet still communicates priority clearly.

Highlight Only the Missing Cell

Sometimes highlighting the entire row is too broad.

Select the required range and use:

=B2=""

Apply it to the relevant column.

For several non-adjacent required columns, create separate rules or use application-specific range selections.

Rule 5: Surface Negative Profit

Suppose a business report contains Revenue, Cost and Profit.

Negative profit should stand out immediately.

A simple rule is:

=F2<0

Choose:

• Red text
• Light-red fill
• Downward icon

Avoid using only red text when the spreadsheet may be printed in black and white or viewed by someone with colour-vision difficulties.

Combine colour with a symbol, bold text or label.

Rule 6: Highlight Values Below Target

Imagine a sales tracker with:

• Actual Sales in column E
• Target in column F

To identify people below target:

=$E2<$F2

This compares each result with the target in the same row.

To highlight results below 80% of target:

=$E2<$F2*0.8

You could use a three-level approach:

• Red: below 80%
• Amber: 80–99%
• Green: target achieved

Avoid Green Everywhere

Green formatting can become visually dominant when most results are normal.

Often it is better to highlight only exceptions:

• Red for serious problems
• Amber for attention needed
• No formatting for acceptable results

This makes the warnings much easier to notice.

Rule 7: Highlight Unusually High Expenses

A fixed threshold may work when the business already knows what counts as excessive.

For example:

=E2>1000

This highlights expenses above £1,000.

However, some datasets need a relative threshold.

You may want to highlight values substantially above the normal range.

One approach is to flag anything more than two standard deviations above the average:

=E2>AVERAGE($E$2:$E$500)+2*STDEV.P($E$2:$E$500)

This can help identify:

• Unusual transactions
• Data-entry errors
• Outliers
• Exceptional costs

A statistical outlier is not automatically wrong. It is a prompt for investigation.

Rule 8: Find Dates Entered as Text

Dates stored as text can break:

• Sorting
• Filtering
• Date calculations
• Pivot tables
• Deadline rules

A text date may look correct while behaving incorrectly.

If the date should be a real numeric Excel date, use:

=AND(C2<>"",NOT(ISNUMBER(C2)))

This highlights non-blank cells in the date column that are not numeric dates.

You can use a similar method for numerical columns:

=AND(E2<>"",NOT(ISNUMBER(E2)))

This is particularly useful after importing data from websites, accounting systems or CSV files.

Rule 9: Detect Numbers Stored as Text

Numbers stored as text can cause:

• Incorrect totals
• Pivot tables using Count instead of Sum
• Sort-order problems
• Failed comparisons
• Broken calculations

Select the numerical column and apply:

=AND(E2<>"",ISTEXT(E2))

This identifies cells containing text where numbers are expected.

Do not automatically convert every highlighted cell until you have checked whether it contains a valid note or code.

Rule 10: Highlight Rows With Errors

Formula errors such as these can damage reports:

• #N/A
• #VALUE!
• #DIV/0!
• #REF!
• #NAME?

To highlight any error in a particular cell:

=ISERROR(F2)

To highlight a row when any of several cells contains an error:

=OR(ISERROR(E2),ISERROR(F2),ISERROR(G2))

This makes broken calculations easier to find before the spreadsheet is shared.

Rule 11: Show Tasks That Are Stuck

A task tracker might include:

• Start Date
• Due Date
• Status
• Percentage Complete

A task could be considered at risk when:

• Its due date is within three days.
• It is not complete.
• Progress is below 75%.

Use:

=AND($C2<=TODAY()+3,$C2>=TODAY(),$D2<>"Complete",$E2<75%)

This surfaces tasks that are approaching the deadline without enough progress.

A separate overdue rule could use:

=AND($C2<TODAY(),$D2<>"Complete")

Rule 12: Highlight Old Open Support Tickets

For an IT support or customer-service tracker, you might flag tickets that have remained open for more than a set period.

Assume:

• Opened Date is in column B
• Status is in column E

For tickets open more than three days:

=AND(TODAY()-$B2>3,$E2<>"Closed",$B2<>"")

For urgent service targets, the time limit could be based on hours rather than days.

Excel stores time as a fraction of a day, so four hours can be represented as:

4/24

A formula could be:

=AND(NOW()-$B2>4/24,$E2<>"Closed")

Ensure the source timestamps are genuine date-and-time values.

Rule 13: Identify Low Stock

Suppose the stock table includes:

• Current Stock
• Reorder Level

Use:

=$E2<=$F2

This highlights products that have reached or fallen below their reorder point.

A more severe rule for zero stock could use:

=$E2=0

You can then apply:

• Red for zero stock
• Amber for at or below reorder level

Rule 14: Flag Expiring Contracts or Subscriptions

For renewal trackers, use separate warning windows.

Expired

=AND($D2<TODAY(),$D2<>"")

Expires Within 30 Days

=AND($D2>=TODAY(),$D2<=TODAY()+30)

Expires Within 90 Days

=AND($D2>TODAY()+30,$D2<=TODAY()+90)

Use consistent priority colours.

Avoid creating so many overlapping shades that the difference becomes unclear.

Rule 15: Highlight Changes From the Previous Period

Suppose monthly performance is recorded across rows.

To identify a reduction from the previous month:

=C2<B2

To highlight a drop greater than 10%:

=C2<B2*0.9

This can help surface:

• Falling sales
• Reduced website traffic
• Declining productivity
• Lower customer satisfaction
• Increased resolution times

Be cautious when the previous value is zero, as percentage comparisons may not behave meaningfully.

Rule 16: Find Gaps in Numbered Sequences

Missing order numbers, invoice numbers or asset IDs can indicate:

• Failed imports
• Deleted records
• Incomplete processing
• Audit problems

If sequential numbers are sorted in column A, use:

=A3<>A2+1

Apply this from the second comparison row downward.

This highlights the row after a gap.

For text prefixes such as INV-1001, you may need a helper column that extracts the numeric section.

Rule 17: Identify Repeated Customer Entries Within a Short Period

Repeated activity may indicate a duplicate transaction or an issue requiring review.

For example, to flag the same customer appearing more than once on the same date:

=COUNTIFS($B:$B,$B2,$C:$C,$C2)>1

Where:

• Column B is Customer
• Column C is Date

This type of formula-based rule can detect duplicates using several fields rather than one value alone.

Rule 18: Highlight Entire Rows Based on One Cell

Formatting a full row can make exceptions much easier to scan.

Suppose Status is in column D.

Use:

=$D2="Escalated"

Apply the formula to:

A2:H1000

The dollar sign locks the status column while each row checks its own value.

This method is useful for:

• Overdue
• Escalated
• Cancelled
• Failed
• On Hold
• Awaiting Approval

Colour Scales: Useful but Easy to Misuse

Colour scales shade cells according to their relative values.

For example:

• Lowest values in red
• Mid-range values in amber
• Highest values in green

They work well for:

• Heat maps
• Performance comparisons
• Risk scores
• Response times
• Survey results
• Monthly sales

They are less suitable when:

• A higher number is not always better.
• The dataset contains extreme outliers.
• Users need precise categories.
• Colour meaning is ambiguous.

A colour scale shows relative position, not necessarily whether a value is acceptable.

Data Bars

Data bars create a horizontal bar inside each cell based on its value.

They are useful for quickly comparing:

• Sales totals
• Stock levels
• Project progress
• Hours worked
• Budget usage
• Ticket volumes

Data bars are compact and often easier to interpret than a colour scale.

However, confirm whether negative values use a distinct direction or colour.

Icon Sets

Icon sets apply symbols such as:

• Arrows
• Traffic lights
• Ticks
• Crosses
• Flags
• Ratings

They can provide useful visual signals, especially when the spreadsheet must remain understandable without colour.

For example:

• Red downward arrow: below target
• Amber horizontal arrow: near target
• Green upward arrow: above target

Review the default thresholds carefully. Excel may initially divide the range into percentages that do not match your business rules.

Set Meaningful Icon Thresholds

Suppose a performance score is measured from 0 to 100.

You might choose:

• Red: below 70
• Amber: 70–89
• Green: 90 or above

Do not allow Excel to choose arbitrary thirds when the organisation already has defined performance levels.

Open Manage Rules and edit the threshold type and values.

Formula-Based Rules Are the Most Flexible

Built-in rules work well for:

• Greater than
• Less than
• Between
• Duplicate values
• Top and bottom values
• Basic dates

Formula-based rules allow you to combine conditions.

For example:

=AND($D2="Open",$C2<TODAY(),$F2>1000)

This could identify open, overdue items worth more than £1,000.

Formula rules are especially useful when the condition depends on several columns.

Use Helper Columns for Complex Logic

Conditional-formatting formulas should remain understandable.

When a rule becomes extremely long, consider adding a helper column.

For example, a helper column called Risk Status might return:

=IF(AND(C2<TODAY(),D2<>"Closed"),"Overdue",

 IF(AND(C2<=TODAY()+7,D2<>"Closed"),"Due Soon",

 "Normal"))

Conditional formatting can then use simple rules:

=$G2="Overdue"

and:

=$G2="Due Soon"

Helper columns also make it easier to filter, count and report the same status.

Rule Order Matters

Several rules may apply to the same cell or row.

For example, an invoice could be:

• Overdue
• High value
• Missing information
• Duplicated

Excel processes rules in a defined order.

Use Conditional Formatting > Manage Rules to:

• Move important rules upward
• Review overlapping ranges
• Edit formulas
• Delete obsolete rules
• Use Stop If True where appropriate

The most urgent rule should usually have priority.

What Does “Stop If True” Do?

When Stop If True is enabled, Excel stops checking lower-priority rules once the current rule matches.

For example:

1. Overdue and unpaid: red
2. Due within seven days: amber
3. Paid: green

When the overdue rule is true, Excel does not continue and apply the amber rule.

This helps prevent conflicting formats.

Use it carefully, because lower rules may provide additional useful formatting.

Apply Rules to the Correct Range

A correct formula can appear broken when the Applies to range is wrong.

For example:

=$C2<TODAY()

may be designed for row 2 onward, but the range accidentally begins at row 3.

This shifts the logic by one row.

Always check:

• The first row of the range
• The row number in the formula
• Locked and relative references
• Whether headers are excluded
• Whether new rows are included

Use Excel Tables for Expanding Data

Convert the source range into an Excel Table using:

Ctrl + T

Tables can help formatting expand as new rows are added.

Benefits include:

• Dynamic ranges
• Consistent formulas
• Filter buttons
• Structured references
• Easier reporting

Still verify new entries, because some conditional-formatting rules may not expand as expected after copying, importing or pasting data.

Conditional Formatting in Google Sheets

Google Sheets uses similar principles.

To create a rule:

1. Select the range.
2. Open Format > Conditional formatting.
3. Choose a condition or Custom formula is.
4. Enter the formula.
5. Choose the formatting.
6. Select Done.

A custom formula for overdue rows might be:

=AND($C2<TODAY(),$D2<>"Paid",$C2<>"")

As in Excel, the formula should be written relative to the top-left data row of the selected range.

Make Warnings Accessible

Do not rely only on red and green.

Some users cannot distinguish these colours easily, and printed copies may not preserve them.

Combine colour with:

• Icons
• Bold text
• Borders
• Status labels
• Symbols
• Clear headings

For example:

• Red fill plus “OVERDUE”
• Amber fill plus warning triangle
• Green tick plus “Complete”

The information should remain understandable without colour.

Use a Consistent Colour Language

Assign meanings consistently across the workbook.

A practical system might be:

• Red: urgent problem
• Amber: attention required
• Green: completed or confirmed
• Blue: informational
• Grey: inactive or not applicable

Do not use red for “high sales” on one sheet and “failure” on another.

Consistency reduces the time needed to interpret reports.

Avoid Too Many Colours

A spreadsheet with ten warning colours does not have ten priority levels. It has visual confusion.

Limit formatting to a small set of meaningful states.

For example:

• Critical
• Needs attention
• Normal
• Complete

Use explanatory labels when the meaning is not obvious.

Do Not Format Every Cell

Conditional formatting is most effective when the majority of the sheet remains visually calm.

When every cell has:

• A colour
• An icon
• A bar
• A border
• Bold text

nothing stands out.

Use formatting to surface problems rather than prove that conditional formatting exists.

Avoid Volatile and Full-Column Rules Where Possible

Formulas using functions such as:

• TODAY()
• NOW()
• OFFSET()
• INDIRECT()

may recalculate frequently.

Full-column rules applied to over a million rows can also slow down a workbook.

Instead of:

A:A

use a realistic range or an Excel Table.

For example:

A2:H5000

Use TODAY() and NOW() when genuinely needed, but avoid repeating complex volatile logic across unnecessarily large ranges.

Check Performance in Large Workbooks

Conditional formatting can slow down spreadsheets when there are:

• Hundreds of overlapping rules
• Full-column formulas
• Large imported datasets
• Volatile functions
• Duplicated rule ranges
• Entire-row formatting across thousands of columns
• Frequent workbook recalculation

Use Manage Rules to remove duplicates and simplify the workbook.

Sometimes several similar rules can be replaced with one helper column.

Conditional Formatting Does Not Validate Data

Conditional formatting warns users after a problem exists.

Data validation can prevent some problems from being entered.

For example:

• A status dropdown prevents misspellings.
• A date rule blocks invalid dates.
• A whole-number rule prevents text in a quantity column.
• A custom rule stops duplicate IDs.

Use both together:

• Data validation to reduce errors
• Conditional formatting to reveal remaining exceptions

Conditional Formatting Does Not Replace Filters

Formatting makes problems visible, but filters help isolate them.

After highlighting overdue invoices, you may also want to filter the Status or Risk column.

A helper column can make this especially effective.

For example:

=IF(AND(C2<TODAY(),D2<>"Paid"),"Overdue","")

You can then filter for Overdue, count the records and use them in a pivot table.

Conditional Formatting and Pivot Tables

Conditional formatting can be applied to pivot table results.

Useful examples include:

• Highlighting low-performing regions
• Adding data bars to sales totals
• Flagging negative profit
• Showing top and bottom categories
• Comparing actual results with targets

When creating the rule, check whether it applies to:

• Selected cells
• All cells showing a particular value field
• The full pivot table

A cell-specific rule may not expand when the pivot changes.

Conditional Formatting for Dashboards

In dashboards, formatting should guide attention quickly.

Useful approaches include:

• Red warning counters
• Traffic-light KPIs
• Data bars for progress
• Icons beside trends
• Limited heat maps
• Highlighted exception tables

Avoid filling every chart and table with the same colours.

The most important warning should be visually dominant.

A Practical Priority System

A simple spreadsheet warning system could use:

Critical

Examples:

• Overdue
• Failed
• Zero stock
• Duplicate invoice
• Formula error

Format:

• Red fill
• Bold dark text
• Warning icon

Attention Required

Examples:

• Due soon
• Below target
• Low stock
• Missing optional information

Format:

• Amber fill
• Dark text

Complete

Examples:

• Paid
• Closed
• Approved
• Delivered

Format:

• Green icon or subtle green text

Informational

Examples:

• Awaiting response
• Scheduled
• Not yet started

Format:

• Blue or grey

A Real-World Invoice Formatting Setup

For the invoice example, use these rules in order.

Rule 1: Duplicate Invoice Number

=AND($A2<>"",COUNTIF($A:$A,$A2)>1)

Format: red border and bold text.

Rule 2: Missing Required Information

=OR($B2="",$C2="",$D2="",$E2="")

Format: pale-purple or amber fill.

Rule 3: Overdue and Unpaid

=AND($C2<TODAY(),$D2<>"Paid",$C2<>"")

Format: strong red fill.

Rule 4: Due Within Seven Days

=AND($C2>=TODAY(),$C2<=TODAY()+7,$D2<>"Paid")

Format: amber fill.

Rule 5: Paid

=$D2="Paid"

Format: subtle green text or tick icon.

This creates a practical exception-based report without overwhelming the user.

Common Conditional Formatting Mistakes

Highlighting Normal Data Too Strongly

When everything is colourful, genuine problems disappear.

Using Colour Without Meaning

Formatting should correspond to a clear action or status.

Relying Only on Red and Green

Use labels, icons or borders as well.

Using Incorrect Cell References

Poorly placed $ symbols make the rule evaluate the wrong cells.

Applying the Rule to the Wrong Range

The formula and selected range must start on corresponding rows.

Forgetting Blank Cells

Blank values may accidentally meet date, duplicate or zero-value conditions.

Using Count Instead of Business Logic

A value being unusual does not automatically mean it is wrong.

Creating Overlapping Rules

Conflicting formats can make the result unpredictable.

Ignoring Rule Order

Lower-priority rules may overwrite more important warnings.

Using Whole Columns in Huge Workbooks

This can make recalculation unnecessarily slow.

Copying Cells and Duplicating Rules

Repeated copy-and-paste operations can create many fragmented rules.

Using Formatting Instead of Data Validation

Highlighting an invalid value is less effective than preventing it where practical.

How to Audit Existing Rules

In Excel:

1. Open Home > Conditional Formatting.
2. Select Manage Rules.
3. Change the dropdown to This Worksheet.
4. Review each formula and range.
5. Remove duplicate rules.
6. Correct mismatched ranges.
7. Put critical rules first.
8. Check Stop If True settings.
9. Test with sample values.

In Google Sheets, select each range and review the rules in the conditional-formatting sidebar.

Test the Rule Deliberately

Do not assume the formatting works.

Create sample cases for:

• A value just below the threshold
• A value exactly on the threshold
• A value just above the threshold
• A blank cell
• A text value
• A zero
• A completed record
• A duplicated record
• An error

Boundary testing reveals whether operators such as <, <=, > and >= are correct.

Document the Meaning

Add a small legend explaining the formatting.

For example:

A legend prevents users from guessing.

Keep it close to the report without allowing it to dominate the page.

Conditional Formatting Checklist

Before creating rules:

• Define the problem.
• Decide what action the warning should trigger.
• Choose clear business thresholds.
• Confirm the source data is clean.
• Identify the correct range.
• Decide whether to format cells or complete rows.
• Use a small, consistent colour system.
• Plan rule priority.

After creating rules:

• Test boundary values.
• Check blank-cell behaviour.
• Review locked and relative references.
• Confirm the Applies to range.
• Check overlapping rules.
• Add a legend.
• Test new rows.
• Review workbook performance.
• Combine the rules with filters and validation where useful.

Final Thoughts

Conditional formatting is most valuable when it reveals something that might otherwise be missed.

Overdue invoices, duplicated identifiers, missing fields, low stock, declining performance and formula errors can all be surfaced automatically. The spreadsheet becomes easier to scan because exceptions draw attention before they become larger problems.

The best conditional-formatting systems are restrained. They use a few consistent warning states, apply rules to clean data and connect each visual signal with a clear action.

Do not colour every cell. Highlight what needs attention.

A well-designed rule can save hours of manual checking and help users make decisions faster, even when the spreadsheet contains thousands of records.

Need Help Improving Your Excel or Reporting Workflows?

Conditional formatting can reveal problems, but poorly structured data and fragmented spreadsheet rules can still make reports slow and unreliable.

Hamilton Group can help organise business data, improve Microsoft Excel and Microsoft 365 workflows, create clearer reporting systems and troubleshoot business technology.

Visit hgmssp.com, call 0330 043 0069, or book a meeting with one of our experts.