How to highlight the duplicates and overlapping dates in Excel?

In the article, the users will understand how to highlight duplicates and overlapping dates in Microsoft Excel. There are several features in the Excel sheet including conditional formatting, and format cells that the users have to fill any type of color according to their needs. The users can use the formula for blank cells in the new formatting rule dialog box. The users can use select the range in which they want to fill the color in any cell.

To highlight the duplicates and overlapping dates in Excel

Step 1

Deliberate the Excel sheet with the data. First, open the Excel sheet and create the data one by one. In this sheet, type any type of the data like the project, Start Date, and End Date fields. The users have to enter the dates that are start date and end date which the users need to highlight the cells in the given list as shown below.

Step 2

In the ribbon, there are many tabs included in the top corner. Place the cursor in the Home tab and click on the tab that has many options included. On the Home tab, place the cursor on the Conditional Formatting tab which has included in the Styles group. On this tab, click on the Conditional Formatting tab that will be visible in the drop-down menu. Click on the menu and select the New Rule tab that will open the dialog box. In the Excel sheet, the created data is displayed. Place the cursor in cell I2 and select all the date cells in which the users inserted the dates one by one.

Step 3

In the ribbon, there are many tabs included in the top corner. Place the cursor in the Home tab and click on the tab that has many options included. On the Home tab, place the cursor on the Conditional Formatting tab which has included in the Styles group. Click on the menu and select the New Rule tab that will open the dialog box. In the dialog box, there are the rules included one by one. Select and click on the rule Use a formula to determine which cells to format that enables the drop-box like this.

Step 4

In the dialog box, there is the input type and place the cursor on it. Now, enter the formula =SUMPRODUCT(($I2<=$J$2:$J$7)*($J2>=$I$2:$I$7))>1 to highlight the overlapping and duplicates. In the dialog box, place the cursor and click on the Format button that opens a new dialog box Format Cells which has the Fill tab. In the dialog box of Format Cells, there are tabs included. Now, click on the Fill tab that displays the color theme like this.

Step 5

In the dialog box, choose any color in the color theme as shown in which the users want to highlight overlapping dates then click on the ok button that closes the dialog box of Format Cells. After closing the Format Cells dialog box, the New Formatting Rule dialog box will display as shown below.

Step 6

In the dialog box, click on the ok button that will close the New Formatting dialog box. After closing the dialog boxes, it will highlight the overlapping dates with the selected color from the color theme randomly as shown below.

Step 7

In this sheet, type any type of the data like project, Start Date, and End Date fields. The users have to enter the dates that are start date and end date which the users need to highlight the duplicate cells in the given list as shown below.

Step 8

In the ribbon, there are many tabs included in the top corner. Place the cursor in the Home tab and click on the tab that has many options included. On the Home tab, place the cursor on the Conditional Formatting tab which has included in the Styles group. Click on the menu and select the New Rule tab that will open the dialog box. In the Excel sheet, the created data is displayed. Place the cursor in cell I2 and select all the date cells in which the users inserted the dates one by one.

Step 9

In the ribbon, there are many tabs included in the top corner. Place the cursor in the Home tab and click on the tab that has many options included. On Home tab, place the cursor on the Conditional Formatting tab which has included in the Styles group. Select the New Rule tab that will open the dialog box. In the dialog box, there are the rules included one by one. Select and click on the rule Use a formula to determine which cells to format that enables the drop-box like this.

Step 10

In the dialog box, there is the input type and place the cursor on it. Now, enter the formula =SUMPRODUCT(($B2<=$C$2:$C$7)*($C2>=$B$2:$B$7)*($A2=$A$2:$A$7))>1 to highlight the duplicates. In the dialog box, place the cursor and click on the Format button that opens a new dialog box Format Cells which has the Fill tab. In the dialog box of Format Cells, there are tabs included. Now, click on the Fill tab that displays the color theme like this.

Step 11

In the dialog box, choose any color in the color theme as shown in which the users want to highlight overlapping dates then click on the ok button that closes the dialog box of Format Cells. After closing the Format Cells dialog box, the New Formatting Rule dialog box will display as shown below.

Step 12

In the dialog box, click on the ok button that will close the New Formatting dialog box. After closing the dialog boxes, it will highlight the duplicate dates with the selected color from the color theme randomly as shown below.

Conclusion

The users utilized an easy instance to display how can highlight the dates which are duplicates and overlapping with a different color randomly. The users used the necessary tabs which are included in the ribbon. The users have to practice the essential options from the ribbon and modify the data according to their needs.