Excel条件付き書式の使い方 期限切れ・未入力のセルに自動で色をつける設定

月曜の朝、契約の一覧を開いて、更新期限の列を上から順に目で追う。期限が過ぎた契約を見落とさないために、毎週この作業をしている担当の方は多いと思います。

この目視の確認は、Excel の条件付き書式に任せられます。期限が過ぎた行は赤、30日以内は黄、書類が未提出のセルは紫というように、開いた瞬間に色で分かる一覧になります。設定の手順は、Microsoft の日本語のサポートページで確かめました(2026年10月25日確認)。対象は Excel for Microsoft 365・2024・2021・2019・2016で、画面の名前は Windows のものです。

条件付き書式は、条件に合うセルだけ自動で書式を変える機能

条件付き書式は、セルの値に基づいて書式を決めるルールを作る機能です。Microsoft は、データのパターンや傾向をより明確にするのに役立つと説明しています。ルールの入口は、どれも [ホーム] タブの [スタイル] グループにある [条件付き書式] です。

ルールの作り方は、大きく 2 つあります。

「向いている場面」の列は編集部の目安です。

[日付] を選ぶと、[昨日]・[来週] のような相対的な期間で塗れます。ただし、Microsoft のページで確かめられた例は、この 2 つまでです。「30日以内」「7日以内」のように日数を自分で決めたいときは、数式を使います。この記事の中心は、その数式のルールです。

例にする契約一覧(架空)

ここからは、総務の担当者が管理している、架空の契約一覧を例にします。列は次のとおりです。

2行目から最大200行目までに入力する想定で、色は A2 から G200 までの範囲に付けます。

期限の色分けは、TODAY 関数の数式で3段階にする

期限の色分けでは、今日の日付を返す TODAY 関数を使います。Microsoft の説明では、TODAY 関数は現在の日付のシリアル値(Excel が日付の計算に使う番号)を返し、書式に引数はありません。ブックを開いているかどうかに関係なく、シートに現在の日付を表示したいときに便利な関数です。

Microsoft の条件付き書式のページには、証明書の有効期限を例にした設定があります。期限が切れていれば赤、60日以内なら黄で塗り、数式は =B2<TODAY() と =B2<TODAY()+60 です。同じ考え方で、日数を変えて3段階にします。

3つのルールと数式

AND 関数は、すべての条件が TRUE のときに TRUE を返します。ここでは「D 列が空でない」と「期限の条件」の両方を満たしたときだけ色が付きます。$D2<>"" を入れるのは、日付がまだ入っていない行まで赤く塗られないようにするためです。空のセルは、日付の比較では 0 のように扱われやすいからです。この点は Microsoft のページには書かれていないので、実際の一覧で確かめてください。

「7日以内」と「30日以内」の式は、過ぎた期限も条件に含みます。それでも赤が優先されるのは、ルールの順番で赤が上にあるからです。順番の決まりは、あとの章で説明します。

設定の手順

1. 色を付ける範囲を、A2 から G200 まで選ぶ。選び始めるセル(A2)が、数式を書くときの基準になる。
2. [ホーム] タブで [条件付き書式] > [新しいルール] を選ぶ。
3. [ルールの種類を選んでください] で、[数式を使用して、書式設定するセルを決定] を選ぶ。
4. [次の数式を満たす場合に値を書式設定] の欄に、数式を入力する。先頭は等号 (=) で、結果が TRUE か FALSE になる式にする。
5. [書式] を選び、[セルの書式設定] の [塗りつぶし] で色を選んで、[OK] を押す。

Microsoft によると、新しいルールは常に一覧の先頭に追加され、優先順位が最も高くなります。そのため、作る順番は黄(30日以内)、橙(7日以内)、赤(過ぎた)の順にします。そうすると、赤が一番上に並びます。未入力と未提出のルールは、そのあとに作ると、さらに上に並びます。

行全体に色を付ける $ の付け方

数式の $D2 は、「D 列は固定して、行は動かす」という書き方です。Microsoft の説明では、参照は通常、セルの位置を基準にした相対参照です。数式をコピーすると、参照も一緒にずれます。列や行の前にドル記号 ($) を付けると、その部分が固定されます。

行全体を同じ色にするには、$D2 の形にします。B 列のセルでも、G 列のセルでも、その行の D 列を見て判定するからです。D2 と書くと、E 列のセルは E 列の日付を見てしまいます。$D$2 と書くと、全部の行が2行目の日付だけを見て、一斉に同じ色になります。

未入力のセルに色を付ける

担当者が決まっていない契約も、色で見つけたいところです。方法は2つあります。

簡単な方法: [空白] を選ぶ

1. 色を付けたい範囲(たとえば E2:E200)を選ぶ。
2. [条件付き書式] > [新しいルール] > [指定の値を含むセルだけを書式設定] を選ぶ。
3. [次のセルのみを書式設定] の欄で [空白] を選び、[書式] で色を決める。

ただし、この方法は 200 行目までの空の行にも色を付けます。契約が 11 件しか入っていなければ、12 行目から下が全部水色になります。

数式の方法: 取引先が入っている行だけ

そこで、数式を使います。

ISBLANK 関数は、テストの対象が空白セルのとき TRUE を返します。IS 関数のページには、二重引用符で囲んだ数値は文字列として扱われるとも書かれています。見た目が空でも、スペースが入ったセルは空白ではありません。Microsoft も、空白の値はスペースを含むセルとは異なると説明しています。

一覧に「見た目は空なのに、色が付かない」担当者の欄があるときは、スペースが入っていないかを疑います。

提出状況の文字で色を付ける

「未提出」と書かれたセルは、[セルの強調表示ルール] で塗れます。

1. 提出状況の範囲(F2:F200)を選ぶ。
2. [条件付き書式] > [セルの強調表示ルール] > [指定の値に等しい] を選ぶ。
3. 左の欄に「未提出」と入力し、書式を選ぶ。

「提出済」と「確認中」の表記が揺れていると、色が付きません。たとえば「未提出」と「未提出 」(末尾にスペース)は、別の文字として扱われます。[特定の文字列] の [次の値を含む] を使うと、「未提出」を含むセルを拾えます。表記の揺れそのものを防ぐには、Excelのプルダウンの作り方 入力規則で選択肢を作り入力ミスを減らす手順のように、提出状況をプルダウンから選ぶ形にするのが確実です。

ルールの順番と「条件を満たす場合は停止」

ルールが増えると、同じセルに複数のルールが当てはまります。Microsoft の説明では、複数のルールが当てはまるときは、[条件付き書式ルールの管理] の一覧の上から下の順に評価されます。上のルールが、下のルールより優先されます。

当てはまるルールの動きは、次の2通りです。

この一覧の例では、担当者の未入力(水色)と未提出(紫)のルールを、期限の3段階より上に置きました。期限が過ぎた赤い行の中でも、水色と紫のセルは赤に負けずに残ります。順番は、[条件付き書式ルールの管理] でルールを1つ選び、[上へ移動] か [下へ移動] で変えます。

[条件を満たす場合は停止] というチェック ボックスもあります。Microsoft によると、これは下位互換性のためのもので、3つ以上のルールや、同じ範囲に複数のルールをサポートしない以前の Excel での表示をまねるときに使います。最初のルールだけ評価したいときは、最初のルールでオンにします。

この記事の例では、塗りつぶしの色が競合するだけなので、順番を正しく並べれば、チェックを入れなくても同じ結果になります。なお、ルールの書式にデータ バー・カラー スケール・アイコン セットを使っている場合は、このチェック ボックスをオン・オフできません。

ルールが増えて、範囲がばらばらになったとき

一覧を何か月も使っていると、同じ式のルールが何本も並び、[適用先] が「$A$2:$G$9」「$A$11:$G$20」のように細かく割れていることがあります。行をコピーして貼り付けたり、塗りつぶしをドラッグしたりすると起きやすくなります。Microsoft のページにも、条件付き書式が設定されたセルのコピー、貼り付け、フィルの操作をすると、コピー先に新しいルールが作られて、優先順位が変わる場合があると書かれています。

直し方は、次の順です。

1. [ホーム] > [条件付き書式] > [ルールの管理] を開き、[書式ルールの表示] を [このワークシート] にして、全部のルールを出す。
2. 同じ式のルールを探し、適用先が割れていないかを見る。
3. 残すルールを1つ選び、[ルールの編集] か [適用先] の欄で、範囲を =$A$2:$G$200 のようにまとめる。
4. 同じ式のルールが残っていれば、削除する。
5. 色の優先順位を [上へ移動]・[下へ移動] で整え、一覧を目で確認する。

どこに条件付き書式があるか分からないときは、[ホーム] タブの [検索と選択] > [条件付き書式] で、設定のあるセルをすべて見つけられます。どうしても整理できなければ、範囲を選んで [ルールのクリア] > [選択したセルのルールをクリア] を押し、3つのルールを作り直すほうが早く済みます。

うまくいかないときの確認と、使うときの注意

TODAY は、開くたびに変わる

TODAY 関数の日付は、ブックを開いたり再計算したりするたびに今日の日付に更新されます。そのため、昨日の夜は「30日以内」の黄色だった行が、今朝は「7日以内」の橙に変わることがあります。期限そのものは変わらず、判定の基準が毎日動いているだけです。

月末の報告に使うときは、その日の画面を PDF や印刷で残します。固定の日付で判定したいなら、基準日を入れたセルを作って参照します。TODAY で日付が更新されないときは、[ファイル] > [オプション] > [数式] を開き、[計算方法の設定] が [自動] になっているかを確かめます。TODAY 関数のページにある案内です。

日付が文字列だと、色が付かない

見た目は日付でも、中身が文字列のことがあります。他のシステムから取り込んだデータなどで起きやすく、日付どうしの比較が期待どおりに働きません。確かめるには、別のセルに =ISNUMBER(D2) と入力します。TRUE なら日付(数値)、FALSE なら文字列です。

文字列の日付は、DATEVALUE 関数で日付に変換できます。Microsoft のページには =DATEVALUE("2011/02/23") のような例があり、テキストとして入力された日付のシリアル値を返します。別の列に =DATEVALUE(D2) と入れ、その列を見るように数式を書き換える方法があります。

数式がエラーを返すセルには、条件付き書式が適用されません。Microsoft は、IS 関数や IFERROR 関数でエラーを別の値に置き換えるよう案内しています。

色だけに頼らない: 記号の列を足す

色は、白黒で印刷したときや、色の違いが見分けにくい人には伝わりにくいものです。一般的には、色と同じ意味の記号を、文字の列で足しておきます。G 列に、色と同じ判定の数式を入れます。

IF 関数は、条件が真のときと偽のときで別の値を返す関数で、入れ子にして複数の比較ができます。次の式は、日付が空のときは何も表示せず、過ぎたら×、7日以内なら!、30日以内なら△を表示します。

記号の列を足すと、フィルターで絞り込んだり、印刷した紙で期限の近い行を見分けたりできます。

重複に色を付けるルールは、ここでは扱わない

同じ値のセルに色を付ける [重複する値] のルールもあります。重複に色を付けて確かめてから消す手順は、Excelの重複の削除 色付けで確かめてから消す手順と表記の揺れの直し方で説明しています。

最初の1枚を作る段取りと、ほかの表への広げ方

最初の1枚は、次の順で作ります。

同じ設定は、休暇や契約の期限を管理するほかの表にも使えます。たとえば、有給休暇の年5日の取得を管理する表なら、取得の期限の列に、条件付き書式で色を付けられます。TODAY や EOMONTH のような日付の関数は、事務で使うExcelのショートカットと関数 集計と日付の計算が速くなる基本で説明しています。