Software

A quick trick for hiding duplicate Excel values

Duplicate values aren't wrong, but you might not want to display them. Use this simple technique to hide duplicate values in Excel.

Duplicate values aren't wrong or bad, but they can be distracting. You probably won't want to delete them, but you might just want to hide them. For example, the simple sheet below repeats date values in column A. In a reporting scenario, those duplicate dates might be annoying.

The easiest way to hide them is to apply a conditional format that changes the font color to the same as the cell's background. The data's still there, but you won't see it. This technique is easy to implement, as promised, but it comes with inherent risks because users forget what they can't see. A sheet with hidden details can be difficult to maintain and troubleshoot. Keep that in mind when you decide whether this technique is right for you.

A quick detour

You might be tempted to use one of Excel's built-in conditional format rules (not available in 2003), but it won't work as expected. It'll hide each instance of a duplicate value. It won't leave the first occurring instance and hide the rest - and that's what we're after. Now, let's take a quick look at what happens when you apply the duplicates rule:

  1. Select A2:A13.
  2. Click the Conditional Formatting option in the Styles group.
  3. Choose Highlight Cells Rule.
  4. Select Duplicate Values.
  5. From the Format dropdown choose Custom Format.
  6. In the resulting dialog, click the Font tab if necessary. Then, choose white (or the color that matches the cell background).
  7. Click OK twice.

The format doesn't produce the results you want - it hides every instance of each duplicate value.

A hiding conditional format

Now let's look at a conditional format that works as expected. First, sort the recordset by the column you're going to format. We'll be using a formula rule that compares two adjacent values - so the values must be sorted for this technique to work. The built-in rule we just reviewed doesn't require a sorted list.

After sorting, hide the duplicate values, as follows:

  1. Select A2:A13.
  2. Click the Conditional Formatting option in the Styles group, and choose New Group. In Excel 2003, choose Conditional Formatting from the Format menu.
  3. Choose Use A Formula To Determine Which Cells To Format from the Select A Rule Type list.
  4. In the lower pane, enter the following formula: =A1=A2
  5. Click Format.
  6. In the resulting dialog, click the Font tab if necessary.
  7. Choose white from the Color palette (or the color that matches the cell background).
  8. Click OK twice.

You probably noticed that the formula refers to a header cell (A1) and not the first cell in the selected range (the data range). This offset tricks the format into formatting subsequent duplicates. If you refer to the first and second cells in the selected range (=A2=A3), the rule would hide all but the last duplicate value.

Caution

To reiterate, this technique might create difficulties down the road. There's nothing wrong with the technique, but the hidden data can be easy to forget. In this particular case, the risk seems inconsequential; it won't always be so. For more on unforeseen formatting headaches, read 10 Excel formatting decisions that can have unfortunate repercussions.

Sample Excel worksheets of this technique are available.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

12 comments
DBlayney
DBlayney

I have used the same basic approach (font colour=background colour) before and find it much simpler that resorting to pivot tables. A suggestion to those who (rightly) point out that the cells look empty: Instead of effectiely blanking the duplciate cells, just choose a lighter font colour (light grey instead of black, for example). This gives the required prominence to rows where the date has changed without hiding the data completely.

daniel
daniel

As an Excel trainer I get that same question sometimes. Conditional Formatting is certainly cool. Keep in mind this other technique, it may be handy in certain occasion. It's very quick too! Using data laid out the same way I created a Pivot Table in the same sheet and quickly dragged the three fields in the Row Labels section. In the Options tab disable Field Headers and in the Design tab I disable the Grand Totals. Finally in the Report Layout choose Show in Tabular Form. The only thing that is a bit of trouble is that the column headings aren't there so copy and and paste the column headings above the P.T. This would look nice in a PowerPoint Slide. Thanks for the Conditional Formatting idea now I got two ways. Daniel

ferchr
ferchr

I use a combination of Pivot and add a filter field, so excel "data" tab identifies detail data elements to filter out. The filter column is a simple "if" statement like "=if(trim(a1)=trim(a2),"duplicate","ok"). This way I don't have to worry about the cell formating or in A1 or A2 as leading or trailing spaces are removed. The new column is a "filter" at the report level in a pivot table.

brailm
brailm

Great post! I frequently format my data as tables with alternating colors for the rows. Instead of changing font color I used a custom number format of ";;;" which displays nothing. My color scheme remains unaffected.

harris
harris

Pivot tables are a great way to display and manipulate data to make it more user friendly. They are quick and easy to make, and can be formatted easily. I prefer to use the "Classic Pivot Table layout" under the display options because the results look like what you are trying to achieve with this method.

bruno.hecquard
bruno.hecquard

Good Excel practice implies to keep the data as simple as possible and to present summaries elsewhere. Formatting cells with blank characters, even using conditional formatting, is bouring and may lead to errors as written in the article. I usually prefers to convert the data list as an Excel table (Insert menu) and then summarize with a Pivot table. A pivot table will automatically classify by dates providing the date field be used as the first row field for the pivot. For all Reports, data presentation, summary, analysis, same advice: don't touch the source Data tabsheet, do the job in another tab, use Pivot as much as possible both for summary layout and for calculations. Quicker, simpler and much more robust.

jbenton
jbenton

looks even better if the next condition is that if they're different put a top border on the cell and if you want to quickly show the hidden data, just select a range including it

bturner23
bturner23

Data > Filter > "Unique records only"

jbenton
jbenton

trouble is that you have to apply this manually to duplicate cells, not have Excel format the display automatically

ssharkins
ssharkins

I agree that leaving the raw data alone is always the best way to proceed, when you can. Thanks for mentioning that.

jbenton
jbenton

but without the repeated information in the 1st column

ssharkins
ssharkins

Filters might not render the data in the order/groups that you need. Hiding is just a simple technique for the simplest/quickest of needs. It is definitely my least favorite though -- for all the reasons mentioned.

Editor's Picks