How to Apply Borders in Excel with Conditional Formatting

Get FREE Advanced Excel Exercises with Solutions!

This article illustrates how to apply conditional formatting borders in Excel. Conditional formatting is one of the most useful features in Excel. You can use it to apply borders to cells automatically saving time and effort. Follow the article and you will be able to apply it in 4 useful cases.


Apply Borders in Excel Conditional Formatting: 4 Ways

1. Apply Conditional Formatting with Borders for Non-Blank Cells

Follow the steps below to apply cell borders automatically whenever you enter values.

📌 Steps:

  • First, select the desired range to apply the formatting. If you want to apply it to the entire worksheet, click on the downward arrow in the upper-left corner of the first cell. Then select Home >> Conditional Formatting >> New Rule.

new conditional formatting rule

  • Next select Format only cells that contain >> Format only cells with >> No Blanks >> Format as shown below.

format only nonblank cells

  • After that, go to the Border tab from the Format Cells dialog box. Change the line style and color if you want. Now click on the Outline preset or any other border type from the Borders section as required. Click OK After that.

apply conditional formatting borders

  • Finally, enter values in the cells and you will see cell borders added to those cells automatically.

Read More: How to Copy Conditional Formatting to Another Sheet


2. Conditional Formatting with Borders for Non-Empty Rows/Columns

Follow the steps below to apply cell borders automatically to non-empty rows or columns whenever you enter values.

📌 Steps:

  • Assume you need to apply a border to the entire row whenever you enter data in cells in column A.
  • Then first select the desired range or the entire sheet to apply the formatting. Then go to Home >> Conditional Formatting >> New Rule.
  • Next, select Use a formula to determine which cells to format as the rule type. After that, enter the following formula in the formula box.
 =$A1<>""
  • Then click on Format, select the Outline preset, and click OK.

conditional formatting borders to entire rows or columns

  • Finally, enter values in cells in column A and you will see cell borders applied to the corresponding rows.
  • If you want to apply a border to the entire row by entering values in any cell, use the following formula instead.
=COUNTA($A1:$XFD1)>0
  • You can use the following alternative formulas to create new formatting rules to apply cell borders to columns.
 =A$1<>""
=COUNTA(A$1:A$1043576)>0

Read More: How to Copy Conditional Formatting with Relative Cell References in Excel


3. Conditional Formatting with Borders for Groups of Rows/Columns

Follow the steps below to apply cell borders automatically to group rows or columns together based on their values.

📌 Steps:

  • Consider the following dataset. Assume you need to apply borders to separate rows of data based on the dates.

dataset

  • First, select the range and go to Home >> Conditional Formatting >> New Rule. Then select Use a formula to determine which cells to format as the rule type. After that, enter the following formula in the formula box.
=$B2<>$B3
  • Next, click on Format, select the Bottom Border from the Border tab and click OK.

conditional borders for group of rows

  • After that, you will see the following result.

data grouped by borders

  • You can use the following formula instead to group columns based on their values.
=B$2<>C$2

Read More: How to Copy Conditional Formatting to Another Workbook in Excel


4. Conditional Formatting with Borders for Dynamic Ranges

Assume you need to apply conditional formatting to apply outside borders to a dynamic range. Follow the steps below to be able to do that.

📌 Steps:

  • First, consider an extended range to which you need to enter data (for example, B2:D50). Then select the leftmost column (B2:B50) within the range and go to Home >> Conditional Formatting >> New Rule.
  • Next, select Use a formula to determine which cells to format as the rule type. After that, enter the following formula in the formula box.
=OR(B2<>"",C2<>"",D2<>"")
  • Now click on Format, select the Left Border from the Border tab, and click OK.

dynamic conditional formatting borders

  • Then, apply a new conditional formatting rule on the same range using the following formula. But this time select the Left and Bottom borders in the Format Cells dialog box.
=AND(OR($B2<>"",$C2<>"",$D2<>""),$B3="",$C3="",$D3="")

add rule for conditional borders

  • Next, apply a new conditional formatting rule to the rightmost column (D2:D50) within the range using the following formula. Select the Right Border in the Format Cells dialog box in this case.
=OR(B2<>"",C2<>"",D2<>"")

formatting rule for right borders

  • Then, apply another new conditional formatting rule on the same range using the following formula. But this time select the Right and Bottom borders in the Format Cells dialog box.
=AND(OR($B2<>"",$C2<>"",$D2<>""),$B3="",$C3="",$D3="")

new conditional formatting rule

  • Finally, select the middle columns (in this case, range C2:C50 only) and apply another conditional formatting rule using the following formula. You must select the Bottom Border only in this case.
=AND(OR($B2<>"",$C2<>"",$D2<>""),$B3="",$C3="",$D3="")

bottom border for middle columns

  • Now enter values anywhere within the extended range (B2:D50) and the outline border will be extended automatically to include that data.

Read More: How to Copy Conditional Formatting But Change Reference Cell in Excel


Things to Remember

  • You must select the desired range before creating a new conditional formatting rule.
  • The formulas contain mixed references. Enter them properly otherwise, you won’t get the desired result.

Download Practice Workbook

You can download the practice workbook from the download button below.


Conclusion

Now you know how to apply conditional formatting borders in Excel. Do you have any further queries or suggestions? Please let us know in the comment section below. Stay with us and keep learning.


Related Articles

<< Go Back to Conditional Formatting | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo