Suppressing Duplicate Values in Microsoft Excel

22. July 2016 how to 0
Suppressing Duplicate Values in Microsoft Excel

 

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.

screenshot showing selected column for suppressing duplicates

From the Home tab on the ribbon, click the Conditional Formatting button and select New Rule…

Conditional Formatting Window in Excel

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.

SuppressDuplicates-ConditionalFormattingRule

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.

screenshot showing data with duplicates suppressed


Leave a Reply