Conditional Formatting in Excel allows users to easily apply formatting, such as changing the cell background color and font color, bolding the required cell values, etc., based on specific conditions defined by them.
This tool differentiates dates, numbers, and even alphabets. Highlighting due dates, weekends, holidays, date ranges, etc., is a breeze.
Consider when you need to highlight the important dates related to your upcoming project with multiple colors. Conditional formatting can help!
You may have used different date functions, such as TODAY, DATE, DATEVALUE, WEEKDAY, etc. But now is the time to take your expertise to the next level using Conditional Formatting.
In this article, we will discuss how to use Conditional Formatting in Excel. We will see how to use the dates to create rules for highlighting the particular cell values. So, make sure to read the article till the end.
Download the Example Excel Sheet
You can download the following Excel spreadsheet that contains the sheets and datasets we used to demonstrate Conditional Formatting in Microsoft Excel.
Practice is the key to success. Make sure you download and follow me along as we move through this article. If you have your own dataset ready to practice the things we discuss in the following sections, skip downloading the above file.
Explained: Excel Conditional Formatting Feature
Conditional Formatting allows users to define rules to highlight the required dates. Depending on their specific requirements, they may choose to change the font type, color, and cell background.
Here’s how to access and use the Conditional Formatting feature,
- Open the desired Excel spreadsheet
- Select the range of cells that you want to format
- Hover to the main menu and click on the “Home” tab
- Click on the “Conditional Formatting” option from the list
- Select a rule type from the available options that suits your needs
- For dates, you will have to use the “New Rule” option
- This will open a “New Formatting Rule” dialog box in which you will have to select “Use a formula to determine which cells to format”, as shown below
- Enter the formula that defines your condition in the space as shown below
- Choose the formatting style you want to apply when the condition is met by clicking on the “Format” option
- You can change the text style to Bold and Italic, underline the text, change the text color, apply Strikethrough to the text, change the cell background color in the newly opened “Format Cells” dialog box
- Click on “OK” to apply the rule
Quick and easy! This is how you can use the Conditional Formatting feature in Excel. In the next section, let’s see how to highlight single and multiple dates in Excel using Conditional Formatting.
How to Use Conditional Formatting in Excel
Here are the cases or examples discussed in the following sections. Click on the respective links to jump to that particular section of the article,
- Highlighting a single date
- Highlighting the date range before and after the given date
- Highlighting dates within the given date range, e.g., 7 (week) or 30 (month)
CASE #1 – Highlight Today’s Date
Let us consider a simple scenario in which we wish to highlight a single date from the Hire Dates column. We assume that many people are hired during a given period of time. Our task is to underline everyone hired today.
Here’s how the data table looks,
It includes employee details such as ID, full name, and hire date.
Let’s begin,
- Open the desired Excel spreadsheet
- Hover to the date column and select all the cells with the dates
- Go to the main menu and click on the “Home” tab
- Choose the “Conditional Formatting” option from the list
- Now, click on the “New Rule” option from the popup
- A new dialog box will appear on your screen; you need to choose the “Use Formula to determine which cells to format” option, as shown in the following image,
- Next, in the Formula section, insert the formula “=$C2=TODAY()” as shown in the following image
- Click on the “Format” button and choose the formatting color as desired in the “Fill” tab
- Now click on “OK” to apply the formatting
The formula we used in the above example is as follows,
=$C2=TODAY()You can also highlight a specific date from the “Hire Dates” column by changing the formula as shown below.
CASE #2 – Highlight Multiple Dates before a given Date
We will learn to highlight dates that fall before the given date.
We need to highlight multiple dates, and the steps are almost similar to the previous example except for the formula we will use.
Let’s begin,
- Open the desired sheet
- Select the cells containing the dates
- Hover over to the “Home” tab and click on the “Conditional Formatting” option from the list
- Click on “New Rule”
- Choose “Use Formula to determine which cells to format”
- In the Formula section, insert the formula “=$B2<=”1/15/2023”” as shown below
- Click on the “Format” button as shown in the following image,
- Select the color of your choice from the “Fill” tab
- Click on “OK” to apply the formatting
Here’s how the formula used in the above example looks,
=$B2<=”1/15/2023You can also use these steps to highlight the dates that fall after the date 1/15/2023 by just making a minor change in the formula as follows,
=$B2>=”1/15/2023”Case #3 – Highlight dates between the given Date Range (30 days – within a week or month)
Consider the following example data, representing the employees and their hiring dates from January to February 2023.
Our task is to highlight the cells between the specified dates: 14 January 2023 and 29 February 2023.
Note that we have entered the date range in cells D3 and D4. D3 contains the initial date, and D4 contains the end date.
Here are the steps,
- Open the desired Excel spreadsheet
- Select the cells containing the dates
- Click on the “Home” tab and select the “Conditional Formatting” option
- Click on “New Rule”
- Choose “Use Formula to determine which cells to format”
- In the Formula section, insert the formula, “=AND($B2>D$3,$B2<D$4)” as shown below
- Click on “Format” and choose the formatting color you prefer in the “Fill” tab
- Press the “OK” button at the end
The formula used in the above example is as follows,
=AND($B2>D$3,$B2<D$4)The above steps can be used to highlight the dates within any given date range. When entering the dates to create the rule, you must follow the same date formatting as seen in the dates column of the table.
Conclusion
Conditional Formatting in Excel is useful for highlighting important information, identifying trends, and making data analysis more easier. We have seen various formulas that create a dynamic table that automatically highlights dates within specific time frames with respect to changes that take place in the future.
If you are still stuck somewhere or need help using the Conditional Formatting in Excel, please comment below. I will try my best to answer your questions as soon as possible.
Also, make sure to explore our blog section for more tips and tricks on how to use Microsoft Excel and Google Sheets like a pro.