Separate Excel List with Automatic Lines

When you create a table in Excel, you can use the built-in styles, to add bands of colour.

The colour bands can make the table easier to read, because you can follow each line across, using the colour as a visual guide.

Separate the Days in the List

If you’re working with a list of tasks or orders, sorted by date, those coloured bands don’t help you see where each day’s data starts and ends.

To make it easy to separate the days, you can add conditional formatting to the table. We’ll add a line at the start of each date, to highlight those rows.

• Select all the cells in the body of the Excel table. In this example, cell A2 is the active cell, and A2:F9 are selected.
• On the Ribbon’s Home tab, click Conditional Formatting, then click New Rule

• In the New Formatting Rule dialog box, click Use a Formula
• In the formula box, enter this formula, which compares the values in cells A1 and A2. There is a $ before each A, because we need an absolute reference to column A.
  • =$A2 <> $A1

• Click the Format button
• In the Format dialog box, click the Border tab
• Select a border pattern and colour, to separate the dates. Unfortunately, you can’t select a thickness for the border line.
• Click the top border in the preview window, and click OK

• Click OK to close the New Formatting Rule dialog box.

There will be a border at the start of each date in the table, and you’ll see at a glance where the dates start and stop.

More Conditional Formatting Examples

You’ll find many more conditional formatting examples and tutorials on the Contextures website.

Watch the Excel Video Tutorial

To see the steps for adding lines between the dates, you can watch this short Excel video tutorial.

___________