Create a dynamic border to your list using excel conditional formatting
Question: I have a list that I keep adding rows to. How do i create a border that expands as the list expands?
Answer: 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:
- Select the top cells of your list
- Go to "Home" tab in excel 2007
- Click the small triangle on the border button in the font window. See picture below.

- Click "Top border"
The border on the sides of the list is dynamic. Here is how to create the border on the left side:
- Select the leftmost column in your list (In this example C:C)
- Click "Home" tab on the ribbon
- Click "Conditional formatting"
- Click "New rule..."
- Click "Use a formula to determine which cells to format"
- Click "Format values where this formula is true" window.
- Type =OR(C1<>"",D1<>"",E1<>"",F1<>"")
- Click Format button
- Click "Border tab" tab
- Create a border on left side of cell
- Click OK!
- Click OK!
Here is how to create the left and bottom border on the left side:
- Select the leftmost column in your list (In this example C:C)
- Create a new conditional formatting formula. (See above list)
- Type =AND(OR($C1<>"",$D1<>"",$E1<>"",$F1<>""),$C2="",$D2="",$E2="",$F2="")
- 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:
- Select the middle columns in your list (In this example D:D and E:E)
- Create a new conditional formatting formula.
- Type =AND(OR($C1<>"",$D1<>"",$E1<>"",$F1<>""),$C2="",$D2="",$E2="",$F2="")
- 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
Related posts:
- Color every second row using dynamic conditional formatting in excel
- Prevent duplicates using dynamic conditional formatting in excel
- How to create a unique list using conditional formatting in excel 2007
- Highlight dates within a date range using conditional formatting
- Highlight duplicates using conditional formatting in excel
- Highlight the second or more duplicates in two lists using conditional formatting in excel
- Highlight smallest duplicate value in a column using conditional formatting in excel
- Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel
- Highlight unique values and unique distinct values in a range using conditional formatting in excel
- Highlight duplicate values in a range using conditional formatting in excel




Leave a Reply