And if you’re using this technique, remember the painful lesson that I learned – don’t copy and paste within a table that has conditional formatting. You could end up with an Excel 2010 Conditional Formatting Nightmare. And nobody wants that!

Lines Disappear in Filtered Lists

Recently, I heard from Lon, who liked the tip about red line borders. But Lon noticed a problem – the line didn’t always show if the list was filtered. For example, if we filter the Product column, to hide Paper, the red borders for some of the dates disappear.

I hadn’t noticed the problem, because my list is usually filtered by date, to show only the latest month’s data. Since the conditional formatting is based on the date column, it would continue to work correctly.

But we can change the conditional formatting, so it works in a filtered list.

Change the Formula for Filtered Lists

To make the conditional formatting work in a filtered list, we can’t use the original formula, which was

=$A1<>$A2

That formula just compares each date to the date above it, and doesn’t care if the rows are hidden or visible.

Instead, we’ll use a formula that was created by Laurent Longre. It lets you work with visible rows after a filter. For information on this formulas, read the Power Formula Technique section, in this article at John Walkenbach’s web site: Excel Experts E-letter

Here is the much longer formula that we can use, to compare dates in the visible rows only.

Download the Sample File

And you can download the sample file used in this blog from the Contextures Sample Excel Files page. In the Conditional Formatting section, look for CF0004 –Conditional Formatting in Filtered List. The zipped file is in Excel 2007/2010 format, and contains no macros.