As has already been said repeatedly, Microsoft Office Exel has very diverse functions that can help us to carry out our work in a faster way and without many complications. But this time we are going to teach them to use a format and not a function to learn, how to put or change the color of a row according to the text or value of a cell in Excel.
How to Set or Change the Color of a Row based on the Text or Value of a Cell in Excel
Many times you have asked yourself this question, about how to change the color of a cell and what function Excel has to solve it. But unfortunately the functions do not change the appearances of the cells, they change the data that those cells contain. That is why in this case we will use a format to solve it.
Yes, as you can see, we are going to use the conditional format that is present within the Excel options. And this will help us in a simple way to solve this problem, if you realize a format will allow us to change the appearance of our spreadsheet. Unlike what it would do if we used a function.
If we have a table composed, for example, of 6 columns and 20 rows, we could use a function to freeze the rows and columns . But you could not find a function that allows us to set or change the color of a row according to the text or value of a cell in Excel. Next we will explain how to apply this format in your data table.
How to change the color of a row based on the text or value of a cell
To carry out this explanation, you must have your Excel sheet ready with a table that contains several columns, for example five. And with several rows for example 10, this data can contain any information, the important thing is that you learn to change the appearance of cells using conditional formatting .
Continuing with the example, suppose that our table shows us six columns with the following data: Product, quantity, value, total, condition. Where the condition will be if the product has been canceled or not. And the rows represent the 10 products that are marketed in the business.
So we want to color the rows containing the products that have not been canceled with some color . To do this in a non-manual way, we must make use of the conditional format. We carry out this operation in the following way, we select the whole table and then we go to the Home tab, to change the color of a row in Excel .
Conditional format
In this tab we are going to look for the Conditional Formatting option , we make a clip and other options appear and we are going to choose New rule. When you clip, a window will appear with the name New Formatting Rule. In it there are several options but we are going to choose “Use a formula that determines the cells to apply formatting.
When choosing this option, we must then write the formula that we are going to use, the formula would be the following = $ E4 = “Not Canceled”. This formula means that all the cells that are in column E, which is the condition column, that is, if a product has been canceled or not. From the first cell or E4 to the last and only those with this message Not Canceled will have a color.
Then you have to press the format button, here you can select the color you want the cells with the message Not Canceled to have. You can also change the appearance, add an effect, change the borders, choose patterns, and more. Then you must accept everything and your box will present the color you chose, in the cells that have the message of Not Canceled.
And this same format can be applied to the rest of the table but now applying the formula to the cells that present the Canceled message. Where, for this message they are only going to change the color. And in a very easy way you learned, how to put or change the color of a row according to the text or value of a cell in Excel.
