Conditional Formatting Depending on the Condition of a Column

Conditional formatting adds in the analysis of data from a spreadsheet. To apply a conditional formatting rule on the same cells, where the condition is applied is relatively simple.

Fig. 1

If we want to color the entire row, depending on the condition that occurs in the cell in a column, things get a little (not exaggerated) more difficult.

Fig. 2

Let suppose that if the result of the exercise (from column O) in figure 1 is greater than 7000, the whole line will be colored a kind of blue. To do this, we will select the entire range (A5:O24), and then navigate to the Home menu, option Conditional FormattingNew Rule (Figure 2). In that form we choose the latter type of rule (Use a formula to determine which cells to format). In the textbox that appears, we will write =$O5>=7000.

Fig. 3

It is important to note that we talk about a mixed address, the dollar symbol being applied only to column O, where the condition is applied, but we will leave relatively the number indicating the row, so that the coloring is done according to the condition applied on that row. We choose by pressing the Format button, the formatting type, then press Ok to close the Format Cells form, and press again Ok to close the rule edit form (Figure 3). The result is the one shown in Figure 4.

Fig. 4

If we want another threshold – for example, to color a shade of orange – we can follow the same steps, but introducing the formula =$O5>=10000.

Fig. 5

Also, with the A5:O24 range selected, we can choose from the Home menu, the option Conditional FormattingManage Rules (already see the old formula).

We press New Rule button (Figure 5), where we follow the previous steps. After choosing the format, returning manager conditional formatting rules, we find that if you press the Apply button, to apply the rule already created, as shown in figure 6.

Fig. 6

Author: Ovidiu.S

I am quite passionate about this professional area as if I know something - no matter how little - I want to share it with others.

Leave a Reply

Your email address will not be published.