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