Flag Duplicate Rows in Zoho Sheet Without Deleting Them

Quick answer: Add a helper column that uses COUNTIFS to count matching combinations of the fields that define a record. Flag counts above one, then use conditional formatting to highlight those rows. This preserves the data and makes the duplicate definition visible. Zoho documents COUNTIFS with multiple matching criteria and formula-based row highlighting.

A repeated product name is not necessarily a duplicate record. Decide which fields must agree before applying a rule, and treat the result as a review flag rather than permission to delete anything.

Scope and duplicate definition

Use the English-language Zoho Sheet web application with edit access to a disposable sample. Documentation was checked on October 16, 2026; no numbered web release is asserted. Formulas use the semicolon argument separators shown in Zoho’s help.

For this example, a duplicate means the same Order, Product, Units, and Amount. The unique Row ID is deliberately excluded: it records where a line came from, not whether its business content was repeated. If your real process allows multiple identical lines within one order, this definition may identify legitimate repeats. Add an appropriate line number or transaction identifier before interpreting them as errors.

1. Enter the eight-record sample

Create a sheet named Review with these headers and values in A1:E9. In C9, leave the cell truly empty; do not type the words “leave blank.”

Keep B:E as original inputs. Put Matching rows in F1, Review flag in G1, and Occurrence in H1. The helpers describe the source; they do not replace it.

2. Count full matching combinations

In F2 enter this formula, then fill down to F9:

=IF(COUNTA(B2:E2)<4;"";COUNTIFS($B$2:$B$9;B2;$C$2:$C$9;C2;$D$2:$D$9;D2;$E$2:$E$9;E2))

The four COUNTIFS range/criterion pairs implement an AND test. Every range covers the same eight source rows; the dollar signs keep those boundaries fixed when you fill down. B2, C2, D2, and E2 change to the current row’s criteria. Read the COUNTIFS guide for the matching model.

The outer check uses COUNTA to withhold a duplicate count when any required field is truly empty. This matches Zoho’s non-empty-cell definition. It is a completeness check, not full validation: a space or formula returning empty-looking text needs separate inspection.

In G2 enter and fill through G9:

=IF(COUNTA(B2:E2)<4;"Incomplete";IF(F2>1;"Review duplicate";"Unique"))

IF selects the appropriate label, following the official IF syntax. Here “Unique” means unique under the chosen four-field comparison among complete rows in this selected range. It is not a guarantee that the record is correct.

3. Check exact row results before adding color

There are four flagged records, forming two duplicate groups. All eight original records must remain. If only later copies are flagged, you have created a different rule from the all-members comparison above.

In unused cells J2:J4, check:

The COUNTIF guide and official reference explain these counts. The categories sum to eight; changing the view should not delete any of them.

4. Highlight rows from the reviewed flag

1. Select A2:H9, excluding the header.
2. Choose Format → Conditional Formatting → Classic → Formula.
3. Set the rule formula to =$G2="Review duplicate".
4. Choose a readable fill and click Apply.
5. Check that rows for R001, R003, R005, and R006 are highlighted across the selected columns.

The command is documented in Zoho’s whole-row formatting guide. The dollar sign fixes column G while the row remains relative to the selected range’s first row, 2. Fixing $G$2 would test the same flag for every row and is not the intended rule.

Keep the text flag visible so color is not the only way to identify a review item. If a formula rule is unavailable in your interface, the documented More Rules → Apply to row route can instead test the text Review duplicate in column G. Confirm the same four-row result rather than layering uncertain rules.

5. Optional: identify the later occurrences

To distinguish a group’s first occurrence from its later copies in the current order, enter this in H2 and fill down:

=IF(COUNTA(B2:E2)<4;"";COUNTIFS($B$2:B2;B2;$C$2:C2;C2;$D$2:D2;D2;$E$2:E2;E2))

The start of each range is fixed, but its end expands to the current row. Expected H2:H9 values are 1, 1, 2, 1, 1, 2, 1, blank. Only R003 and R006 have an occurrence above one. Thus there are two later occurrences, even though four records belong to duplicate groups.

This ordering convention does not tell you which record is authoritative. Sorting changes what counts as “first”; compare source timestamps or approved identifiers when choosing which record to retain. Do not delete the later rows merely because their occurrence number is two.

Maintain the review as the data grows

As a controlled extension, append R009, O101, Hub, 1, 60 in A10:E10. Extend every fixed COUNTIFS range from row 9 to row 10, fill F:H to row 10, and extend the formatting range to A2:H10. Also update any summary count ranges.

R002 and R009 should now both have a matching count of 2. You should see six Review duplicate, two Unique, and one Incomplete record. A copied formula that still ends at row 9 will miss part of this new comparison. This example makes no automatic-range-expansion promise.

Matching limits and failure recovery

• Case matters in your identifiers: Zoho documents COUNTIFS as case-insensitive. This recipe is not suitable for distinguishing ABC from abc. The EXACT guide supports case-sensitive pairwise review, but do not assume an untested array formula is a drop-in replacement.
• Descriptions contain operators or pattern-like text: Criteria functions can have special matching rules. Test representative values before using this recipe for arbitrary descriptions; the sample uses simple controlled codes and labels.
• Numeric-looking text or rounding differs: Standardize types deliberately. Two displayed amounts can look equal while their stored values differ; define the business rule before rounding anything.
• Everything appears unique: Check that you did not include the unique Row ID among the matching fields.
• Too many rows are flagged: Check whether one field was omitted, a range shifted, or a legitimate repeated line needs another key.
• Only one cell is colored: Inspect the format’s applied range, not just its formula. Zoho’s conditional-format guide documents editing the source range.

Because the original columns remain untouched, you can correct the helper formulas or remove the review formatting without reconstructing deleted rows. Preserve that separation until a human-approved resolution is recorded.

Verification: Documentation checked on October 16, 2026; duplicate groups, flags, occurrence counts, and append-case results were calculated independently. Not tested in Zoho Sheet, including formula execution and conditional-format rendering.