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:
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.
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:
❸ After that pickup a color using the Format option.
❹ Finally hit the OK command.
When you are done with all the steps mentioned above, you will see two rows have been highlighted as in the image below:
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.
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:
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.
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:
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.
Things to Remember
📌 Always select the cells before applying the Conditional Formatting command.
📌 Press CTRL + Z to undo the Conditional Formatting command.
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.