How often happened that you wanted to color a cell in excel in color depending on whether it had more than one value and another color if you had another value?
Ever wanted to know immediately if a value is good or bad to only update a spreadsheet?
This trick of Microsoft Excel is your solution!
To color a cell based on its value and give it any color you want, and this update automatically, just follow these steps:
1) need to have an excel spreadsheet, you can be the complication like, for this example take a very simple:
2) Secondly you have to select the cells that are going to implement the automatic coloring, if you have a template to grow indefinitely, you can choose to select all column. For this example select the entire column.
column finite selection
Select the entire column
3) Then you have to go to the menu F ormat -> For m ato Conditional
4) will open a new window that will allow you two options, " Cell Value" or " Formula"
cell value allows you to select a range of values \u200b\u200bfrom which will be a cell, but in case you want to have a condition that depends on other cells, for example, the difference between two cells, it is good to have on hand the ability to add formulas of complexity we want:
5) Take the case of a formula, we use the formula IF is a conditional, the format is super simple: = IF (condition, return value ok, ok no return value)
Where condition is an equation, for example say that if cell B1 is greater than five (5), then returns TRUE, it returns FALSE.
The equation is as follows:
= IF (B1> 5, TRUE, FALSE)
As can work something? is simple, putting B1, which in the example case is the first cell selected, Excel automatically changes the function for each cell, such as B7 cell will have an equation:
= IF (B7> 5; TRUE , FALSE)
If you had selected only cells with values:
would have seen a different behavior as you to place:
Putting B1
As you can see, there are values \u200b\u200bless than five (5) marked!, This is because if you put your selection B1 begins at B2, B2 will have the formula:
Do you understand?
6) Finally, 'll be able to add up to three different conditions for three colors and different formulas, in this example, if the value of B is between 5 and 10, it turns yellow, if greater than 10, green.
Notice how the formula changes that do not step up to the bottom (because 10 is greater than 5 and meets the first formula ever)
The bottom line is this:
" And if I apply the column format for all the road as I do?
Simple! Excel takes the names of cells with a symbol "$" before them as immutable, that is, if you got the role
a) Select the entire book b) The formula write
Any question the answer from the comments!
If you had selected only cells with values:
would have seen a different behavior as you to place:
Putting B1
As you can see, there are values \u200b\u200bless than five (5) marked!, This is because if you put your selection B1 begins at B2, B2 will have the formula:
= IF (B1> 5, TRUE, FALSE)
and B7 have the formula: = IF (B8> 5; TRUE , FALSE)
Do you understand?
6) Finally, 'll be able to add up to three different conditions for three colors and different formulas, in this example, if the value of B is between 5 and 10, it turns yellow, if greater than 10, green.
Notice how the formula changes that do not step up to the bottom (because 10 is greater than 5 and meets the first formula ever)
The bottom line is this:
" And if I apply the column format for all the road as I do?
Simple! Excel takes the names of cells with a symbol "$" before them as immutable, that is, if you got the role
= IF ($ B $ 8> 5, TRUE, FALSE)
All selected sheets will use the same value, therefore, if you want to mark the return of the whole line such as yellow if B is greater than 5, you have to: a) Select the entire book b) The formula write
= IF ($ B1> 5, TRUE, FALSE)
$ B With what you say is "always looking at column B to values, not using neither A nor any other, no matter which column you are) Any question the answer from the comments!
