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.

________________

0 thoughts on “Hide Duplicate Headings in Excel Report”

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

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.