Draw a line between groups in sorted data — a top border (or shading) on the first row of each new category. A rule comparing each row to the one above marks the boundaries.
The example
First row of each region group is marked.
| A | B | |
|---|---|---|
| 1 | Region | Rep |
| 2 | East | Ann |
| 3 | West (new) | Cy |
The formula
Compare each row to the one above:
How it works
A change in the key marks a boundary:
- Make sure the data is sorted by the grouping column.
- Add a rule
=$A2 <> $A1— TRUE when the current group differs from the previous row. - In the format, add a top border (or a fill) to draw the separator at each group start.
- Lock the column with
$Aand select all columns so the border spans the whole row.
Separators, not bands. This marks the boundary between groups with a line; to shade alternating groups in different colors, use the group-counter approach in the shade-alternating-groups recipe.
Try it: interactive demo
Sorted groups; new groups get a top line.
Variations
Shade the new row
Fill instead of border:
Alternating group colors
Use a group counter — see shade-alternating-groups.
Subtotal boundaries
Mark where to insert subtotals.
Pitfalls & errors
Sort first. Boundaries only make sense on data grouped together; unsorted data marks every change.
First data row. Row 2 compares to row 1 (the header) — it’ll always mark the first group, which is usually fine.
Border in the format. The top-border look is set in the rule’s Format, not the formula.
Practice workbook
Frequently asked questions
How do I draw a line between groups in Excel?
What's the difference from banded groups?
Why is every row marked?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class