Function overview

Conditional formatting is used to apply some formatting to cells that meet certain criteria, so users can better view andanalyze desired data from lots of content. You can use conditional formatting in Sheets for various purposes such as:

•Team info organizing: Mark all team members under the age of 30.

•Project tracking: Quickly check which projects are under 50% completion.

•Sales performance tracking: Mark sales performance that fail to meet expectations.

•And more...

How to use conditional formatting?

Apply a new conditional formatting

1.Apply to range

There are two ways of selecting the applied range for a conditional fromatting:

Method 1:

•Step 1: Click the Conditional formatting button in the Sheets toolbar, then click the Select a data range button on the right of the input box.

•Step 2: Type your desired cell range (e.g., D2:D7) into the Add a range input box or directly select desired cells in a Sheet, then hit Confirm.

Method 2:

•Directly select desired cells in a Sheet, then click the Conditional formatting button in the toolbar. Selected cell range will be automatically shown in Apply to range input box.

2.Format rules

Set your desired rules in the Format rules input boxes. For example, if you'd like to highlight tasks that are under 50% completion. Just select Value - < Is less than - 50%.

3.Formatting style

You can set cell style in the Formatting style section. Style options are Bold, Italic, Underline, Strikethrough, Text color and Fill color. Choose your desired formatting style and see preview in the Preview bar.

4.Once all settings are determined, just hit the Done button in the bottom right corner to apply conditional formatting.

Check applied conditional formatting

To check an applied conditional formatting, just click on one cell with that formatting and click the Conditional formatting button in the toolbar. Applied conditional formatting will be shown on the right of the Sheet.

You can hover over a conditional formatting to see its applied range in the Sheet.

Adjust a conditional formatting

Click on one cell with the applied conditional formatting and click the Conditional formatting button in the toolbar. Click the desired formatting to change applied range, format rules and formatting style.

Delete a conditional formatting

To delete a conditional formatting, just open conditional formatting list, hover over the one you wish to delete and hit Delete.

FAQs

Q: Can I apply multiple conditional formattings to one particular range?

A: Of course. When multiple conditional formattings are applied, the latest applied one will be shown on the top.

Q: How many conditional formattings can I apply to one particular range?

A: You can apply up to 20 formatting rules and styles in each sheet.

👍 If this article has been helpful, please give us a thumbs up.