Md. Shamim Reza
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.
Download Practice Workbook
You can download the practice workbook from the download button below.
4 Ways to Apply Borders in Excel Conditional Formatting
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.
- Next select Format only cells that contain >> Format only cells with >> No Blanks >> Format as shown below.
- 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.
- Finally, enter values in the cells and you will see cell borders added to those cells automatically.
Read More: How to Apply All Borders in Excel (4 Quick Methods)
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.
- 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)>0Read More: How to Insert Border in Excel (5 Simple Methods)
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.
- 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.
- After that, you will see the following result.
- You can use the following formula instead to group columns based on their values.
=B$2<>C$2Read More: How to Add or Remove Cell Borders 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.
- 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="")
- 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<>"")
- 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="")
- 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="")
- 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 Add or Remove Dotted Border in Excel (4 Easy Ways)
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.
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. You can also visit our ExelDemy blog to explore more about excel. Stay with us and keep learning.
Related Articles
- How to Cancel Moving Border in Excel (4 Quick Tricks)
- [Fixed!] Table Border Not Showing in Print Preview (2 Solutions)
- How to Print Borders at Page Break in Excel (2 Quick Methods)
- Change Border Color in Excel (3 Suitable Ways)
- How to Remove Page Border in Excel (3 Methods)
- How to Remove Borders in Excel (4 Quick Ways)
