Hide Duplicate / Repeating Values in a Column in MS Excel.

How to hide Repeating /  Duplicate Values In A Column in Microsoft Excel with Example?


As you are working with lists in MS Excel, after sorting, you may find that, you have multiple rows with the same values repeating down one or more columns. If you are printing this list as a report, for aesthetic (pleasing) purposes, you may prefer to have only the first instance of each item shown.

One option is to clear the cells for all duplicate items below the first one. The problem with this approach:

1) approach can be very time-consuming if you're working with a long list and

2) once you delete values from the cells, you won't be able to sort or filter your list.

Here is a clean and easy trick using Conditional Formatting that you need only do one time. After that all future duplicate values will be hidden.

So, How to hide repeating values in a Column in Microsoft Excel (While Printing) with example?
1) Select a range of cells in the column where you want to hide duplicates;

2) In this example we have selected B2:B28, you will notice that cell B2 is the Active Cell;

3) On the Home tab, click Conditional Formatting in the Styles group and then choose New Rule...;

4) Select 'Use a formula to determine which cells to format';

5) In the formula field type =B2=B1

6) Click the Format... button, click the Font tab, select the Color dropdown, choose white* from the color palette and click OK.

Note:- * (Aestrik) This assumes the background of your cells is white so the duplicate text will be invisible.

7) Click OK to close the Conditional Formatting dialog.

You should now see that the only first value is visible but all the duplicates below are hidden. However they are still there (just look in the formula bar) but they blend into the white background.

Hide duplicates in Excel 2007 using this Conditional Formatting trick

Use Conditional Formatting and white font to hide duplicates in Excel 2007.