How do I copy conditional formatting for each row

Conditional Formatting allows you to highlight cells based on any criteria. In this article, Ill show you 5 ways by which you can apply conditional formatting to multiple rows.Lets

How do I copy conditional formatting for each row

Conditional Formatting allows you to highlight cells based on any criteria. In this article, Ill show you 5 ways by which you can apply conditional formatting to multiple rows.

Lets say we have a dataset where scores of different students in three different subjects are given in multiple rows. We want to highlight those cells where the scores are equal to or greater than 80 (greater than 79).

dataset

Table of Contents hideDownload Practice Workbook5 Ways to Apply Conditional Formatting to Multiple Rows1. Apply Conditional Formatting to Multiple Rows by Selecting cells2. Using Paste Special Feature3. Format Painter to Apply Conditional Formatting to Multiple Rows4. By Dragging the Fill Handle5. Conditional Formatting Rules ManagerConclusionFurther Readings

Download Practice WorkbookApply Conditional Formatting to Multiple Rows.xlsx

5 Ways to Apply Conditional Formatting to Multiple Rows

1. Apply Conditional Formatting to Multiple Rows by Selecting cells

You can apply conditional formatting to multiple rows by selecting cells from different rows.

First, select all the cells where you want to apply conditional formatting. After that, go to Home > Conditional Formatting > Highlight cell rules. You can select your criteria type from here. For our dataset, we want to highlight cells which are greater than 79. So we will select Greater Than.

conditional formatting

Now, a window named Greater Than will appear. In the Format cells that are Greater THAN box type criteria number which is 79 for our dataset and in the with box select your preferred color of Fill and Text which you want to apply in formatting. Finally, click on OK.

format box

As a result, the cells which have a number greater than 79 in all rows will be highlighted.

conditional formatting to multiple rows

Read more: Conditional Formatting on Multiple Rows Independently in Excel

2. Using Paste Special Feature

You can apply conditional formatting to multiple rows by using Paste Special if one of your cells already has formatted. Suppose, cell D6 is highlighted based on the condition of being greater than 79 (we showed it to apply greater than conditional formatting in the previous method). Now, we will apply the same formatting in all other rows.

Firstly, Copy cell D6 by right clicking on it.

copy

Now select all the cells and right click. A dropdown menu will appear. Select Paste Special from this menu.

paste special

As a result, a Paste Special window will appear. From this window select Formats and click on OK.

paste special box

Finally, you will get the cells which contain a number greater than 79 highlighted.

conditional formatting to multiple cells

Similar Readings:

  • How to Highlight Row Using Conditional Formatting (9 Methods)
  • Apply Conditional Formatting to Each Row Individually: 3 Tips
  • Excel Alternating Row Color with Conditional Formatting [Video]

3. Format Painter to Apply Conditional Formatting to Multiple Rows

Format Painter is an amazing feature of Excel by which you can easily apply the format of one cell to other cells. First, select the cell where conditional formatting is already applied. After that, go to the Home tab and click on the Format Painter icon.

Format Painter

Now, just select the cells from multiple rows. Conditional formatting will be automatically applied to all the cells.

conditional formatting to multiple rows

4. By Dragging the Fill Handle

Another way to apply conditional formatting to multiple rows is by dragging the formatted cells. In cell B6 conditional formatting is already applied. The value of B6 is less than 79 thats why it is not highlighted. Now, keep your cursor at the right bottom of the cell and a small plus sign will be shown. At this moment click on the cell and drag it to the end of your dataset.

drag cell

Now you can see the Fill Options at the end of your dataset. Click on this and select Fill Formatting Only. It will apply the conditional formatting of cell B6 to all other cells in column B.

Fill options

To apply conditional formatting in other columns, simply drag column B to the right and select Fill Formatting Only from the Fill Options.

conditional formatting to multiple rows

5. Conditional Formatting Rules Manager

Another way of applying conditional formatting is using Rules Manager from the Conditional Formatting ribbon. First, select your formatted cell. Then go to Home > Conditional Formatting > Manage Rules.

manage rules

After that, the Conditional Formatting Rules Manager window will appear. In the Applies to box insert the cell range where you want to apply conditional formatting. At last, click on OK.

rules manager

As a result, conditional formatting will be applied to your selected cell range.

conditional formatting to multiple rows

Conclusion

You can apply conditional formatting to multiple rows by using any of the above described methods. If you have any confusion, please leave a comment.


Further Readings

  • How to Do Conditional Formatting for Multiple Conditions (8 Ways)
  • Excel Conditional Formatting on Multiple Columns
  • How to Highlight Lowest Value in Excel (11 Ways)
  • Excel Conditional Formatting Formula

Video liên quan