Question: I have a list that I keep adding rows to. How do i create a border that expands as the list expands?

Answer:

The easiest way to go is an excel defined table, you can customize how it looks easily:

Recommended articles

How to use Excel Tables
An Excel table allows you to easily sort, filter and sum values in a data set where values are related.

Adding more rows to your list expands the border automatically. See picture below.

How to create dynamic border

The border on top of the list is static. Here is how to create the top border:

1. Select the top cells of your list
2. Go to "Home" tab in excel 2007
3. Press with left mouse button on the small triangle on the border button in the font window. See picture below.
   
   
4. Press with left mouse button on "Top border"

The border on the sides of the list is dynamic. Here is how to create the border on the left side:

1. Select the leftmost column in your list (In this example C:C)
2. Press with left mouse button on "Home" tab on the ribbon
3. Press with left mouse button on "Conditional formatting"
4. Press with left mouse button on "New rule..."
5. Press with left mouse button on "Use a formula to determine which cells to format"
6. Press with left mouse button on "Format values where this formula is true" window.
7. Type =OR(C1<>"",D1<>"",E1<>"",F1<>"")
8. Press with left mouse button on Format button
9. Press with left mouse button on "Border tab" tab
10. Create a border on left side of cell
11. Press with left mouse button on OK!
12. Press with left mouse button on OK!

Here is how to create the left and bottom border on the left side:

1. Select the leftmost column in your list (In this example C:C)
2. Create a new conditional formatting formula. (See above list)
3. Type =AND(OR($C1<>"",$D1<>"",$E1<>"",$F1<>""),$C2="",$D2="",$E2="",$F2="")
4. Create a border on the left and down side of cell

Do the same thing for the right side (F:F) of the list using the two above examples. Obviously creating borders on the right and down side of cells.

Finally creating the border lines below the list:

1. Select the middle columns in your list (In this example D:D and E:E)
2. Create a new conditional formatting formula.
3. Type =AND(OR($C1<>"",$D1<>"",$E1<>"",$F1<>""),$C2="",$D2="",$E2="",$F2="")
4. Create a border on the down side of cell

Get excel sample file for this tutorial.

create-a-dynamic-border-using-excel-conditional-formatting.xls
(Excel 97-2003 Workbook *.xls)

Functions used in this article

OR(logical1, logical2, ...)
Checks whether any argument are TRUE and returns TRUE or FALSE. Returns FALSE only if all arguments are FALSE.

AND(logical1, logical2, ...)
Checks whether all arguments are TRUE and returns TRUE if all arguments are TRUE

Recommended reading

Built-in conditional formatting

Highlight cells rule

Top bottom rules

Basic CF formulas

Search using CF

Unique distinct

Duplicates

Compare

Min max

Dates

Misc

Excel categories

Leave a Comment