Hiding Duplicate Values

There are times when you have duplicate data that you want to retain, but for the sake of improved readability, you wish to hide it. One way to do this is to use the built-in Hide function in Excel, but it may not always be the best choice.

Another way to hide your data is to use Conditional Formatting. You can do this to hide (Camouflage, actually) the duplicate values, and make your table a lot easier to read. The following Before and After examples illustrate this:

Before

After

In this example, when the table is sorted by State, Conditional Formatting has been used to give the duplicate occurrences of each State name a White Font color.

Here is How You Do This:

In Excel 2007 and 2010:
1. Sort your data
2. Select range B3:B12
3. On the Ribbon, go to the Home tab and click Conditional Formatting
4. Choose New Rule
5. Click Use a Formula to Determine Which Cells to Format
6. For the formula, enter: =B3=B2
7. Click the Format Button
8. Select White as the font color (same color as the cell)
9. Click OK twice

In Older Versions of Excel:
1. Sort your data
2. Select range B3:B12
3. Choose Format/Conditional Formatting
4. From the dropdown, choose Formula Is
5. For the formula, enter: =B3=B2
6. Click the Format button
7. Select White as the font color (same color as the cell)
8. Click OK twice

That’s it! Your table is much cleaner and easier to read, and you didn’t even break a sweat setting it up. How cool is that!