Tips for conditional formatting in Excel cells

For the different data in the Excel table, we can set the format of its display according to different conditions and requirements, so that the different data is displayed more prominently, which is the application of conditional formatting in Excel cells. That is, we can format cells differently based on the different conditions that the data in the cell satisfies. So, what if we want the format of a cell to be set according to the conditions that other cell data satisfies, can it be implemented? Of course, in Excel 2007, conditional formatting can also make this hope a reality.

The exam number is in column A, the total score is in G, the graduation school is in H, the grades are distributed in columns C to f, and now we format the corresponding cells according to the specified conditions.

One, according to a single condition set

Now we want to fill in the color with the exam numbers for all the students in the Graduate school, and set the font bold. Of course, can not be sorted and other operations.

To select the A2 cell, click the small triangle under the Conditional Formatting button in the Style feature group on the Start tab of the Ribbon, click New Rule in the pop-up menu, and open the New Formatting Rule dialog box.

Select the "Use formulas to determine which cells to format" item in the Select Rule Type list. Enter the formula in the input box below "format values for this formula" = $H 2 = "Industrial Professional" (note: does not include outside double quotes). Then click the Format button in the lower-right corner of the dialog box to open the Format Cells dialog box.

In the newly opened dialog box, click the Font tab and set Glyph to bold. Click on the "Fill" tab, click the color you want in the color list, and make sure that you close all the dialog boxes.

Finally, select the A2 cell, click the Format Painter command button in the Clipboard feature group on the Ribbon's Start tab, and then brush all cells below A3 to the last line. As you can see now, all of the "Graduation schools" for the "Industrial professionals" column A cells are implemented in the specified format.

Second, according to the multiple conditions set

Sometimes the conditions used to format them are more complex and may have to be set according to multiple criteria. For example, set the "Graduation School" for "industrial Professional", g column "Total score" is greater than or equal to 580 of the test number in the cell fill color and set the font bold. So how do you set it?

Instead of using the previous method, enter the formula in the Formula entry box in the New Formatting Rule dialog box "=and ($H 2 =" Industrial Professional ", $G 2>580)" (Note: Do not include outside double quotes), and then set the appropriate format. Do not forget to use the format to brush the list of the other cells in the column, all OK.