Hide Duplicate Headings in Excel Report

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, but are buried in a long list of items.

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 works best when the column is sorted, so duplicate headings are listed together.

Hide the 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.

CondFormatHideDup01

To hide the headings, you can add conditional formatting to the cells with month names in column D.

Hide Duplicates in Excel 2007

  1. Select all the cells with month names, cells D2:D13 in this example.
  2. On the Ribbon, click the Home tab, then click Conditional Formatting
    Conditional Format New Rule
  3. Click Use a Formula to Determine Which Cells to Format
  4. 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
  5. Click the Format button, and click the Font tab.
  6. 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.
  7. Click OK to close the Format Cells dialog box, then click OK to close the New Formatting Rule dialog box.

New Formatting Rule dialog box

Hide Duplicates in Excel 2003

  1. Select all the cells with month names, cells D2:D13 in this example.
  2. On the menu bar, click Format, then click Conditional Formatting
  3. From the first drop down, select Formula Is
  4. 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
  5. Click the Format button, and click the Font tab.
  6. 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.
  7. Click OK to close the Format Cells dialog box, then click OK to close the New Formatting Rule dialog box.

Conditional Format Formula

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.

Conditional Format Hidden

When cell D4 is selected, you can see January in the formula bar, but it’s not visible in the cell, because the font is white.

Watch the Video

To see the steps in Excel 2007, you can watch the following short video tutorial.

________________

You may also like...

7 Responses

  1. 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.

  2. JP says:

    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.

  3. Toad says:

    Excellent! I love tips like these: easy to follow, and very useful. Thanks.

  4. 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.

  5. Kanti Chiba says:

    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

  6. Gina Whipp says:

    Great tip! I’m exporting data to Excel and was jumping thru all kinds of hoops to hide duplicates till I found this!

Leave a Reply to Debra Dalgleish Cancel reply

Your email address will not be published. Required fields are marked *