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.

ConditionalFormatSections

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.

  1. 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.
  2. On the Ribbon’s Home tab, click Conditional Formatting, then click New Rule
    • ConditionalFormatSections02
  3. In the New Formatting Rule dialog box, click Use a Formula
  4. 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
    • ConditionalFormatSections05
  5. Click the Format button
  6. In the Format dialog box, click the Border tab
  7. Select a border pattern and colour, to separate the dates. Unfortunately, you can’t select a thickness for the border line.
  8. Click the top border in the preview window, and click OK
    • ConditionalFormatSections03
  9. 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.

ConditionalFormatSections04

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.

Or watch on YouTube: Separate Dates in Excel List With Lines

___________

You may also like...

4 Responses

  1. Maxime Manuel says:

    Great tips!
    Thank you!

  2. Rudy says:

    Thank you very much for your tips! :*

  1. May 24, 2012

    […] have a specific value, or that meet other conditions. Recently, we used conditional formatting to show where a new date starts, in a list of […]

  2. July 19, 2012

    […]    Excel Books for Beginner to Intermediate » Line Between Dates in Filtered List A while ago, I showed you how to add a red border at the top of a row, when a new date starts in a list. You can read those instructions here: Separate Excel List with Automatic Lines. […]

Leave a Reply to Maxime Manuel Cancel reply

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