Method 1 – Applying Conditional Formatting with Borders for Non-Blank Cells
Steps:
- Select the desired range to apply the formatting. To select the entire worksheet, click on the downward arrow in the upper-left corner of the first cell. Then select Home >> Conditional Formatting >> New Rule.
- Select Format only cells that contain >> Format only cells with >> No Blanks >> Format as shown below.
- Go to the Border tab from the Format Cells dialog box. Change the line style and color if you want.
- Click on the Outline preset or any other border type from the Borders section as required.
- Click OK.
- Enter values in the cells to see cell borders added to those cells automatically.
Read More: How to Copy Conditional Formatting to Another Sheet
Method 2 -Applying Conditional Formatting with Borders for Non-Empty Rows/Columns
Steps:
- Apply a border to the entire row when you enter data in cells in column A.
- Select the desired range or the entire sheet to apply the formatting.
- Go to Home >> Conditional Formatting >> New Rule.
- Select Use a formula to determine which cells to format as the rule type.
- Enter the following formula in the formula box:
=$A1<>""
- Click on Format, select the Outline preset.
- Click OK.
- Enter values in cells in column A to see cell borders applied to the corresponding rows.
- To the entire row by entering values in any cell, use the following formula:
=COUNTA($A1:$XFD1)>0
- The following alternative formulas can 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
Method 3 – Applying Conditional Formatting with Borders for Groups of Rows/Columns
Steps:
- Use the following dataset. Apply borders to separate rows of data based on the dates.
- Select the range and go Home >> Conditional Formatting >> New Rule.
- Select Use a formula to determine which cells to format as the rule type.
- Enter the following formula in the formula box:
=$B2<>$B3
- Click on Format, select the Bottom Border from the Border tab, and click OK.
You will see the following result.
- You can use the following formula instead to group columns based on their values.
=B$2<>C$2
Method 4 – Applying Conditional Formatting with Borders for Dynamic Ranges
Steps:
- Use an extended range to enter data (for example, B2:D50).
- Select the leftmost column (B2:B50) within the range and go to Home >> Conditional Formatting >> New Rule.
- Select Use a formula to determine which cells to format as the rule type.
- Enter the following formula in the formula box:
=OR(B2<>"",C2<>"",D2<>"")
- Click on Format, select the Left Border from the Border tab, and click OK.
- Apply a new conditional formatting rule on the same range using the following formula.
- Select the Left and Bottom borders in the Format Cells dialog box.
=AND(OR($B2<>"",$C2<>"",$D2<>""),$B3="",$C3="",$D3="")
- 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<>"")
- Apply another new conditional formatting rule on the same range using the following formula.
- Select the Right and Bottom borders in the Format Cells dialog box.
=AND(OR($B2<>"",$C2<>"",$D2<>""),$B3="",$C3="",$D3="")
- 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="")
- 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 the Practice Workbook
You can download the practice workbook from here.
Related Articles
- How to Make Yes Green and No Red in Excel
- How to Create a Rating Scale in Excel
- How to Use Conditional Formatting on Text Box in Excel
- How to Apply Alignment in Excel Conditional Formatting
- How to Copy Conditional Formatting to Another Cell in Excel
- How to Copy Conditional Formatting Color to Another Cell in Excel
<< Go Back to Conditional Formatting | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Thank you for the detailed instructions on applying borders!!
Hello TTunstall,
You are most welcome. Thanks for your feedback and suggestions. Glad to hear our detailed explanations helped you Keep exploring Excel with ExcelDemy!
Regards
ExcelDemy