Googleスプレッドシートで顧客リストや商品マスタを管理していると、「同じデータが重複して登録されていないか？」を確認する作業が必ず発生します。
数百件、数千件のデータを目視でチェックするのは現実的ではありませんし、見落としによる送信ミスや集計エラーなどの重大なトラブルに繋がります。
そんな時は、スプレッドシートの「条件付き書式」と「 COUNTIF 関数」を組み合わせることで、重複したデータを自動的に赤色などで目立たせる（ハイライトする）仕組みが作れます。
この記事では、最も確実な重複チェックの設定手順と、初心者が必ず陥る「すべてのセルが赤く塗られてしまうエラー」の解決法まで分かりやすく解説します。
💡 この記事で解決するポイント
- 条件付き書式とCOUNTIF関数で重複セルに色をつける基本手順
- そのままコピペで使える「重複判定用カスタム数式」
- 【要注意】全セルが赤く塗られてしまう「数式ズレ」エラーの原因と対策
- 1件目はそのまま残し、「2件目以降のダブリ」だけを色付けする応用数式
【恐怖】数式を間違えて「全顧客が重複」判定になった話
条件付き書式での重複チェックは非常に便利ですが、数式の設定を1文字でも間違えると大惨事になります。私のパニック体験談を聞いてください。
しかし、ネットで拾った数式をよく分からずに適当にコピペした結果、適用した瞬間に『リストの数千件がすべて真っ赤に塗りつぶされる』という大惨事に！
『えっ！？顧客全員ダブってるの！？システムバグった！？』とパニックになり、危うく顧客データを全消去するところでした。
原因は、条件付き書式の『適用範囲の開始行（A2）』と『数式内の参照セル（A1）』が1行ズレていたこと。スプレッドシートは1行のズレで判定が崩壊して全セルがエラー表示になるため、数式の意味を理解して一言一句正確に設定することが絶対に必要だと痛感しました。
「コピペで動かない」「全部に色がついた」という方は、この記事で紹介する「範囲とセルの同期ルール」を必ず確認してください。
「重複の色付け」と「重複の削除」の使い分け
スプレッドシートには、標準機能として「重複を削除」という機能も備わっています。これと「条件付き書式（色付け）」はどう使い分けるべきでしょうか？
| 処理の種類 | データの変化 | 最適な用途・シチュエーション |
|---|---|---|
| 条件付き書式（色付け） | データは消えず、セルに色が付くだけ。 | 「なぜ重複したのか」を目視で確認・調査したい場合。安全。 |
| 「重複を削除」機能 | 重複した行が即座に物理削除される。 | 問答無用でデータを綺麗にしたい時。※必要なデータも消えるリスクあり。 |
どちらを残すか判断が必要な顧客リストなどは、必ず「条件付き書式での色付け」を行ってから目視で精査してください。
【基本手順】重複したデータを自動で色付けする方法
ここでは「A列（A2以降）」に入力されたデータから重複を探す手順を解説します。
- 範囲を選択する： 色を付けたい範囲（例：
A2:A1000）をドラッグして選択します。 - 条件付き書式を開く： 上部メニューの
表示形式＞条件付き書式をクリックし、右側に設定パネルを開きます。 - 条件を指定する： 「セルの書式設定の条件」のプルダウンを一番下までスクロールし、
カスタム数式を選択します。 - 数式を入力する： 以下の「コピペ用数式」を入力します。
=COUNTIF ($A$2:$A, A2)>1最後に「書式設定のスタイル」から目立つ背景色（赤など）を選んで「完了」を押せば設定完了です。
【応用技】2件目以降の「ダブリ部分」だけを色付けする数式
基本の数式では、重複している「大元の1件目」も赤くなってしまいます。
「元データは残して、あとから重複して追加された2件目以降だけを赤くして消したい」という実務では、以下の数式を使用します。
=COUNTIF ($A$2:A2, A2)>1この数式では、上から順にカウント（相対参照）していくため、初めて登場したデータは「1」となり色は付かず、同じデータが再び現れた時に「2」以上となって色が付く仕組みです。
よくある質問（FAQ）とエラーの解決法
Q1: 指定した範囲のすべてのセル（関係ない行まで）真っ赤に塗られてしまいます！
A: 適用範囲と数式がズレているのが原因です！
たとえば、条件付き書式のパネルにある「適用先（範囲）」が A2:A100 なのに、カスタム数式で =COUNTIF ($A$2:$A, A1)>1 のように「A1」を指定してしまうと、スプレッドシートの判定が1行ずつ上にズレて崩壊し、全セルがおかしな色になります。
必ず「適用先の開始行」と「数式末尾のセル参照」を A2 でピッタリ一致させてください。
Q2: 色がついた重複行だけをまとめて上に集めて確認したいです。
A: フィルター機能の「色で並べ替え」を使いましょう。A列のフィルターアイコン（逆三角形）をクリックし、 色で並べ替え ＞ 塗りつぶしの色 から設定した赤色を選ぶと、重複データだけが画面上部にズラッと集まり、比較が劇的に楽になります。
まとめ：数式の「適用範囲」と「参照セル」を揃えるのが絶対ルール！
重複データの自動色付けにおけるポイントまとめは以下の通りです。
- 基本ツール： 条件付き書式の「カスタム数式」に
COUNTIFを使う。 - 基本の数式：
=COUNTIF ($A$2:$A, A2)>1（重複すべてに色付け） - 応用の数式：
=COUNTIF ($A$2:A2, A2)>1（2件目以降のダブリのみ色付け） - 最重要の鉄則： 適用範囲の開始行（A2など）と数式末尾のセル（A2）を絶対に合わせる！ズレると全セルがバグる。
この仕組みをマスターすれば、何千件のデータが追加されても重複が発生した瞬間に赤く光って知らせてくれる最強のエラーチェックリストが完成します。正しい数式を入力して、データクレンジングの時間を大幅に短縮しましょう！
