Nehad Ulfat
In Microsoft Excel, when you have to look for duplicates or matching values across multiple worksheets, you’ll find lots of suitable formulas to meet the objective. After finding the matches or duplicates, you can also highlight the corresponding cells with particular colors, or with different text fonts. In this article, you’ll find those methods to highlight duplicates across multiple sheets with proper examples and illustrations.
Download Practice Workbook
You can download the Excel workbook that we’ve used to prepare this article.
3 Suitable Approaches to Highlight Duplicates across Multiple Worksheets in Excel
To highlight duplicates or matches across multiple worksheets, we have to go for the Conditional Formatting option. After setting up a new rule formula to find the duplicates over multiple worksheets, we have to select the cell format with colors or text designs. Thus the corresponding cells with the duplicate values in the selected worksheet will be highlighted with the defined formats.
1. Use COUNTIF Function to Highlight Matches across Excel Worksheets
The following picture represents a worksheet named Sheet1. It contains two columns showing some order IDs on the left and the right one shows the IDs that are in transit.
In the second worksheet named Sheet2, the other two columns are lying with a list of the order IDs that have been delivered already on the left and the corresponding delivery dates on the right.
Now we’ll look for all the duplicates of order IDs across Sheet1 and Sheet2. The matched order IDs in Sheet1 will be highlighted with a specified color then. So, let’s go through the following procedures now to meet our objectives.
📌 Step 1:
➤ From Sheet1, select the range of cells where the duplicate values will be highlighted.
➤ Under the Home ribbon, select the New Rule command from the Conditional Formatting drop-down.
A dialogue box named ‘New Formatting Rule’ will appear.
📌 Step 2:
➤ From the Rule Type options, select ‘Use a formula to determine within cells to format’.
➤ In the formula box, type:
=COUNTIF(Sheet2!$B$5:$B$14, Sheet1!B5)➤ Press Format.
📌 Step 3:
➤ In the Format Cells window, select a color for highlighting the duplicates.
➤ Press OK.
📌 Step 4:
➤ You’ll find the preview of the formatted cell with text in the New Formatting Rule dialog box.
➤ Press OK.
Finally in Sheet1, you’ll see the highlighted cells with the order IDs that are also present in Sheet2.
We have used the COUNTIF function here to define the criteria for highlighting the cells in Sheet1. The COUNTIF function looks for each order ID of Sheet1 in Sheet2 and returns the occurrence of each duplicate for the corresponding order ID. When we input this formula in the Rule Description box of the New Formatting Rule window, the application of the Conditional Formatting will look for the cells of the specified range in Sheet1 where the formula returns non-zero values only and thereby highlight the corresponding cells only.
Highlight Multiple Duplicates across Two Worksheets
Now let’s assume, we have multiple duplicates for an order ID in Sheet2. In Sheet1, the corresponding order ID will be highlighted with another color or cell format.
📌 Step 1:
➤ In Sheet1, select the range of cells for the order IDs again.
➤ Under the Home ribbon, choose the Manage Rules command from the Conditional Formatting drop-down.
A dialog box named Conditional Formatting Rules Manager will appear.
📌 Step 2:
➤ Click on the option ‘Duplicate Rule’. This will create a duplicate of your previously defined rule.
➤ Now select Edit Rule and the Edit Formatting Rule window will show up.
📌 Step 3:
➤ In the formula box of the Rule Description, enable editing and add “>1” only at the end of the formula.
➤ Click on the Format option.
📌 Step 4:
➤ Select a new and different color for the second formatting rule.
➤ Press OK.
📌 Step 5:
➤ You’ll be shown a preview of the second formatting rule. Click OK again.
📌 Step 6:
➤ In the Conditional Formatting Rules Manager dialog box, the second rule is embedded now.
➤ Press OK for the last time and you’re done.
Like in the following screenshot, you’ll find Cell B13 highlighted with another color since this cell contains an order ID that is present multiple times in Sheet2.
In the second rule of conditional formatting, we’ve inserted a condition that looks for the count of more than 1. Thus the application highlights the corresponding cells with another defined color.
Read more: How to Highlight Duplicates in Excel
2. Insert ISNUMBER Function to Find Duplicates across Multiple Worksheets in Excel
We can also combine the ISNUMBER and MATCH functions to find the duplicates or matches across two Excel worksheets. The MATCH function returns the relative position of an item in an array that matches a specified value in a specified order. And the ISNUMBER function checks whether a value is a number or not.
So, the required formula in the Rule Description box here will be:
=ISNUMBER(MATCH(B5, Sheet2!$B$5:$B$14,0))
We’ll get the following result as found in the previous method.
🔎 How Does This Formula Work in Conditional Formatting?
- The MATCH function looks for the matches of the order IDs from two worksheets and returns the row number of the corresponding order ID in Sheet1. If the function does not find a match, it returns an error value.
- The ISNUMBER function looks for the numeric values only and ignores the error values found by the MATCH function. Thus the function returns TRUE for the numeric data and FALSE for the error values.
- Finally, the Conditional Formatting highlights the matches based on the boolean value ‘TRUE’ only.
3. Apply VLOOKUP Function to Highlight Duplicate Rows across Multiple Worksheets
Now we’ll insert the VLOOKUP function in the New Formatting Rule. The VLOOKUP function looks for a value in the leftmost column of a table and then returns a value in the same row from the specified column.
The required formula with the VLOOKUP function in the Rule Box will be:
=VLOOKUP(B5,Sheet2!B5:C14,,FALSE)
And the following picture shows the highlighted cells where the application of the VLOOKUP function has returned valid outputs.
Concluding Words
I hope all of these simple methods mentioned above will now help you to apply them in your Excel spreadsheets when needed. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.
