How to change the row color in Excel based on a cell's value
Microsoft Excel allows changing the row colour based on the cell's value, and it is often required to highlight the cell based on its value.
In this tutorial, we will cover the different methods, tips and formula examples to highlight entire rows filled with number and text values. We will be covering the below topics:
Let's get started!
Steps to change the colour of a row based on the values of a single cell
We are given the following data, and are asked to shade the cells.
To distinguish between the values many times, we shade the rows (Carrying distinct values) with different colours. It helps end users instantly determine the values and see the most important orders at a glance. Now the question arises of how to get it done in Excel, and the solution to this problem is Conditional Formatting.
Perform the following steps to quickly change the row colour based on a number for a single cell:
Note: Make sure to put the dollar symbol $ before the cell's address, and it will ensure that the same column letter persists when the formula gets copied across the row. It's the only trick you must remember to apply the formatting to the whole row based on a value in the selected cell.
Filling out cells with a single colour is easy. Now let's move forward and add multiple rules within a selected range of cells.
How to add multiple rules with the priority you require
In the previous example, we highlighted the Excel rows with single colour based on values in the Number of files field. Now, if you want to apply a second rule in the same dataset where we want to change the colour of the cells if the value is greater than 20, for instance, you can apply a rule to colour the rows that contain the quantity 10 or greater. Use the below formula to get it done!
Once you have created the second formatting rule, we need to set the priority using the following steps:
How to change a row color based on a text value in a cell
So far, we have applied conditional formatting to the cells based on numbers. You can easily apply conditional formatting based on text values as well. For instance, in the following table, you can highlight the cells based on their delivery status so that:
Now the question arises of how to implement the above logic in a formula. If we apply a formula using the direct keywords like "Updated" (=$F3= "Updated"), "Submitted" (=$F3= "Submitted") or "Pending" (=$F3= "Pending"), the formula won't give the desired results. It's because the above formula starts looking for an exact match, and our text contains more text; therefore, an exact match won't give what we want.
In such cases, the best method is to take advantage of the Search function that works for the partial match as well. Perform the following steps to quickly change the row colour based on the partial match:
Easy Isn't it!
Highlight row if cell starts with specific text
If in the formula we use >0, it represents that no matter what text value we supply, the specified row will be coloured. For instance, the column (F) contains the value "status", since the key value is greater than 0, therefore this row will be coloured.
Now, we can modify the formula where the row will be coloured only if the key cell starts with a given text, and to implement this we will use =1 in the formula,
The above formula represents that the row will only be highlighted if the given text is found at the first position of the specified text in the cell.
NOTE: Sometimes, if you text contains leading spacing, in such cases this formula may not work properly. Therefore, ensure that there are no leading spaces present in the key column, else the formula will not return an optimal solution,
How to change a cell's color based on another cell's value
It's a simple and easy variation of changing a cell's colour based on another cell's values. It is mostly useful when instead of changing the entire dataset, you choose to select the background colour of some specific column or range of cells.
For example, we could create the above formula rules only, but instead of shading the entire dataset, we can specify only column D (Files field), where column D will be shaded based on the status of the files.
Rest all the steps will remain the same; all you need to do is to apply it only to cell D3 to D12.
The above sequence of formula will return the following output where only the cells column D is shaded based on the values of column F.
Conditional Formatting is an amazing Excel tool that quickly helps to highlight cells on the basis of any criteria that you want to apply. We have already covered some interesting formulas in this tutorial. Go and give it a try today!