Application of Excel conditional format formula in four cases

We know that the Excel conditional formatting feature can selectively apply formatting automatically based on cell content, and it adds a lot of convenience to excel. If you make conditional formatting and formulas work together, you can play a more powerful role, and here are some examples of how to use formulas in conditional formatting, hoping to bring some inspiration to your readers.

First, discriminant input is correct

When you enter a number with fixed digits, such as an ID card, where the number of digits is incorrect, we want Excel to give a hint. Although you can use the data validation settings, but when the input error, Excel will always pop up a prompt dialog box, a friend may feel this "abrupt" reminder a little influence mood, let "conditional format" to "moderate" reminder it.

1, create a "conditional format" formula

Let's say that by "conditional formatting," the fill color of the cell that matches the number (15-bit or 18-bit) is set to green, and when the input is complete, the input is correct by looking at whether the cell's fill color changes to green.

Because the ID number data belongs to the "text" type, first select the A2:a52 cell area where you want to store your ID number, and set the number format to "text." Then, when the A2:a52 cell range is selected, select the menu format → conditional format command, open the Conditional Formatting dialog box, click the drop-down arrow under condition 1, and select formula in the pop-up Drop-down list (Figure 1).

Then enter the formula "=or (A2) =15,len (A2) =18)" In the text box to the right of it. Then click the Format button, select the Pattern tab in the Open Cell Format dialog box, and choose green as the fill color for the eligible cells (Figure 2).

When you are set up, click OK to return to the Conditional Formatting dialog box, check the error and click OK again to complete the conditional formatting (Figure 3).

Tip: The above operation, first select a range of cells a2:a52, and then for the range of cells to set the formula for conditional formatting. In this case, the formula should use a reference to the upper-left cell in the selection range, in this case A2. When the formula input is complete, you can look at the conditional formatting formulas for other cells in this range, such as A8 cells, =or (LEN A8) =15,len (A8) =18), because the above reference is relative, and it automatically changes based on the actual offset of the cell. And get the formula that fits the other cells.

2, the implementation of the specific effects

Now to test what the above settings can achieve, enter some ID number in the cell in the A2:a52 area, when the number of digits is 18-bit or 15-bit, the fill color of the cell is automatically "green" and the ID number is not the correct number, and the fill color of the cell is not changed (Figure 4), We can judge the correctness of the input from whether we change color.

If you need to delete a cell conditional format after you have entered all and confirmed correctly, select the A2:a52 cell range, and then open the Conditional Formatting dialog box, click the Delete button in Figure 3, check the condition 1 check box in the Open Delete Conditional Formatting dialog box, and click OK (Figure 5).

　　Second, find the top three sales

as shown in Figure 6, the B2:b12 cell contains the sales data, to find out the top three, and to display them in blue font.

Select B2:b12 Cell, open the dialog shown in Figure 1, enter the formula "=b2>large ($B $: $B $12,4)", and then set the qualifying font format to blue. The specific effects of the implementation are shown below (Figure 6).

Note: Although the "Sales" data column can be sorted to find the top three, however, perhaps we want to arrange the date in order, then "conditional format" can do "the best of both worlds."

Third, to meet the special conditions of the date highlighted

Sometimes, we might want to highlight the cell that matches the date of the special condition, such as Saturday or Sunday. We can then select the cell that contains the date, like the a2:a12 in Figure 6, and then open the cell as shown in Figure 1, enter the formula =or (Weekday (a2,2) =6,weekday (a2,2) =7), and then set the qualifying cell fill color as a shadow.

Tip: Function Weekday (serial_number,return_type) function to return a date for the day of the week, Serial_number?? Represents a sequential sequence number that represents the date of the day to look for. When the parameter return_type is 2 o'clock, the function returns an integer between the number 1 (Monday) and the number 7 (Sunday).

Four, let the worksheet interval fixed row display shadow

When there are more rows of cell data, we can make the display more eye-catching, the worksheet interval fixed rows to display shadows, the effect is shown in the following figure (Figure 7).

The effect above is to use the formula "=mod (Row (), 2) =0", and if you want two rows to display the shadow, use the formula "=mod (Row (), 3) =0", and so on.

　　tip: function MOD (number,divisor) returns the remainder of Division Two, where number is dividend and divisor is divisor. function row (reference) returns the line number of the reference. Which reference?? is the cell or range of cells that you want to get the row number for, and if you omit reference, the reference to the cell that contains the function row is assumed.

"Conditional format" and the combination of formula there are a lot of applications, interested friends may wish to study again OH.