How do I set up an Excel formula to flag duplicate values across multiple columns?

Ops Acceleration September 23, 2026 · 11 min read

Contents

A duplicate check that works in a clean demo sheet often falls apart the minute it hits a real operations file.

You’ve seen the version of this problem already. Column A has customer IDs, Column C has the same IDs copied in from another export, Column F has notes, someone merged cells down a group of rows to make the report “readable,” and now Excel is flagging the wrong things, missing obvious matches, or turning half the sheet yellow because of blanks. That is not an Excel problem so much as a structure problem.

If you need to flag duplicate values across multiple columns, the clean answer is to compare normalized values, not the display layout. Merged cells, repeated headers, and blank-looking cells break naive formulas because Excel stores the value only in the top-left cell of a merged range. Everything else in that merge looks occupied to a human and blank to a formula.

The formula most people start with, and why it breaks#

If your data is simple, this works:

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

Or, in conditional formatting:

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

That will flag a value in A2 if it appears anywhere in columns A through C.

The problem is that this assumes three things that are rarely true in live reports:

every visible cell actually contains its own value
blanks are true blanks, not part of a merged block
headers and section labels are not mixed into the range

In finance and ops reporting, especially in hand-maintained workbooks, none of those assumptions hold for long. I see this a lot in admin-owned trackers and month-end files where readability won over structure six versions ago.

If the worksheet is built for human scanning instead of machine logic, your duplicate formula will behave like the sheet is lying to it, because it is.

The cleanest setup for duplicates across multiple columns#

Use a helper range first. It is faster, easier to audit, and far more reliable than trying to cram every exception into one giant conditional formatting formula.

Let’s say you want to check duplicates across columns A, C, and F, starting on row 2.

Step 1: Build a normalized helper value for each row#

In G2, create a row-level value that pulls the nonblank value from the columns you care about, or keeps them separate if you want to test each cell individually.

You have two common cases.

Case 1: You want to know whether any value in A, C, or F appears anywhere else in those columns#

Use cell-level checking. Put this in conditional formatting for each column separately:

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

Apply that rule to:

=$A$2:$A$10000

Then create matching rules for C and F:

=AND(C2<>"",COUNTIF($A:$A,C2)+COUNTIF($C:$C,C2)+COUNTIF($F:$F,C2)>1)

=AND(F2<>"",COUNTIF($A:$A,F2)+COUNTIF($C:$C,F2)+COUNTIF($F:$F,F2)>1)

This is the simplest reliable pattern for duplicate values across columns when the same value can show up in different fields.

Case 2: You want to know whether the combination of columns is duplicated by row#

That is a different question. In G2:

=TEXTJOIN("|",TRUE,A2,C2,F2)

Then in H2:

=AND(G2<>"",COUNTIF($G:$G,G2)>1)

That flags duplicate row combinations, not duplicate individual values.

People mix up these two checks constantly. If you need “same invoice number anywhere in these columns,” use Case 1. If you need “same three-field record repeated,” use Case 2.

How to stop blanks and headers from polluting the result#

Most false positives come from junk values, not true duplicates. Blank cells, merged spillover cells, repeated labels like “Total” or “Customer ID,” and cells with invisible spaces all distort the count.

Use a stricter formula.

For a single cell check in A2:

=AND(TRIM(A2)<>"",A2<>"Customer ID",A2<>"Total",COUNTIF($A:$A,A2)+COUNTIF($C:$C,A2)+COUNTIF($F:$F,A2)>1)

If headers vary by sheet, put excluded labels on a separate tab, say Lists!A1:A20, and use:

=AND(TRIM(A2)<>"",COUNTIF(Lists!$A$1:$A$20,A2)=0,COUNTIF($A:$A,A2)+COUNTIF($C:$C,A2)+COUNTIF($F:$F,A2)>1)

That gives you control without editing the rule every month.

A better normalization pattern for messy text#

If IDs or names come in with stray spaces, normalize before counting:

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

Where J:J is a helper column containing normalized values from A, C, and F stacked or referenced consistently.

If you skip normalization, "ABC123" and "ABC123 " are different values to Excel. Humans miss that. Formulas do not.

For a deeper cleanup workflow, this is where finding duplicate records safely in Excel matters. The formula is only as good as the data shape underneath it.

Why merged cells break duplicate formulas#

Here’s the part generic tutorials usually skip.

Merged cells do not store a value in every visible row. Excel keeps the content only in the upper-left cell of the merged area. The rest of the merged cells are effectively empty for formula purposes.

So if A2:A4 is merged and displays PO-10482, only A2 actually contains PO-10482. A3 and A4 look filled, but formulas read them as blank.

That creates two common failures:

a duplicate formula misses matches because it checks A3 or A4, which are technically blank
a duplicate formula flags the wrong rows because the visible grouping does not match the stored cell values

This is why an Excel duplicate formula for merged cells feels inconsistent. It is not inconsistent. It is reading the underlying structure exactly as stored.

What to do instead#

You have three options, in order of reliability:

Approach	Works with merged cells	Auditability	Speed on large files	Recommendation
Use formulas directly on merged ranges	Poor	Poor	Mixed	Avoid if possible
Unmerge and fill down helper values	Excellent	Excellent	Good	Best option
Keep merges, use helper formulas to reference top-left values	Fair	Medium	Medium	Acceptable if you cannot redesign the sheet

If this workbook matters, unmerge the data area and use Center Across Selection for visual formatting instead of Merge & Center. Same look, fewer broken formulas.

The practical fix for merged cells and blank-looking rows#

The best Excel duplicate formula for merged cells starts with a helper column that fills the visible value down to every row in the merged block.

Assume column A contains merged cells. In G2, use:

=A2

In G3, use:

=IF(A3<>"",A3,G2)

Copy down.

What this does:

if the current row has a real value in A, keep it
if the current row is blank because it is inside a merged block, carry forward the last real value

Now G:G contains a usable, row-by-row version of what the sheet visually shows.

You can then run duplicate logic against G:G instead of the merged column:

=AND(G2<>"",COUNTIF($G:$G,G2)>1)

If you need to compare merged A against unmerged C and F:

=AND(G2<>"",COUNTIF($G:$G,G2)+COUNTIF($C:$C,G2)+COUNTIF($F:$F,G2)>1)

That is the most practical Excel duplicate formula for merged cells for people who inherited a report they cannot rebuild today.

If multiple columns contain merges#

Create one helper column per merged source:

G for normalized A
H for normalized C
I for normalized F

Example for H3:

=IF(C3<>"",C3,H2)

Then compare across helper columns:

=AND(G2<>"",COUNTIF($G:$G,G2)+COUNTIF($H:$H,G2)+COUNTIF($I:$I,G2)>1)

This is also the cleanest way to handle multiple columns duplicates when each source column has a different formatting problem.

Key takeaway: if a value is spread across merged cells, fix the data shape first with helper columns, then count duplicates against the normalized helper range, not the merged display range.

How to keep the workbook from crawling#

A duplicate rule that works on 500 rows can choke a workbook at 50,000. The usual culprit is not COUNTIF itself. It is bad range discipline.

Do not point duplicate formulas at entire columns unless you actually need 1,048,576 rows of calculation.

Compare these:

Bad for large files:

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

Better:

=AND(A2<>"",COUNTIF($A$2:$A$50000,A2)+COUNTIF($C$2:$C$50000,A2)+COUNTIF($F$2:$F$50000,A2)>1)

Better still, turn the range into an Excel Table and use structured references. Tables resize automatically without forcing whole-column calculations.

Practical speed rules that actually matter#

Use helper columns instead of nested monster formulas
Excel recalculates simpler formulas faster and they are easier to debug.
Limit the applied conditional formatting range
If your data ends at row 18,240, do not apply formatting to row 100,000.
Normalize once, count once
Trim spaces, fill merged values, and exclude headers in helper columns instead of repeating those steps inside every formatting rule.
Avoid volatile functions for this job
OFFSET, INDIRECT, and similar functions recalculate more often than you want in big workbooks.
Split logic from presentation
Put the duplicate flag in a helper column like IsDuplicate, then let conditional formatting reference that single TRUE/FALSE result.

Here is a solid pattern for large sheets:

Column	Purpose	Formula example
G	Normalized A	`=IF(A2<>"",TRIM(A2),G1)`
H	Normalized C	`=IF(C2<>"",TRIM(C2),H1)`
I	Normalized F	`=IF(F2<>"",TRIM(F2),I1)`
J	Duplicate flag for G	`=AND(G2<>"",COUNTIF($G$2:$G$50000,G2)+COUNTIF($H$2:$H$50000,G2)+COUNTIF($I$2:$I$50000,G2)>1)`

Then format based on =$J2=TRUE.

That is much easier to maintain than hiding all of the logic inside conditional formatting.

When the same value can appear in different columns#

This is where people overcomplicate things.

If invoice 98431 can appear in A, C, or F, and you want to flag it wherever it appears, you do not need a special array formula. You need a consistent comparison set.

Option 1: Sum the counts across each target column#

For A2:

=AND(A2<>"",COUNTIF($A$2:$A$50000,A2)+COUNTIF($C$2:$C$50000,A2)+COUNTIF($F$2:$F$50000,A2)>1)

This is readable and works well.

Option 2: Stack values into one helper column#

If performance or maintainability matters, create a single comparison list on another sheet or helper area:

values from normalized A in M2:M50000
values from normalized C in M50001:M100000
values from normalized F in M100001:M150000

Then use:

=AND(G2<>"",COUNTIF($M$2:$M$150000,G2)>1)

That approach is often faster to audit because there is only one count range.

For teams in Remote / nationwide operations roles who live in export-heavy spreadsheets, this helper-stack method tends to survive handoffs better than clever formulas. The next person can follow it.

If you are using data validation, use it after cleanup, not before#

Excel data validation can help prevent new duplicates, but it is not a cleanup tool for broken merged reports.

Once you have a normalized source column, you can block future duplicate entries with a custom validation formula like:

=COUNTIF($G$2:$G2,G2)=1

Or for direct entry in A:

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

That only prevents duplicates going forward. It does nothing to repair old merged-cell logic.

If your workbook is already messy, clean the structure first, then lock the rule in. Same principle as contact cleanup. You do not want to merge or suppress records until you know what the data is actually saying. That is the same reason merge duplicate contacts without losing data is a process problem, not just a button-click problem.

A setup I’d actually trust in a live operations workbook#

If I were handed a reporting file from a finance coordinator in Danville, California or an ops analyst working remote across multiple U.S. facilities, and they said “flag duplicates across these merged columns without breaking the sheet,” I would do this:

The working sequence#

Copy the tab before touching anything
Unmerge data cells if allowed
If unmerge is not allowed, create helper columns to fill values down
TRIM and normalize the values
Exclude known headers and labels
Use helper duplicate flags, not direct formatting formulas
Apply conditional formatting only to the true data range
Spot-check 10 to 20 known duplicates manually

That last step matters. If your formula says it found 312 duplicates, inspect a sample. Formula confidence should be earned, not assumed.

For broader spreadsheet triage, the cleanest way to audit an operations stack is the same mindset in a bigger container. Find the manual workaround. Price the leak. Fix the structure.

The formula set to copy right now#

If you need one practical answer, use this.

Assume:

merged or messy values in A, C, and F
data starts on row 2
helper columns G, H, I normalize those fields

In G2:

=TRIM(A2)

In G3 and down:

=IF(A3<>"",TRIM(A3),G2)

In H2:

=TRIM(C2)

In H3 and down:

=IF(C3<>"",TRIM(C3),H2)

In I2:

=TRIM(F2)

In I3 and down:

=IF(F3<>"",TRIM(F3),I2)

In J2:

=AND(G2<>"",G2<>"Customer ID",G2<>"Total",COUNTIF($G$2:$G$50000,G2)+COUNTIF($H$2:$H$50000,G2)+COUNTIF($I$2:$I$50000,G2)>1)

Copy down. Then apply conditional formatting to your original data rows using:

=$J2=TRUE

That is the Excel duplicate formula for merged cells pattern I would trust most in a real workbook. It handles blank-looking merged rows, avoids flagging true blanks, works across several columns, and will not slow the file nearly as much as whole-column conditional formatting with repeated logic.

If you want help beyond the formula itself, especially when duplicate checks are just one symptom of a spreadsheet-driven workflow that keeps leaking time, Done-For-You is the faster path. Ops Acceleration steps in, maps the real workflow, fixes the handoffs, and handles the implementation work directly. Book a call through the portal and show them the workbook that keeps breaking.