See all How-To Articles

Apply Conditional Formatting to Multiple Rows in Excel & Google Sheets

In this article, you will learn how to apply conditional formatting to multiple rows in Excel and Google Sheets.

 

create conditional formatting rule final

 

Apply Conditional Formatting to Multiple Rows

If you have conditional formatting in one cell in Excel, you can apply it to multiple rows in a few different ways. Let’s show first how to create a conditional formatting rule for one cell. Say you have the list of numbers below in Column B.

 

create conditional formatting rule initial data

 

Create Conditional Formatting in a Single Cell

First, create a rule that highlights cell B2 in red if its value is greater than 20.

  1. Select a cell and in the Ribbon, go to Home > Conditional Formatting > Highlight Cells Rules > Greater Than.

 

apply conditional formatting to multiple rows initial data

 

  1. In the pop-up window, enter 20 and click OK, leaving the default formatting (Light Red Fill with Dark Red Text).

 

create conditional formatting rule

 

Cell B2 is formatted in red, because its value is greater than 20.

 

create conditional formatting rule final

 

Next, we’ll show how to apply this formatting to other rows in the range.

Apply to More Cells by Copy-Pasting

The first option is to use copy-paste to copy only formatting to other cells.

  1. Right-click a cell with conditional formatting and click Copy (or use the keyboard shortcut CTRL + C).

 

apply conditional formatting to multiple rows 1

 

  1. Select and right-click a range where you want to paste the conditional formatting. Then click on the arrow next to Paste Special and choose Formatting.

 

apply conditional formatting to multiple rows 2

 

As a result, the formatting rule is copied to the whole range, and all cells with numbers greater than 20 are now red (B3, B5, B6, and B9).

 

apply conditional formatting to multiple rows final

 

Note: In this example, there’s a fixed value in the formatting rule. In cases where you have formulas as rules, you must pay attention to cell references in those formulas when copying the formatting.

 

Edit Conditional Formatting Rules

Instead of copying and pasting formats, you can edit the conditional formatting rules to extend the range they’re applied to. Each formatting rule has a range assigned to it. In this case, the range is cell B2. To edit a rule and apply conditional formatting for multiple rows, follow these steps:

  1. Select a cell with a conditional formatting rule and in the Ribbon, go to Home > Conditional Formatting > Manage Rules.

 

apply conditional formatting edit rule

 

  1. In the Rules Manager window, (1) set the range to =$B$2:$B$10 in the Applies to box and (2) click OK.

 

apply conditional formatting edit rule 1

 

The output is exactly the same as in the previous section: All cells with numbers greater than 20 are highlighted.

Apply Conditional Formatting to Multiple Rows in Google Sheets

You can also apply conditional formatting to multiple rows in Google Sheets in the same ways shown above. Let’s first show how to create a conditional formatting rule for one cell.

Create Conditional Formatting in a Single Cell

To create a rule in cell B2 that will highlight the cell if the value is greater than 20, follow these steps:

  1. Select the cell, and in the Menu, go to Format > Conditional formatting.

 

google sheets conditional formatting create

 

  1. In the formatting window on the right side, (1) select Greater than under Format rules, (2) enter 20, and (3) click Done.
    This keeps the default formatting color (green), but you can change it if you want by clicking on the Fill color icon.

 

google sheets conditional formatting create 1

 

Since the value of cell B2 is 47, greater than 20, the cell is highlighted in green.

 

google sheets conditional formatting create 2

 

Apply to More Cells by Copy-Pasting

  1. Right-click a cell with a conditional formatting rule and click Copy (or use the keyboard shortcut CTRL + C).

 

google sheets apply conditional formatting to multiple rows

 

  1. Select and right-click the range where you want to paste the formatting rule (B3:B10), (2) click Paste special, and (3) choose Paste conditional formatting only.

 

google sheets apply conditional formatting to multiple rows 1

 

As a result, the formatting rule is applied to the entire data range in Column B (B2:B10).

 

google sheets apply conditional formatting to multiple rows final

 

Edit Conditional Formatting Rules

As in Excel, you can also edit the existing rule to expand the formatted range.

  1. Select a cell with conditional formatting (B2), and in the Menu, go to Format > Conditional formatting.

 

google sheets apply conditional formatting edit rule

 

  1. On the right side of your browser, the conditional formatting window appears, showing the rules assigned to the selected cell. Click on the rule to edit it.

 

google sheets apply conditional formatting edit rule 1

 

  1. Set the range to B2:B10 and click Done.

 

google sheets apply conditional formatting edit rule 2

 

Again, the rule is now applied to the entire range.