Hide Duplicate Headings in Excel Report

Hide Duplicate Headings in Excel

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.

list with duplicate month headings
list with duplicate month headings

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
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. In the Conditional Formatting dialog box, rom 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 Formatting dialog box with formula rule
Conditional Formatting dialog box with formula rule

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

first instance of each heading is visible
first instance of each heading is visible

Watch the Video

To see the steps for hiding duplicate headings, watch this short video tutorial.

0 thoughts on “Hide Duplicate Headings in Excel Report”

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

  2. 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 to Kanti Chiba Cancel reply

Your email address will not be published.

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