I needed to prepare an excel file with over 10,000 records for a user and wanted to suppress duplicate values in Excel to make it easier to evaluate the data. Suppressing duplicates provides clarity to the data when many different values exist in a column. I first ran across the ability to suppress duplicates in a tool called Hyperion MyReports–I loved this feature and really missed the ability to do it within Excel.
Below is an example of how to suppress duplicates but still keep the data in tact. In column A, State, we want to hide the duplicate values, but show the first value in the order.
Select the column that contains values that need suppressing.
From the Home tab on the ribbon, click the Conditional Formatting button and select New Rule…
Depending on your operating system (Mac or Windows), your view may be different, but the process is still the same. On the Mac, from the Style box, select Classic. Below that, select Use a formula to determine which cells to format. In the formula bar, enter =A1=A2. This formula tells excel to look at the value in A1 and see if it is the same as the value in A2 and then run down the column of selected values to find matches. If it finds a match, Excel will format it using your options, which will be set below.
In the Format With drop down menu, select custom format…. A dialog box will open allowing you to change the fill to no color, and set the font color to white. Once done selecting the colors, click OK on all of the open dialog boxes, and be amazed.
The values in column A are still present (click a cell and see the value in the formula bar), but the conditional formatting rule changes the font color to white, suppressing the duplicate values.