How to Apply Conditional Formatting to Multiple Rows (5 Ways)

Without a doubt, Conditional Formatting is a nifty feature of Excel that allows us to highlight cells based on any criteria. So, we can, at a glance, locate the cells that satisfy the given condition. Granted this, in this article, we’ll describe 5 ways how to apply Conditional Formatting to multiple rows.

The GIF below is an overview of the article which represents the application of Conditional Formatting to multiple rows.

Overview of Applying Conditional Formatting to mutiple rows

In the following sections, we’ll learn about the dataset and explore each method in detail and with the proper illustrations.


Download Practice Workbook


5 Ways to Apply Conditional Formatting to Multiple Rows

First and foremost, let’s consider the List of Students Marks dataset shown in the B4:E11 cells containing the Student, Mathematics, Physics, and Biology columns respectively. Here, we want to highlight those cells with scores greater than 79. Therefore, let’s see it in action.

Dataset for applying conditional formatting to mutiple rows

Here, we have used the Microsoft Excel 365 version; you may use any other version at your convenience.


1. Selecting Multiple Rows to Apply Conditional Formatting

In the first place, let’s start with the simplest way to apply Conditional Formatting to multiple rows. Now allow us to demonstrate the process in the steps below.

📌 Steps:

  • First of all, select all the cells, in this case, C5:E11 >> go to Conditional Formatting >> Highlight Cell Rules >> select the Greater Than option.

Selecting conditional formatting, highlight cell rules and choosing greater than

Now, a Greater Than window appears.

  • Next, in the Format cells that are GREATER THAN box, enter 79 >> click on OK.

Entering greater than value

Ta-dah! That is how simple it is to apply Conditional Formatting to multiple rows.

apply conditional formatting to multiple rows by selecting multiple rows


2. Implementing Conditional Formatting with Paste Special Feature

Besides, we can use the Paste Special option to copy-paste the Conditional Formatting if one of the cells already has been formatted. Suppose, cell E5 is highlighted based on the condition that its value is greater than 79 and now we want to apply the same formatting in all other rows.

📌 Steps:

Select copy from contextual menu

Using paste special only formatting option

Finally, the results should look like the image given below.

apply conditional formatting to multiple rows with paste special


3. Applying Conditional Formatting with Format Painter

Alternatively, Format Painter is another amazing feature of Excel by which we can easily apply the format of one cell to other cells. Therefore, just follow along.

📌 Steps:

Applying format painter

Eventually, the final output should look like the picture shown below.

apply conditional formatting to multiple rows with format painter


4. Utilizing Fill Handle Tool to Employ Conditional Formatting

For one thing, we can also implement the Fill Handle tool to apply conditional formatting to multiple rows by dragging the formatted cells.

📌 Steps:

Utlizing Fill handle to apply conditional formatting

Boom! The final output should resemble the figure shown below.

apply conditional formatting to multiple rows with Fill handle


5. Using Manage Rules to Apply Conditional Formatting

Last but not least, the Manage Rules feature within the Conditional Formatting drop-down can also be used to format multiple rows. Now, allow us to demonstrate the process in the steps below.

📌 Steps:

  • First, select the C5 cell which has Conditional Formatting >> move to Conditional Formatting >> choose the Manage Rules option.

Selecting Manage rules

Not long after, the Conditional Formatting Rules Manager wizard pops out.

  • At this point, in the Applies to box insert the C5:E11 cell range >> click Apply >> hit OK.

Entering cell reference

Consequently, your final output should appear in the screenshot given below.

apply conditional formatting to multiple rows with manage rules


Practice Section

We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

Practice Section


Conclusion

To sum up, we hope this article helps you understand how to apply Conditional Formatting to multiple rows. Now, if you have any queries, please leave a comment below. And, you can read more articles like this on our website ExcelDemy.

Prantick Bala

Prantick Bala

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

2 Comments
  1. Very good, however, if I want to copy conditional formatting from cell J3 to J34 but also the Cell Value move in line with the row.

    Example.
    I copy conditional formatting from cell J3 to J4 but the new J4 conditional format still looks at the data in D3 and not D4.
    This repeats over multiple cells, if I copy CF from J3 and Format Painter from J4 to J34 every cell from J4 to J34 looks at D3 for the value rather than column D on the same row.

    Only way around is to do each cell individual or go into CF manager and update each one.

    Does anyone has a solution, something like an option to change in Excel 🙂

    • Reply Avatar photo
      Naimul Hasan Arif Nov 15, 2022 at 12:45 PM

      If you look at our method 5, we have tried to apply conditional formatting based on a certain condition. The value was fixed all along the applied range.

      But with changing conditional values, we need more detailed information. Please contact us through mail or any social media with your dataset.

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo