ExcelやGoogleスプレッドシートで管理表を作っていると、「完了になった行だけ色をつけたい」「期限切れの行を丸ごと目立たせたい」という場面がありますよね。ところが、条件付き書式を設定したつもりなのに、1つのセルだけ色がついたり、なぜか別の行が塗りつぶされたりして、提出前に手が止まることがあります。
ロロメディア編集部でも、記事進行管理表で「ステータスが完了なら行全体をグレーにする」設定を入れたとき、最初はステータス列だけしか色がつきませんでした。原因は、数式の列固定と適用範囲の指定ミス。たった1文字の「$」が抜けていただけで、表全体の見え方が崩れていたんです。
条件付き書式で行ごとに塗りつぶす基本の考え方
条件付き書式で行全体に色をつけるとき、一番大事なのは「条件を見るセル」と「色をつける範囲」を分けることです。
たとえば、A列からF列まであるタスク管理表で、D列のステータスが「完了」になったら、その行全体をグレーにしたいとします。この場合、条件を見るのはD列ですが、塗りつぶしたい範囲はA列からF列です。
ここを間違えると、D列だけ色がついたり、A列だけ色がついたりします。見た目は小さなミスですが、業務では地味に痛いんですよ。会議前に進行管理表を共有したとき、完了タスクが一目で分からないと、確認に時間がかかります。
行全体を塗るには数式で条件を指定する
行ごとに塗りつぶす場合は、通常の「セルの値が次の値に等しい」だけでは足りないことがあります。
なぜなら、「ステータス列の値を見て、同じ行の他のセルにも色をつける」という動きが必要だからです。このとき使うのが、数式を使った条件付き書式です。
たとえば、2行目からデータが始まり、D列にステータスが入っているなら、Excelでは次のように指定します。
=$D2=”完了”
この式は、「同じ行のD列が完了なら」という意味です。Dの前にある「$」がポイントで、列を固定しています。行番号の2には「$」をつけません。
「$」の位置を間違えると行全体に反映されない
条件付き書式で詰まりやすいのが、「$」の位置です。
提出前の管理表で、完了行だけ色をつけたいのに、なぜか全行が塗られたり、1列だけ反応したりすることがあります。焦って色設定を変えても直りません。原因は、ほとんどの場合、数式内の参照がずれていることです。
覚えるのはこれだけで大丈夫です。
| やりたいこと | 数式例 | 意味 |
|---|---|---|
| D列を見て行全体を塗る | =$D2=”完了” | D列だけ固定する |
| D2だけを完全固定する | =$D$2=”完了” | すべての行がD2だけを見る |
| 各セル自身を判定する | =D2=”完了” | 列も行も動く |
行ごとに塗りつぶしたいなら、基本は「列だけ固定」です。つまり「=$D2」の形になります。
ここを「=$D$2」にしてしまうと、すべての行がD2だけを見ます。D2が完了なら全行が塗られ、D2が空ならどこも塗られません。見た目にはバグのようですが、数式としては正しく動いている状態です。
Excelで条件付き書式を使って行ごとに塗りつぶす方法
Excelで行全体を塗りつぶす場合は、最初に範囲を選んでから条件付き書式を作ります。
この順番がかなり大事です。先に1セルだけ選んで設定してしまうと、そのセルにしか色がつきません。あとから適用範囲を直すこともできますが、慣れていないとそこでまた迷います。
ここでは、A列からF列までの表で、D列が「完了」になった行をグレーにする例で進めます。
Excelでステータスが完了の行を塗りつぶす手順
作業中の表で「完了だけ色をつけたい」と思ったとき、まず見てほしいのはデータの開始行です。
見出しが1行目で、データが2行目から始まるなら、数式も2行目を基準にします。ここを3行目で始めてしまうと、1行ずれて色がつくことがあります。レビュー前の表でこのズレが出ると、完了していないタスクまで完了扱いに見えてしまうので注意してください。
手順は次の通りです。
- 色をつけたい範囲を選択する
- ホームタブから「条件付き書式」を開く
- 「新しいルール」を選ぶ
- 「数式を使用して、書式設定するセルを決定」を選ぶ
- 数式欄に =$D2=”完了” と入力する
- 「書式」から塗りつぶし色を選ぶ
- OKで保存する
ここで選ぶ範囲は、D列だけではありません。A2:F100のように、色をつけたい表全体を選びます。条件を見るのはD列ですが、色を塗る範囲はA列からF列だからです。
適用範囲は表全体にする
Excelで失敗しやすいのが、適用範囲をステータス列だけにしてしまうことです。
たとえばD列だけ選んで条件付き書式を設定すると、当然D列だけが塗られます。行全体を塗りたいなら、A2:F100のように横方向も含めて選ぶ必要があります。
ロロメディア編集部でも、原稿管理表で「公開済み」の行を薄いグレーにしたとき、最初はステータス列だけが塗られていました。見た目としては悪くないのですが、担当者名や納期が目立たず、確認に時間がかかりました。
行全体を塗る目的は、状態を一目で判断することです。ステータス列だけ色がついても、表を横に見なければいけないなら、条件付き書式の効果は半分になります。
条件付き書式ルールの管理で範囲を確認する
設定後にうまく動かない場合は、まず「ルールの管理」を見てください。
Excelでは、ホームタブの「条件付き書式」から「ルールの管理」を開くと、どの範囲にどの条件が設定されているか確認できます。ここで「適用先」がD2:D100になっていたら、D列だけにしか反映されません。
正しく行全体にしたいなら、適用先を次のようにします。
=$A$2:$F$100
適用先では、範囲全体を固定して指定します。数式では「$D2」のように行を動かしますが、適用先は塗りたい範囲そのものを指定するので、表全体をしっかり入れてください。
Googleスプレッドシートで行ごとにセルを塗りつぶす方法
Googleスプレッドシートでも、考え方はExcelとほぼ同じです。
ただし、メニュー名や設定画面が違います。Excelに慣れている人ほど、「新しいルール」が見つからずに止まることがあります。Googleスプレッドシートでは、右側に条件付き書式の設定パネルが表示される形です。
Googleスプレッドシートでカスタム数式を使う
Googleスプレッドシートで行全体を塗るときは、「カスタム数式」を使います。
たとえば会議前にタスク表を整理していて、完了行だけ薄くしたいとします。1行ずつ手で塗ると、あとからステータスが変わったときに直し忘れますよね。条件付き書式なら、D列を「完了」に変えた瞬間に自動で行全体の色が変わります。
手順は次の通りです。
- A2:F100のように塗りたい範囲を選ぶ
- メニューの「表示形式」から「条件付き書式」を開く
- 「セルの書式設定の条件」で「カスタム数式」を選ぶ
- 数式欄に =$D2=”完了” と入力する
- 塗りつぶし色を選ぶ
- 「完了」を押す
Googleスプレッドシートでも、D列の前だけ「$」で固定します。これで、各行のD列を見ながら、A列からF列まで色をつけられます。
範囲の開始行と数式の行番号をそろえる
Googleスプレッドシートで特に多いミスが、範囲と数式の行番号ズレです。
たとえば適用範囲がA3:F100なのに、数式を=$D2=”完了”にしていると、1行ずれて判定されることがあります。自分では完了行に色をつけたつもりなのに、1つ下の行が塗られる。提出前の進行表ならかなり焦ります。
適用範囲がA2:F100なら、数式は=$D2=”完了”です。適用範囲がA3:F100なら、数式は=$D3=”完了”にします。
つまり、数式の行番号は「適用範囲の一番上の行」に合わせます。これだけ覚えておくと、ほとんどのズレは防げます。
Googleスプレッドシートで空白行まで色がつくときの対処
Googleスプレッドシートでは、表の下にある空白行まで色がつくことがあります。
原因は、条件式が空白にも当てはまるようになっている場合です。たとえば「D列が空白なら色をつける」という条件を作ると、データが入っていない下の行まで全部塗られます。
この場合は、A列など必ず入力される列も条件に加えます。
=AND($A2<>””,$D2=”未対応”)
この式は、「A列が空白ではなく、D列が未対応なら」という意味です。案件名やタスク名がA列に入っている表なら、空白行を除外できます。
条件付き書式で行全体に色がつかない原因と直し方
条件付き書式がうまくいかないとき、原因はだいたい決まっています。
色がつかない、違う行が塗られる、1セルだけ反応する、全行が塗られる。この4パターンを見れば、どこを直すべきか分かります。
焦って最初から作り直す前に、原因を切り分けましょう。作り直すより、数式と適用範囲を1つずつ確認したほうが早いです。
1セルだけ色がつく原因は適用範囲が狭い
ステータスセルだけ色がつく場合は、適用範囲が狭い可能性が高いです。
たとえば、D2:D100だけを選んで条件付き書式を作ると、D列しか塗られません。これは設定ミスというより、Excelやスプレッドシートが指定された範囲どおりに動いているだけです。
直すには、ルールの管理画面で適用範囲を変更します。
A2:F100のように、行全体として塗りたい範囲を指定してください。列数が増える可能性があるなら、A2:H100など少し広めに取っても構いません。ただし、シート全体を指定すると動作が重くなる場合があるので、必要な範囲に絞るのが実務では安全です。
違う行が塗られる原因は行番号のズレ
別の行が塗られる場合は、数式の行番号が適用範囲と合っていません。
見出しが1行目、データが2行目からなら、適用範囲はA2:F100、数式は=$D2=”完了”です。ここで数式を=$D1=”完了”にしてしまうと、1行上のD列を見て判定します。
実務でこのミスが怖いのは、見た目だけでは気づきにくいことです。完了していない案件が完了色になっていると、会議で確認漏れが起きる可能性があります。
直し方は単純です。適用範囲の最初の行と、数式内の行番号を合わせます。A5:F100を選んでいるなら、数式は=$D5=”完了”です。
全行が塗られる原因はセルを完全固定している
すべての行が同じ色になる場合は、数式でセルを完全固定している可能性があります。
典型例は、=$D$2=”完了”です。この式は、どの行に対してもD2だけを見ます。D2が完了なら全行が塗られ、D2が完了でなければどの行も塗られません。
行ごとに判定したいなら、行番号の前に「$」をつけないでください。
正しい形は、=$D2=”完了”です。D列は固定し、行番号は動かす。これが行ごとに塗る条件付き書式の基本になります。
条件が合っているのに色がつかない原因は文字の違い
数式も範囲も合っているのに色がつかない場合、セルの文字が微妙に違っていることがあります。
たとえば、見た目は「完了」でも、実際には「完了 」のように後ろに空白が入っているケースです。コピー貼り付けで管理表を作っていると、この空白が混ざります。提出前に色がつかず、何度も数式を見直して疲れるパターンですね。
確認する方法は、条件セルを直接クリックして、数式バーを見ることです。余分なスペースがあれば削除してください。
入力ミスを防ぐなら、ステータス列をプルダウンにするのがおすすめです。「未対応」「対応中」「完了」のように選択式にしておけば、表記ゆれが減ります。
実務で使える条件付き書式の数式例
ここからは、実際の業務でそのまま使える数式を紹介します。
条件付き書式は、1回覚えるとタスク管理、売上管理、問い合わせ管理、採用管理、記事制作管理まで使えます。表を見る時間が短くなるので、地味ですがかなり効くんです。
ここでは、データが2行目から始まり、A列からF列まで塗る前提で説明します。
ステータスが完了なら行をグレーにする
タスク管理表で一番使いやすいのが、完了行をグレーにする設定です。
作業済みの行が薄くなるだけで、未対応タスクがかなり見やすくなります。ロロメディア編集部でも、記事制作表では公開済みの記事をグレーにして、確認すべき原稿だけが残るようにしています。
数式は次の通りです。
=$D2=”完了”
D列にステータスが入っている場合、この式でD列が完了の行だけ塗れます。適用範囲はA2:F100のように、色をつけたい表全体にしてください。
「完了」ではなく「公開済み」「対応済み」「済」などを使っている場合は、数式内の文字を表に合わせます。大事なのは、セルの中の文字と数式の文字を完全一致させることです。
期限切れの行を赤くする
期限管理で使うなら、日付を使った条件付き書式が便利です。
たとえばC列に期限、D列にステータスが入っていて、期限を過ぎても完了していない行を赤くしたい場合は、次のようにします。
=AND($C2<TODAY(),$D2<>”完了”,$C2<>””)
この式は、「C列の日付が今日より前で、D列が完了ではなく、C列が空白ではない場合」という意味です。TODAYは今日の日付を返す関数です。
ここでC列が空白ではない条件を入れているのは、空白行まで赤くなるのを防ぐためです。期限未入力の空白行が全部赤くなると、表全体が警告だらけになって見づらくなります。
担当者が自分の行だけ色をつける
チームで使う表なら、担当者ごとに色をつける設定も便利です。
たとえばB列に担当者名が入っていて、「佐藤」の行だけ色をつけたいなら、次の式を使います。
=$B2=”佐藤”
この設定を使うと、自分のタスクだけを一目で見つけられます。ただし、担当者ごとに色を増やしすぎると表が派手になりすぎます。実務では、重要な担当者や確認対象だけに絞ったほうが見やすいです。
担当者名の表記ゆれにも注意してください。「佐藤」「佐藤さん」「Sato」が混ざっていると条件が反応しません。名前もプルダウンにしておくと、ミスが減ります。
数値が基準を超えた行だけ塗る
売上管理や在庫管理では、数値条件を使うことがあります。
たとえばE列の売上が100,000円以上なら行を塗りたい場合は、次の式です。
=$E2>=100000
注意したいのは、数字が文字列として入っている場合です。見た目は100,000でも、文字列扱いだと正しく判定されないことがあります。データをコピーしたときに起きやすいので、数値条件が動かない場合はセルの表示形式を確認してください。
複数条件で行を塗り分ける方法
条件付き書式は、複数ルールを組み合わせるとかなり実務的になります。
たとえば、期限切れは赤、対応中は黄色、完了はグレーのようにすると、表を開いた瞬間に優先順位が分かります。会議前に「どれから確認しますか」と迷う時間が減ります。
ただし、ルールを増やしすぎると管理が難しくなります。色が競合したり、どの条件が効いているのか分からなくなるからです。
AND関数で複数条件を同時に満たす行を塗る
AND関数は、複数の条件をすべて満たすときだけ反応する関数です。
たとえば、C列の期限が過ぎていて、D列が完了ではない行を赤くするなら、次の式を使います。
=AND($C2<TODAY(),$D2<>”完了”)
この式は、期限切れかつ未完了の行だけに色をつけます。期限が過ぎていても完了済みなら塗られません。
実務では、この条件がかなり使えます。未対応の遅延だけを目立たせられるので、完了済みの古いタスクまで警告色になるのを防げます。
OR関数でどれか1つに当てはまる行を塗る
OR関数は、複数条件のうち1つでも当てはまれば反応する関数です。
たとえば、D列が「保留」または「要確認」の行を黄色にしたい場合は、次の式です。
=OR($D2=”保留”,$D2=”要確認”)
この設定を入れると、確認が必要な行をまとめて目立たせられます。会議前に「保留」と「要確認」を別々に探す必要がありません。
ただし、OR条件は広く反応しやすいので、色がつく行が増えすぎることがあります。表全体が黄色だらけになる場合は、条件を絞るか、色を薄くしてください。
優先順位をつけるならルールの順番を見る
複数ルールを使う場合は、ルールの順番が重要です。
たとえば、期限切れは赤、完了はグレーというルールが同じ行に当てはまることがあります。完了済みでも期限が過ぎていれば、どちらの色を優先するか決めなければいけません。
Excelでは、条件付き書式のルール管理画面で順番を変更できます。重要なルールを上に置き、必要に応じて「条件を満たす場合は停止」を使います。
Googleスプレッドシートでも、ルールの並びによって表示が変わることがあります。複数色を使うなら、最後にサンプル行を作って、想定どおりの色になるか確認してください。
条件付き書式を仕事で使いやすくする設計のコツ
色が多すぎる表は、パッと見たときに何が重要か分かりません。信号機のように赤・黄・グレー程度に絞るほうが、実務では使いやすいです。
表は見た目を飾るためではなく、判断を早くするためにあります。条件付き書式も同じです。
色は3種類までに絞ると見やすい
条件付き書式で失敗しやすいのが、状態ごとに色を増やしすぎることです。
未対応は赤、対応中は青、保留は黄色、完了は緑、確認中は紫、差し戻しはオレンジ。こうなると、表を見た人が色の意味を覚えられません。共有会議で「この色は何でしたっけ」と確認が入り、かえって時間がかかります。
おすすめは、次のように役割で色を分けることです。
| 色の役割 | 使う場面 |
|---|---|
| 赤系 | 期限切れ、緊急、要対応 |
| 黄系 | 確認中、保留、注意 |
| グレー系 | 完了、対象外、非表示に近い扱い |
この程度なら、初めて表を見る人でも意味を理解しやすいです。色の意味は、表の上部に小さく凡例として書いておくとさらに親切です。
プルダウンと組み合わせるとミスが減る
条件付き書式は、入力値が正しくないと動きません。
だから、ステータス列は自由入力にしないほうがいいです。「完了」「完了済み」「済」「対応済」といった表記ゆれが出ると、条件式が反応しない行が出ます。
実務では、データの入力規則でプルダウンを作るのがおすすめです。
ExcelでもGoogleスプレッドシートでも、ステータスを選択式にできます。選択肢を固定しておけば、条件付き書式の数式も安定します。
表の列構成を変えたら条件付き書式も見直す
表に列を追加したり、列順を変えたりしたときは、条件付き書式も確認してください。
たとえば、ステータス列がD列からE列に移動したのに、数式が=$D2=”完了”のままだと、別の列を見て判定してしまいます。これが一番怖い。色はついているのに、正しい条件でついていないからです。
列を動かした後は、必ずルール管理を開きます。数式の参照列と適用範囲が、今の表に合っているか確認してください。
条件付き書式が重い・壊れるときの見直しポイント
条件付き書式を大量に入れると、ファイルが重くなることがあります。
特に、行全体に対して何千行も設定している表では、スクロールや入力が遅くなることがあります。締切前に管理表が固まると、かなりストレスですよね。
動作が重いときは、設定を増やす前に整理してください。
適用範囲を必要な行数に絞る
よくあるのが、A:Fのように列全体へ条件付き書式を設定しているケースです。
小さな表なら問題ありませんが、行数が増えると余計な範囲まで判定されます。実務では、A2:F500やA2:F1000のように、必要な範囲へ絞るほうが安全です。
もちろん、今後データが増える表なら少し余裕を持たせて構いません。ただし、使っていない何万行まで対象にする必要はありません。
似たルールは1つにまとめる
条件付き書式が増えると、どのルールが効いているのか分かりにくくなります。
たとえば、「保留なら黄色」「要確認なら黄色」「確認待ちなら黄色」と3つのルールを作るより、OR関数で1つにまとめたほうが管理しやすいです。
=OR($D2=”保留”,$D2=”要確認”,$D2=”確認待ち”)
このようにまとめれば、あとから色を変えるときも1つのルールを直すだけで済みます。
コピー貼り付けでルールが増殖していないか確認する
Excelでは、セルをコピーして貼り付けたときに条件付き書式のルールが増えることがあります。
見た目は同じでも、裏側では似たルールが何個も並んでいる状態です。こうなると、ファイルが重くなったり、想定外の色がついたりします。
ルール管理を開いて、同じようなルールが複数並んでいないか確認してください。不要なものは削除し、1つのルールにまとめます。
まとめ：行ごとに塗りつぶすなら「列固定」と「適用範囲」がすべて
条件付き書式で行ごとにセルを塗りつぶすときは、難しい関数よりも基本設定が大事です。
まず、色をつけたい範囲を表全体で選びます。次に、条件を見る列だけを「$」で固定します。たとえばD列のステータスが「完了」なら、数式は=$D2=”完了”です。
うまくいかないときは、適用範囲、数式の行番号、$の位置、セル内の文字を確認してください。1セルだけ塗られるなら適用範囲が狭い。違う行が塗られるなら行番号がずれている。全行が塗られるなら=$D$2のように完全固定している可能性があります。
実務で使うなら、完了行をグレー、期限切れを赤、確認中を黄色くらいに絞ると見やすくなります。さらにステータス列をプルダウンにしておけば、表記ゆれによるミスも防げます。
条件付き書式は、表をきれいにするための機能ではありません。確認漏れを減らし、判断を早くするための機能です。会議前、提出前、進行管理の確認前に「どこを見るべきか」が一瞬で分かる表になれば、それだけで仕事はかなり楽になりますよ。
参考記事
・Microsoft サポート｜Excel で条件付き書式を使用して情報を強調表示する
・Microsoft サポート｜Excel for Mac で数式を使用して条件付き書式を適用する
・Google ドキュメント エディタ ヘルプ｜Google スプレッドシートで条件付き書式ルールを使用する
