How to Apply Borders in Excel with Conditional Formatting

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:

• 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:

• The following alternative formulas can create new formatting rules to apply cell borders to columns.

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:

• 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.

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:

• 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.

• 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.

• 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.

• 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.

• 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