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.

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.

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.

Now, a Greater Than window appears.

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

Ta-dah! That is how simple it is to apply Conditional Formatting to 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:

• Initially, follow the steps shown previously to apply Conditional Formatting to the E5 cell >> Right-click to open the Contextual Menu >> click on Copy.

• Then, select the C5:E11 cells >> press the Paste drop-down >> choose the Paste with Formatting option.

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

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:

• At the very beginning, apply Conditional Formatting to the E5 cell by following the previous steps >> observe the live demonstration as shown in the animated GIF.

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

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:

• In this scenario, Conditional Formatting is already applied to the C5 cell as shown prior >> observe the GIF to follow the steps in real-time.

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

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.

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.

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

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.

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.