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

create-a-dynamic-list-border-using-excel-conditional-formatting3

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

create-a-dynamic-list-border-using-excel-conditional-formatting11

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. Click the small triangle on the border button in the font window. See picture below.create-a-dynamic-list-border-using-excel-conditional-formatting2
  4. Click "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. Click "Home" tab on the ribbon
  3. Click "Conditional formatting"
  4. Click "New rule..."
  5. Click "Use a formula to determine which cells to format"
  6. Click "Format values where this formula is true" window.
  7. Type =OR(C1<>"",D1<>"",E1<>"",F1<>"")
  8. Click Format button
  9. Click "Border tab" tab
  10. Create a border on left side of cell
  11. Click OK!
  12. Click 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

Download 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