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

Get FREE Advanced Excel Exercises with Solutions!

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.

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. Highlighting Multiple Rows with 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

Read More: How to Apply Conditional Formatting to Each Row Individually


2. Applying Conditional Formatting to Multiple Rows 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

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

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

Read More: How to Highlight Row Using Conditional Formatting


3. Applying Conditional Formatting to Multiple Rows with Excel 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

Read More: Conditional Formatting on Multiple Rows Independently in Excel


4. Using Fill Handle Tool to Apply Conditional Formatting to Multiple Rows

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 Excel Conditional Formatting to Multiple Rows 

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 that 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


Download Practice Workbook


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.


Conditional Formatting Rows in Excel: Knowledge Hub

<< Go Back to Conditional Formatting | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Prantick Bala
Prantick Bala

PRANTICK BALA is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he has transitioned into the role of a content developer. Beyond his work, find him immersed in books, exploring new... Read Full Bio

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

  2. HELLO
    I NEED TO APPLY A CONDITIONAL FORMATING (THE MINIMUM MALUE) IN ROW TO MULTIPLE ROW ROW BY ROW

    • Hi JAD,
      I hope you are doing well. Thank you very much for reading our articles.
      You want to apply conditional formatting to the minimum value in each row across multiple rows. For that, you need to use a formula in the conditional formatting. Follow the instructions given below:

      1. Select the data range.
      2. Click on the Conditional Formatting drop-down.
      3. Select New Rule to appear Edit Formatting Rule window.
        Go to the New Rule of Conditional Formatting
      4. In the Edit Formatting Rule window:
        • • Go to Select a Rule Type > Use a formula to determine which cells to format.
        • • Insert the following formula into the “Format values where this formula is true” box: =C5=MIN($C5:$E5)
        • • Set a color to highlight using the Format button.
        • • Finally, press OK.

        Insert a formula to apply Conditional formatting

      You can see minimum values of each row have been highlighted.
      Minimum values have been highlighted after applying Conditional Formatting

      Regards,
      Alok Paul
      ExcelDemy Team

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo