Let’s take a look. As we’ve seen previously, Excel provides a built-in conditional format preset to highlight duplicates in a set of values. In simple situations, like this list of colors, the format is easy to use and makes all duplicate values clear. But what about cases where the list of data contains more than one column, and individual values are naturally duplicated? When we select the data, and apply the preset for duplicate values, the entire table is highlighted, since all values are repeated at least once. In a case like this, we don’t really want to check for duplicate values; we’d like to check for duplicate rows. That is, rows that contain all of the same values. One way to do this is to temporarily add another column to the end of the table that brings the other values together in one cell. Let’s call this column “Key,” as in “unique key.” Now let’s add the formula. This is a little like adding numbers together, except in this case we need to add text values together. This is called “concatenation.” We can use the ampersand character in our formula to concatenate each value in the row into one master value, or key. The result isn’t very good looking, since we didn’t include spaces, but it does give us what we need—a single value that takes into account all the values in a given row. Now that we have this value, we can apply the conditional formatting preset for duplicates. This time, only true row duplicates are highlighted. With duplicates highlighted, you can easily go through the list to review and remove any rows that aren’t needed. When you’re done with the key, simply remove the column from the table.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
