Use formula to set the conditional formatting

Uploaded time: October 28, 2021 Difficulty Intermediate

A free Office suite fully compatible with Microsoft Office

Free Download
Free download

Use formula to set the conditional formatting

Use formula to set the conditional formatting

Take this table as an example. We can use the formula to set the conditional formatting by ourselves and mark the finished projects.

Select the cell range A2:C22, and get into the Home tab. Click the Conditional Formatting drop-down button, and choose the New Rule option. In the pop-up dialog, we choose the Use a formula to determine which cells to format option.

In the Format only cells with edit box, enter the formula =$C2=Finished . The absolute reference has been added to the column label to prevent the data in column C from moving. The row number does not add an absolute reference so that it will change into $C2, $C3, $C4 with the selected data area.

Then, we click the Format button to open the Format Cells dialog, switch to the Patterns option, select a favorite fill color, and click OK to complete the settings. In this way, when the cell content of column C is equal to Finished, the conditional formatting we've just set will be applied.

If we want to clear conditional formatting, we only need to choose Clear Rules under Conditional Formatting, and click Clear Rules from Entire Sheet.

Did you get it?

To be office excel advanced, you could learn how to use WPS Office Spreadsheet online in WPS Academy.