Highlight duplicates in excel how to#
Highlight duplicates in excel manual#

In the next section of our Excel tutorial, we’ll perform the same task, but with the COUNTIF function.

Highlight duplicates in excel how to#

How to Find Duplicate Values Using COUNTIF Function

Invoice number “OB1J264-000X” was mistakenly entered twice, so one of the entries must be removed to avoid mistakenly paying the recipient twice.

the cells formatted in red fill and red font. DateĪfter selecting our range of invoice data and clicking the shortcut to highlight duplicates, we can see there is one duplicate right away, i.e. The task here is to confirm that there are no duplicates in the following data set before payments are issued. Suppose you’re reviewing the end-of-month invoices due to be paid out in the month of November 2022. How to Highlight Duplicate Values Using Conditional Formatting To access the spreadsheet used in our modeling exercise, fill out the form below. We’ll now get started with our Excel tutorial. Highlight Duplicate Values Calculator – Excel Model Template In comparison to the prior method, setting a custom rule tends to be more practical for more complicated tasks beyond highlighting simple duplicates.įor instance, the formula can be adjusted to count cells that meet a partial criterion, such as all cells that end in a specified string of text or digits. If you want to highlight only the duplicates that appear more than two times, the “>1” can be replaced with “>2” – or if you want to highlight only values with an exact given number of duplicates, simply switch the “>” with “=”. Unlike the range, the active cell should NOT be an absolute reference, as the formula is automatically copied across all cells in the chosen range.

“ criteria” → The active cell can be any cell reference in the range.

In order for the conditional formatting to function correctly, the range must be anchored, i.e.

“ range” → The selected range of cells in which the rule will find and highlight duplicates.

The following keyboard shortcut can be used to make the process above even more efficient. Highlight Duplicate Values Keyboard Shortcut If applicable to the task at hand, once the duplicate entries are highlighted, the “Remove Duplicates” option can then be selected in the “Data” tab (and “Data Tools” group”). “Light Red Fill with Dark Red Text”) or customize the style. You can either proceed with the default formatting setting (i.e. Upon clicking on “Duplicate Values…”, the following box should appear. Near the bottom of the list, the “Duplicate Values…” selection is the one that we want to select, which should open the formatting style box in which duplicate entries will appear. Within “Conditional Formatting”, the next step is to open the “Highlight Cell Rules” group, which displays a variety of rules that could be applied. The “Conditional Formatting” selection can be found on the Home tab in Excel, within the Styles group. Highlighting Duplicate Values with Conditional Formatting

Step 3 → Select “Highlight Cells Rules”.

Step 2 → Click on “Conditional Formatting” (Styles Group).

The process of highlighting the duplicate values in a selected range of data can be broken into four steps: One side benefit of utilizing conditional formatting in Excel is that not only are duplicate values in the current range highlighted, but the conditional formatting can continuously check for duplicates as new entries roll-in, assuming the rule is set properly. For instance, a company might be wanting to identify recurring customers that have placed multiple orders in the past. On the other hand, the duplicate entries could be a normal occurrence (and thus might not need to be removed).

Highlight duplicates in excel manual#

The quickest and most convenient method of identifying and highlighting duplicates in a data set is to utilize the conditional formatting feature in Excel.Ĭonditional formatting provides the ability to highlight duplicate entries, after which the user can either remove the duplicates or keep them as-is.ĭuplicate values can often be the result of a system or error arising from manual entry, which in most cases would be subsequently removed. How to Highlight Duplicate Values in Excel (Step-by-Step)