How to Apply Conditional Formatting to Each Row Individually

Get FREE Advanced Excel Exercises with Solutions!

Conditional Formatting is a versatile feature embedded in Excel, that comes up with a lot of flexibility 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 3 easy ways on how to apply conditional formatting to each row in Excel individually.

overview of using new rule to apply conditional formatting each row individually

Here, we have used Conditional Formatting to highlight all the rows, having a quantity of more than 25. Read through the rest of the part to learn more along with some other methods in detail.


Download the Practice Workbook

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


3 Easy Ways to Apply Conditional Formatting to Each Row Individually

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

Sample Dataset showing categorized list of products

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


1. Using 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 more delay, let’s get straight on the steps right now.

Steps:

  • First of all, select the entire data table.
  • Then go to Home ▶ Conditional formatting ▶ New Rule.

apply new rule in conditional formatting

  • After hitting the New Rule command, a dialog box called New Formatting Rule will appear.
  • 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.

selecting use formula to determine which cells to format option in the 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 showing highlighted cells after applying new rule in conditional formatting

Read more: How to Highlight Row Using Conditional Formatting


2. Applying Conditional Formatting to Each Row with Highlight Cells Rules

Now you have to highlight all the cells in the column Quantity, having a quantity of more than 30. To implement this, we have used the Highlight Cells Rules command here. So, without having any further discussion, let’s dive straight into the steps.

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.

use of highlight cells rules in conditional formatting

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

entering value 30 in the greater than box

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

Applying Conditional Formatting to Each Row with Highlight Cells Rules Result

Read more: How to Apply Conditional Formatting to Multiple Rows


3. Utilizing 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 you have 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 follow the steps.

Steps:

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

utilize top/bottom rules in excel conditional formatting

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

pop-up dialog box

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

output of Utilizing Top/Bottom Rules to Apply Conditional Formatting to Each Row

Read more: Conditional Formatting on Multiple Rows Independently in Excel


How to Clear Rules from Each Row

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

Steps:

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

clear rules in conditional formatting

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

final output after clearing 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. We recommend you 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 Roy
Mrinmoy Roy

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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo