Excelの空白セルを除外して今日以前の日付をハイライトする方法徹底解説

Excelの条件付き書式をうまく活用できるようになると、日々の業務がびっくりするほどスムーズになります。私も最初は関数を組み合わせる作業に戸惑いましたが、いったんコツをつかむと自分なりにカスタマイズできるようになり、手間が激減しました。今回は、特に「空白セルを除外しつつ、今日以前の日付を自動でハイライトする」設定方法を中心に、実践的なテクニックを交えながらご紹介していきます。実際に私が抱えていたちょっとしたトラブルや解決策もご紹介しますので、ぜひ最後までご覧になってみてください。

Excelの条件付き書式を使いこなすメリット

条件付き書式を使うと、データの入力ミスを早期に発見できたり、重要な情報を一目で把握したりすることができます。私が勤めている部署では、毎日更新する進捗管理表で期限切れのタスクを一瞬で見つけられるようになり、上司からの「対応遅れはない？」というチェックも受けやすくなりました。日付を基準にして色分けができると、一目瞭然で仕事の優先順位を立てられるのは大きなメリットだと感じています。もしExcelでデータ管理をされているなら、一度設定しておくと毎日の業務が格段に楽になるはずです。

よくある失敗事例

Excelの条件付き書式を設定する際、慣れていないとどうしてもうまくいかないケースが出てきます。私も最初の頃は同じミスを繰り返していました。ここでは、私自身がつまずいたポイントを中心に、よくある失敗事例をご紹介します。

空白セルと文字列セルの混在

本来は日付が入力されているはずのセルに、なぜか空白や文字列が混ざっていることがあります。実は「スペースだけ入力されていて見た目では空白に見えるセル」や「数字のようでテキスト扱いされているセル」が原因で、条件付き書式が動作しないことが多いです。私の職場でも、他の担当者が入力したデータを取り込むときにスペースが含まれていたことがあり、条件付き書式が反応せずに困った経験があります。

絶対参照と相対参照の使い分けミス

条件付き書式で数式を使う場合、セルの参照の仕方がポイントになります。たとえば絶対参照($マークを付ける)なのか、相対参照なのかを誤ると、設定した範囲全体に思わぬ結果が出ることもあります。私はかつて、J列全体をハイライトしたいのに、なぜかJ3だけが色づいたり、逆にシート全体が誤ってハイライトされたりという失敗をしたことがあります。そこで一度しっかりAbsolute/Relativeの考え方を学んでおくと、後々のトラブルを防ぎやすいです。

今日以前の日付をハイライトする基本手順

では、今回のメインテーマでもある「セルが空白でない」かつ「今日以前の日付をハイライトする」設定方法を説明します。具体的にはAND関数とTODAY関数を組み合わせた数式を使い、条件付き書式に設定します。

数式の書き方

数式としては、ANDを用いて下記のようにまとめる形です。

=AND(J3 <= TODAY(), J3 <> "")

このようにすることで「セルが空白でなく、かつ、今日以前の日付であればTRUE」という条件を満たすセルだけがハイライトされます。J3は条件付き書式で指定する範囲の先頭セルを指しており、範囲をJ3:J36に設定するなら、このように書きます。

参照範囲の指定

実際に条件付き書式の画面で設定する際は、まずハイライトしたい範囲(J3:J36など)をまとめて選択します。次に「新しいルール」または「ルールの管理」を開き、「数式を使用して…」を選びます。数式欄に上記のAND関数を書き込み、好きな書式(背景色や文字色)を指定して完了です。これで、対象範囲内のセルが空白でなく、今日以前の日付なら自動的にハイライトされます。

私は今までいろいろ試行錯誤しましたが、最終的にはこの書式がシンプルかつ安定して機能することに落ち着きました。

応用的な条件付き書式

Excelを使い込んでいると、「今日以前の日付だけではなく、特定の曜日や特定の条件で色分けしたい」というニーズが出てくることがあります。ここからは、AND関数やTODAY関数を応用した少し高度な設定の例をご紹介します。

土日だけ色を変える場合

土日を判定するにはWEEKDAY関数を使います。WEEKDAY(セル参照, 2)が6または7であれば土日となります。AND関数と組み合わせれば、「セルに日付が入っていて、その日付が土日」という条件で色を変えることができます。

例：日付が土日でかつ今日以前

もし「日付が土日で、なおかつ今日以前なら赤色でハイライトしたい」などの条件を設定する場合は、次のような数式が考えられます。

=AND(
  WEEKDAY(J3,2) >= 6,
  J3 <= TODAY(),
  J3 <> ""
)

この例ではWEEKDAY(J3,2)が6または7なら土曜・日曜になります。そこにJ3 <= TODAY()の条件をAND関数で追加することで、土日かつ今日以前の日付を抽出できます。さらにJ3 <> “”を入れて、空白セルやテキストが混じった場合を排除します。

未来日を別の色で強調する方法

逆に、今日より先の日付を示すセルをハイライトする設定を入れたいというシーンもあります。たとえば納品期限が明日以降のものを黄色で強調し、来週以降のものは緑で強調するなど、細かいルール分けを行うときに便利です。

AND関数の活用例

たとえば「今日より1日でも先の日付なら黄色にする」設定をしたいなら、次の数式を条件付き書式に入れることで可能です。

=AND(J3 > TODAY(), J3 <> "")

また「今日から7日以内に納品期限がくるものをオレンジにする」などの応用も、DATEDIFやTODAY関数を組み合わせれば設定可能です。たとえば

=AND(J3 - TODAY() <= 7, J3 > TODAY(), J3 <> "")

このようにすれば「今日より後でかつ7日以内」という条件が設定できます。

日付データの扱いで注意すべきポイント

日付の扱いはExcelで非常に便利ですが、同時に気をつけなければいけない点も多いです。表面的には見えない不具合が潜んでいる場合があるので、ここではチェックしておくと便利なポイントをまとめていきます。

シリアル値と表示形式のギャップ

Excelの日付は内部的にシリアル値(連続した数値)で管理されています。たとえば「2025/02/05」は数値としておよそ45000前後の値を持っているようなイメージです。表示上は「2025/02/05」と見えていても、実態は数字です。そのため、数式で比較するときはシリアル値同士の比較となります。ただし、参照元のセルが文字列扱いの場合はうまく比較できません。

数値としての「日付」が正しく入力されていると、TODAY()との大小比較がスムーズに行えます。

セルがテキスト扱いになっているケース

ユーザーが手入力で日付を入れたとき、半角と全角の違いや、不要なスペースによって文字列扱いになる場合があります。こうしたセルは日付としての比較ができず、条件付き書式で思うような結果が得られません。私が以前、外部からもらったデータをマクロで自動取り込みしたとき、一部のセルが「文字列扱いの日付」だったために予期せぬ箇所がハイライトされてしまったことがありました。テキスト扱いになっているセルは、数値や日付に変換するか、手入力し直す必要があります。

文字列扱いのセルが混在すると、条件付き書式がまったく機能しないことがあるので注意が必要です。

さらなる活用例：条件を組み合わせるときのポイント

条件付き書式は複数のルールを組み合わせることもできます。たとえば、同じ範囲に対して「期限切れは赤」「3日以内に期限がくるものはオレンジ」「残り7日以内は黄色」などの階層的なルールを設定すると、ひと目で管理状況を把握できます。ただし、複数ルールを設定する場合は、どちらを優先的に評価するかなど、ルールの重複に注意してください。

ルールの優先順位

Excelの条件付き書式では、複数の条件が重なるときの優先度を「ルールの管理」画面から設定できます。上にあるルールほど優先度が高くなり、条件を満たした場合には下のルールよりも上のルールが適用されます。私も以前、この優先順位をうっかり確認せずに、全然違うセルがハイライトされたことがありました。複数ルールを適用するときは、どの条件を上に持ってくるかを意識しましょう。

例：期限切れを最優先に赤色で表示

たとえば期限切れ(今日より前の日付)を赤色で表示し、その次に「まだ期限内だけど3日以内のもの」をオレンジ表示にする場合、まずは「今日より前の日付であれば赤」というルールを作り、それをルール一覧の最上段に配置します。その下に「3日以内ならオレンジ」のルールを作ることで、適切に色が使い分けられます。

私が実際に使っているテクニックや事例

ここからは、私が普段の業務で「空白セルを除外して今日以前の日付をハイライトする」以外に、どのように条件付き書式を活用しているのかをいくつかご紹介します。身近な事例なので、みなさんの使い方の参考になればうれしいです。

タスク管理表での経過日数表示

私の部署では、タスク開始日と今日の日付との差を計算し、経過日数が一定以上になったら背景を赤くするというルールを設定しています。具体的には、タスク開始日のセルをA列、完了予定日をB列、今日の日付との経過日数をC列に計算させて、そのC列が10日を超えたら「遅延疑い」として赤色にしています。こうすることで、締切間近にも関わらず放置されているタスクに気づきやすくなりました。

私自身、定期的にシートをチェックするときに色で一発判定できるので、時間の大幅短縮につながっています。

会議室の予約管理での判定

別のシーンでは、会議室の予約一覧を作成するときにも「今日以前の予約は無効」「明日の予約は要確認」と色分けしています。これにより「本日の予約一覧だけが濃い色で表示される」ため、担当者がすぐに予定を把握できるのです。特に外部からゲストを招く会議の場合は、うっかり忘れが命取りになるので、予約状況が一目で分かるようにしておくと安心です。

設定の流れを表で整理

条件付き書式に慣れていない方には、実際にどんな手順で設定を行うのかがイメージしづらいかもしれません。そこで、最後に設定の流れを簡単に表にしてみました。今回は「セルが空白でなく、かつ今日以前の日付をハイライトする」場合を例にまとめています。

このように、操作自体はそれほど複雑ではありません。最初のうちは何度か試しに設定してみて、数式の動作を確認するといいと思います。もしうまくいかなかったら、セル内のデータが正しく日付扱いになっているかどうか、また参照の絶対/相対指定が正しいかどうかをチェックしてみてください。

まとめ：確実な日付判定の秘訣

ここまで紹介してきたように、Excelの条件付き書式を使って「空白セルを無視しつつ今日以前の日付をハイライトする」ためには、AND関数とTODAY関数、それからセルの正しいデータ形式が鍵になります。私自身も最初は何度か試行錯誤を繰り返しましたが、下記のポイントを押さえることで安定して動作するようになりました。

データ形式を正しくそろえる

セルに余計な空白や文字列が入っていないかを確認し、数値扱いの日付だけが並ぶようにすることが大切です。インポートしたデータの場合は特に注意が必要で、テキストになっている場合はセルの書式設定や関数を使って変換しておきましょう。

先頭セルの参照を正しく記述

条件付き書式で数式を作る際に、たとえばJ3を参照するのか$J$3を参照するのかで結果が変わります。今回のように列全体に同じルールを適用したい場合は、絶対参照を使わずにJ3<=TODAY()という形で相対参照にしておくのが一般的です。

AND関数を使って複数条件を組み合わせる

今回のように「今日以前の日付」かつ「空白セルではない」という組み合わせならAND関数が大活躍です。他の条件でも応用可能なので、覚えておくといろいろ応用がききます。

個人的には、条件付き書式を使い始めてからExcelへの愛着がさらに増しました。色分けって、思った以上に作業効率を上げてくれますよ。

さらに一歩進んで効率化を図るアイデア

条件付き書式だけでも十分強力ですが、マクロやVBAと組み合わせるとより高度な管理が可能です。たとえば、シートを開いた瞬間に「自動的に条件付き書式を更新」するマクロを組むと、リアルタイムに最新の日付状況を判別できます。また、Power Queryを使ってデータを取得し、データを更新するタイミングで条件付き書式を適用する設定をするのも有効です。

VBAとの連携例

VBAで Worksheet_Change イベントや Workbook_Open イベントを使うと、シートが開かれたときやセルが変更されたときに特定のスクリプトを動かすことができます。たとえば定期的に日付比較関数を走らせて、条件付き書式のルールそのものを自動生成するなどの操作が考えられます。ただし、チームで使う場合はVBAを含んだファイル形式(xlsm)になり、セキュリティ設定などに注意が必要です。

注意点：共有設定やバージョンによる不具合

共有ブック機能をオンにしている場合や、古いバージョンのExcelを使用している場合は、条件付き書式やVBAがうまく動作しないことがあります。私も過去にOfficeバージョンが混在した環境でトラブルが起きた経験がありました。こうした場合はバージョンアップを検討するか、一部の機能を使わないようにするなどの対策が必要となることもあります。

チーム全体が同じExcelバージョンを使っていないと、せっかく設定したルールが正しく引き継がれない可能性があります。

本記事のまとめと今後の活用アイデア

ここまで、Excelの条件付き書式で「空白セルを無視しつつ、今日以前の日付をハイライトする」具体的な方法や、その応用例、注意点などを詳しくご紹介してきました。条件付き書式をうまく活用すれば、期限管理やタスク管理、会議室の予約状況など、さまざまな業務の見える化が一気に進みます。色分けによって人間が視覚的に情報を捉えやすくなるので、他のメンバーとの情報共有もスムーズです。

もし「今まで条件付き書式を使ってみたけどうまくいかなかった」という方は、今回取り上げた以下の点を改めてチェックしてみるといいかもしれません。

最終チェックポイント

セルのデータ形式

空白やテキストではなく、数値としての「日付」が正しく入っているか。スペースなど見えない文字が混ざっていないか。

数式の参照

絶対参照が必要か、相対参照でよいのか。先頭セルを正しく指定しているか。

関数の組み合わせ

ANDやOR、NOTを正しく組み合わせられているか。今回はANDが中心だったが、シーンによってはORを使うこともある。

私も、Excel作業を人に見せるときは「色で強調してわかりやすく見せる」というのを意識しています。慣れてくると自由度が高くて楽しいですよ。

今後の活用アイデア

今回の設定をベースにして、今後は下記のような発展系も考えられます。

季節や年度ごとの色分け

「年度末までの日数が〇日を切ったら赤」にしたり、期ごとに色を変えたりすると、年度単位の進捗管理がしやすくなります。

複数条件の優先度設定

条件が増えてくると、どの条件を優先して適用するかをルールの管理で慎重に設定しておくと混乱を防げます。

シート全体の装飾

行ごとに「プロジェクト名」「担当者」「期限」などが混在している場合は、各列に異なる条件付き書式を与えることで一覧管理をさらに見やすくできます。

終わりに

条件付き書式は、Excelを使いこなすうえで避けては通れない重要な機能の一つです。今回の「セルが空白なら無視し、今日以前の日付だけをハイライトする」という設定は、多くの業務シーンで役立つ基本的かつ汎用的なテクニックだと思います。慣れてくると数式でさまざまなアイデアを形にできるようになるので、業務効率の向上やミスの削減につながっていくはずです。

私自身も、最初は「なんでこのセルがハイライトされないんだろう？」と悩むことが多かったのですが、ひとつひとつ原因を潰していくうちに、Excelのデータ形式や参照の仕組みを深く理解できるようになりました。最終的には、業務スピードだけでなくデータの信頼性もアップし、上司や同僚からの評価が上がったのを実感しています。

これからExcelをさらに使いこなしたいという方は、ぜひ今回ご紹介した方法や注意点を参考に、実際のシートで試してみてください。ちょっとした工夫だけで、見る人が「わかりやすい」「使いやすい」と感じるシートが作れるようになるはずです。