Conditional formatting is a way to distinguish cells in Google Sheets by shading them a different color if they meet specific criteria. Setting up the formatting isn’t much different than in Microsoft Excel. If you’ve switched from Microsoft Office or just prefer using Google Docs, you’ll be happy to know you can format your cells based on a wide variety of criteria.

Getting Started

Open your spreadsheet in Google Sheets. I recommend creating a backup until you get the hang of the formatting. It’s easier to go back to the original than try to undo all the changes you’ve made.

Access conditional formatting by selecting the Format menu and choosing Conditional Formatting.

Formatting Menu

 

 

You’ll then be able to select your cell range and criteria.

Setting Up Conditional Formatting

On the right side of the spreadsheet, you’ll see the conditional formatting window. Before selecting any criteria, choose either Single color or Color scale. The first option colors your cells a single color. For instance, if you wanted all cells between 50 and 70 to be formatted, you could make all those cells turn red.

If you choose color scale, you’re able to choose a range of colors for a gradient affect. This works well if you want the color to gradually change as the values of a cell increase or decrease.

Color Modes

 

Once you’ve chosen your color mode, you’re ready to set up the rest of your conditions.

Click inside the Apply to range text box and either enter your cell range or highlight the cell range on your spreadsheet. Only select the cells you want to be formatted. You can still base your conditions off of other cells.

If you’re highlighting a range, press OK in the What Data? box to approve your selected range.

Range Select

 

 

Select your condition using the Format cells if drop down menu. If you have a custom condition in mind, choose Custom Formula is at the bottom of the list.

Conditions

 

 

Enter your criteria in the Value or Formula box that appears directly  under your chosen condition. If you’re doing a custom formula, enter it in this box.

You’ll see a preview of how cells will be formatted based on what you enter. This gives you an easy way to tweak the formula or condition to get it just right.

Set Formula

 

Next, select your formatting style. Basically, you can choose from either a colored cell or colored text.

Formatting Style

 

If you don’t like the default colors, click the paint can icon and choose your own color. If you’re only changing the color of the text, select the text color icon and choose your color.

Select Color

 

If you’re using the Color scale mode, you’ll need to select a color for your min and max values.

Click Done when you’re finished customizing your conditional formatting to create the rule and apply it to your sheet.

Save Rule

 

Whenever you need to change, delete or add a new rule, open the Conditional Formatting menu and click Add New Rule. If you highlight the same range of cells or any cells already affected by conditional formatting, you’ll see the existing rules listed for you to edit or delete.

All changes and formatting are saved moments after setting them. Play around with the different conditions to see how each affects your data. Remember, conditional formatting can be set on text, numbers and even blank cells.

~Crystal