A few weeks ago we looked at a way to fill blank cells in an Excel report, so you’d be able to filter or sort a table of data.
The downside of that technique is that it’s harder to pick out the sections in a report. The headings don’t pop off the page — they’re buried in a long list of items.
Why Not Both?
You can have the best of both worlds, if you fill in all the blanks, then hide the duplicate items with conditional formatting.
This technique , described below, works best when the column is sorted, so duplicate headings are listed together.
Hide Duplicate Headings
In this table, the January items are listed, then February, and each row has the month name filled in.
If the list is long, it can be hard to see exactly where each month’s items start.
To hide the headings, you can add conditional formatting to the cells with month names in column D.
Hide Duplicates in Excel 2007
- Select all the cells with month names, cells D2:D13 in this example.
- On the Ribbon, click the Home tab, then click Conditional Formatting
- Click Use a Formula to Determine Which Cells to Format
- In the Edit the Rule section, enter a formula that refers to the active cell, and the cell above it. In this example, D2 is the active cell:
=D2=D1 - Click the Format button, and click the Font tab.
- From the Color drop down, select a font colour to match the cell fill colour. In this example, the cell has no fill, so I’ll select White as the font colour.
- Click OK to close the Format Cells dialog box, then click OK to close the New Formatting Rule dialog box.
Hide Duplicates in Excel 2003
- Select all the cells with month names, cells D2:D13 in this example.
- On the menu bar, click Format, then click Conditional Formatting
- In the Conditional Formatting dialog box, rom the first drop down, select Formula Is
- In the Formula box, enter a formula that refers to the active cell, and the cell above it. In this example, D2 is the active cell:
=D2=D1 - Click the Format button, and click the Font tab.
- From the Color drop down, select a font colour to match the cell fill colour. In this example, the cell has no fill, so I’ll select White as the font colour.
- Click OK to close the Format Cells dialog box, then click OK to close the New Formatting Rule dialog box.
The Duplicates Are Hidden
After you apply the conditional formatting:
- the first instance of each heading is visible.
- Any duplicate headings immediately below it are hidden.
When cell D4 is selected, January appears in the formula bar, but it’s not visible in the cell, because the cell font is white
Watch the Video
To see the steps for hiding duplicate headings, watch this short video tutorial.
Another way to show that the data is still there is by selecting a number of cells. The “hidden” ones show as white text on a light grey.
This looks like an easy way to make the data look more “user friendly” while still keeping the data there for pivot tables, macros, etc.
Excellent! I love tips like these: easy to follow, and very useful. Thanks.
Thanks! Glad you like them, Toad. And this easy tip can make a workbook look better, as JP said.
Jan Karel, thanks for mentioning another way to find the hidden content.
Another method I use is the COUNTIF in the conditional formatting and make the format of the text White
=COUNTIF(B2:$B$8,B2)>1 (Note the absolute and realtive cells) So as long as there is more than one occurance from the currnet cell to the last cell it will be White.
This will change the format of all duplicate cells except the last.
However, if you want to keep the first occurance then use the following
=COUNTIF($B$2:B2,B2)>1
Great tip! I’m exporting data to Excel and was jumping thru all kinds of hoops to hide duplicates till I found this!
Thanks Gina, glad it helped you!