Prantick
Conditional Formatting allows you to highlight cells based on any criteria. In this article, I’ll show you 5 ways by which you can apply conditional formatting to multiple rows.
Let’s 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).
Download Practice Workbook
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.
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.
As a result, the cells which have a number greater than 79 in all rows will be highlighted.
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.
Now select all the cells and right click. A dropdown menu will appear. Select Paste Special from this menu.
As a result, a Paste Special window will appear. From this window select Formats and click on OK.
Finally, you will get the cells which contain a number greater than 79 highlighted.
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.
Now, just select the cells from multiple rows. Conditional formatting will be automatically applied to all the cells.
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 that’s 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.
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.
To apply conditional formatting in other columns, simply drag column B to the right and select Fill Formatting Only from the Fill Options.
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.
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.
As a result, conditional formatting will be applied to your selected cell range.
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.
