Apply Conditional Formatting to Each Row Individually: 3 Tips

Conditional formatting is a versatile feature embedded in Excel, that comes up with a lot of flexibilities in terms of highlighting cells. You can apply conditional formatting to a single cell or a row as well as multiple cells or rows. It also allows you to undo your applied conditional formatting to certain cells or from the entire spreadsheet. In this article, you will receive 4 special tips on how to apply conditional formatting to each row in Excel individually.


Download the Practice Workbook

You are recommended to download the Excel file and practice along with it.


3 Tips to Apply Conditional Formatting to Each Row Individually

In this article, we will be using a sample product price list as a dataset to demonstrate all the methods. So, let’s have a sneak peek of the dataset:

Dataset for 4 Tips to Apply Conditional Formatting to Each Row Individually

So, without having any further discussion let’s dive straight into all the methods one by one.


1. Use New Rule to Apply Conditional Formatting to Each Row

The new rule command allows you to create user-defined conditions based on which it can format or highlight cells.

In our demo dataset, you will see a product price list having a column called Quantity. Based on the values of this column, we are going to highlight all the rows, having a quantity of more than 25.

So without having more delay, let’s get straight on the steps right now.

❶ First of all, select the entire data table.

❷ Then go to Home ▶ Conditional formatting ▶ New Rule.

usage of new rule to apply conditional formatting to each row individually

After hitting the New Rule command, a dialog box called New Formatting Rule will appear. Now all you need to do is,

❶ Select Use a formula to determine which cells to format.

❷ Then insert the formula:

=$D5>25
within the Format values where this formula is true.

❸ After that pickup a color using the Format option.

❹ Finally hit the OK command.

New formatting rule dialog box

When you are done with all the steps mentioned above, you will see two rows have been highlighted as in the image below:

result of applying new rule: conditional formatting each row individually

Read more: How to Highlight Row Using Conditional Formatting


2. Use Highlight Cells Rules to Apply Conditional Formatting to Each Row

Now we want to highlight all the cells in the column Quantity, having a quantity of more than 30.

To implement this, we will use the Highlight Cells Rules command here.

So without having any further discussion, let’s dive straight into the steps.

❶ First of all, select the entire column of your dataset.

❷ Then go to Home ▶ Conditional Formatting ▶ Highlight Cells Rules.

After approaching so far, you will have so many more options than you can utilize to format or highlight your data in your data table. But we will choose the Greater Than command among all the other options. Because we want to highlight only those values having a quantity of more than 30.

highlight cells rules selection

After selecting the Greater Than command, the Greater Than dialog box will appear. Now,

❸ Insert 30 within the insertion box.

❹ Then hit the OK command.

When you are done with all the steps mentioned above, you will see the end result as in the image below:

Read more: How to Apply Conditional Formatting to Multiple Rows


3. Use Top/Bottom Rules to Apply Conditional Formatting to Each Row

Another way to apply conditional formatting to each row individually is to use the Top/Bottom Rules command.

Using this command, now we want to highlight all the cells having a quantity below the average within the Quantity column.

To make this thing come into action, all you need to do is,

❶ Select the entire column, Quantity.

❷ Then go to Home ▶ Conditional Formatting ▶ Top/Bottom Rules.

After approaching this, we will see a bunch of options appear. Among the options, we want to pick up the Below Average command.

Top/Bottom Rules: conditional formatting each row individually

After choosing the Below Average command, a dialog box named Below Average will appear. All you need to do is just hit the OK command within the pop-up dialog box.

When you are done with all the steps as mentioned above, you will see the end result as illustrated in the image below:

Read more: Conditional Formatting on Multiple Rows Independently in Excel


Clear Rules from Each Row

After applying conditional formatting, you may feel the necessity of removing the applied formatting from a specific cell. If so, then you are recommended to follow the steps below to learn to clear rules from your applied cells.

❶ First of all, select the range of the cells, where you have already applied the conditional formatting.

❷ After the go-to Home ▶ Conditional Formatting ▶ Clear Rules ▶ Clear Rules from Selected Cells.

After hitting the Clear Rules command from selected cells, you will see all of your applied cell formattings have been removed at once.

conditional formatting clear rules


Things to Remember

📌 Always select the cells before applying the Conditional Formatting command.

📌 Press CTRL + Z to undo the Conditional Formatting command.


Conclusion

To sum up, we have provided 3 tips to apply conditional formatting to each row individually in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.


Further Readings

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo