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.

________________