¿Quieres cambiar el color de las celdas en Excel según el valor numérico que contienen? En este tutorial aprenderás paso a paso a pintar celdas en Excel automáticamente con formato condicional, para que se resalten automáticamente aquellos valores que cumplan tus criterios (mayores, menores, entre rangos, etc.). Te mostraré cómo aplicar distintas reglas y colorear las celdas de forma personalizada, facilitando el análisis y la lectura de tus datos.
Categoría
📚
AprendizajeTranscripción
00:00In this video, I show you how to change the color of a cell depending on the numeric value or number
00:04it contains. I'll show you the simplest way possible, and at the end of the video, I'll explain
00:10why it doesn't work for many users and the color changes incorrectly. I want the cell color to
00:15change based on the stock quantity, so I select the entire range of cells to which I want to apply
00:20conditional formatting in the home tab, conditional formatting, rules to highlight cells, and I must
00:26use these two rules, is greater than and is less than. When the stock is greater than 9, that is, 10 or
00:34greater, I want to apply a green color. So, I'll use is greater than as the format. I'm going to apply
00:41it to cells greater than 9, which does not include 9, but from 10 onwards. And here I choose any of the
00:49formats or I use a custom format. I'm going to use the green fill, dark green context. I
00:56accept and now I have a format applied. Again, with the cells selected, let's go to conditional formatting,
01:04rules for highlighting cells. And now I'm going to use is less than when it's less than 10, which
01:09doesn't include 10. In that case, I wanted to use a yellow fill, which would be this one.
01:16I accept and there you have it. And finally, I wanted to apply a red fill when it's zero. So with the cells
01:23selected, let's go to conditional formatting. And when it's equal to I want the fill to be red.
01:34As you can see, it works correctly, but depending on how you apply the rules, it can give you an error.
01:39And this would be the error. As you can see, now, even though the stock is zero, it continues to mark
01:45it in yellow. And what's the reason for this? Well, this is due to the order in which conditional
01:49formatting is applied. Let's select the range and click on conditional formatting. To see what order
01:54is being applied, let's go to the manage rules option. Here's the list of the rules we've created.
01:59They're applied from top to bottom, that is, first, Excel verifies that this rule is met,
02:04then the second, and finally the third. What happens? That when it evaluates the value zero zero,
02:10it is effectively a value less than 10. So it applies the yellow color and does not evaluate the
02:16next rule, which is the cell value equal to zero. That is why it is not working correctly. To fix it,
02:22select the rule and with these two arrows you have to move it up or down. In this case, up.
02:28Keep in mind that when you apply conditional formatting to numeric values, you have to apply
02:33them in an orderly fashion. For example, if you were to apply it to ranges of grades to put outstanding,
02:38notable, good, etc., you would have to apply it from highest to lowest or from lowest to highest.
02:44Tell me in the comments if you have ever had the problem that the order of the conditions had
02:48affected the color of the cells. If you found it useful, give it a like and if you want to see
02:53more videos like this, subscribe to our channel and don't forget to activate the notifications in
02:57the bell so you don't miss anything. See you in the next video.