Conditional formatting is a powerful feature that allows you to automatically apply styles to cells, rows, and columns based on your data.

Similar to traditional spreadsheets, Spreadsheet.com supports conditional formatting, which allows you to automatically apply styles to cells based on data contained in your spreadsheet. Conditional formatting is a great way to automatically highlight or call attention to specific information in your workbook without having to manually style cells individually.

There are endless ways to use conditional formatting in your workbooks. Maybe you’re using a startup budget calculator to track cash flow and want to flag expenses over a certain dollar amount. Or maybe you’re using Spreadsheet.com to manage a project and want to automatically highlight a task once a user and due date has been assigned. Or maybe, as in the example below, you’re managing next year’s hiring budget and want to assign a color scale to a column with bonus payments and highlight positions with total compensation above a specified value.

This article features Spreadsheet.com's Applicant Tracking with Hiring Budget template. Explore the Template Gallery to find ready-to-use templates in over 20 different categories, or learn more about starting a new workbook from a Spreadsheet.com template.

Setting up Conditional Formatting

To configure your conditional formatting, select the swatch book icon   in the View toolbar to open the conditional formatting dialog.

The conditional formatting dialog allows you to create conditional formatting rules for any of the worksheets in your workbook. Select the intended worksheet from the dropdown in the top left corner and click the New rule button on the right hand side.

From the New rule dropdown, you can establish a new rule to format a row or a cell within a column, or to format an entire column. Let’s take a closer look at each option.

Formatting Rows and Cells

Conditional formatting allows you to format an entire row – or one column within a row – based on an established rule. To begin setting up your rule, select “If row satisfies condition format row/column” from the Choose a rule template dialog.

As you can see in the screenshot below, the condition and associated formatting is stated in the form of an “if” statement. When setting up your conditional formatting rule, there are three fields to edit:

Jump to: Condition (1)  |  Formatting location (2)  |  Formatting style (3)

Condition (1)

By clicking on “row satisfies condition,” you can establish the criteria on which the formatting will be based. This determines when the conditional formatting will be applied to your data.

To add a criteria to your condition, click the New condition button in the lower left hand corner of the popup dialog. As you can see in the screenshot below, the condition is stated in the form of a “where” statement.

In the first of the three fields here, you can choose the column on which to establish your condition. In the second and third fields, you can dictate what that condition is. Let’s take a look at a couple of examples from a hiring budget workbook.

In the condition below, conditional formatting will be applied where the total compensation for a position is greater than or equal to $150,000:

And in the condition below, conditional formatting will be applied where the compensation paid out for a position in 2022 will be greater than or equal to $75,000:

When you’re finished specifying your condition, click on Apply in the lower right corner to save it. Conditions may contain one or more criteria. If you apply multiple criteria, you can determine whether all criteria must be met or if only some criteria must be met by selecting “and” or “or,” respectively. In the example below, we are applying the conditional formatting when the total compensation for a position is greater than or equal to $150,000 and when the compensation paid out in the next fiscal year is greater than or equal to $75,000:

Formatting location (2)

By clicking on “row/column,” you can specify the location where the formatting will be applied. You can decide whether an entire row is automatically formatted, or a single column within the row. Click on row/column, and select the intended location from the dropdown menu:

For our example, we’ll choose to highlight the entire row if the condition is met by selecting Entire row from the top of the dropdown.

Formatting style (3)

By clicking on “Hello world!” you can specify the formatting style to be applied to your target location. Like the usual formatting options, you can apply styles to either the text or the cell, or select from a list of predefined styles by clicking the lightning bolt icon .

The style defined here will be applied to the formatting location when the condition criteria is met, as described above.

Once you’ve defined all three fields, your conditional formatting rule will be applied to your worksheet. Take a look at the image below to see how the rule defined above is applied to our hiring budget worksheet:

Notice how the rows that meet our criteria per our Condition (1) are highlighted as defined in Formatting location (2) and Formatting style (3).

Formatting Columns

Conditional formatting also allows you to apply a color scale to an entire column based on the values within the column. Unlike applying conditional formatting to a row or cell, which takes into account one or multiple criteria, conditional formatting applied to a column takes all of that column’s values into account.

To begin setting up your rule, select “Format a column as” from the Choose a rule template dialog.

When setting up your column formatting, there are two fields to edit:

Jump to: Formatting location (1)  |  Formatting color scale (2)

Formatting location (1)

By selecting “a column,” you can specify the location where the formatting will be applied. Click on a column, and select the intended column from the dropdown menu:

Formatting color scale (2)

Clicking on the color scale graphic in your conditional formatting rule allows you to change the appearance of the gradient applied to your column formatting.

You can select from a list of predefined styles by clicking on the color scale at the top of the dropdown dialog, or you can create your own by using the color selectors next to Min, Mid, and Max.

By default, the distribution of your color scale will set the minimum and maximum of the range as the minimum and maximum values of your column data, respectively, with the midpoint set as the median value. You can use the dropdowns next to Min, Mid, and Max to change the type of data used to establish your color scale distribution and the fields next to these boxes to manually input the numbers themselves.

In the example below, we are formatting the “Bonus” column as a green-yellow-red color scale, with the minimum, maximum, and midpoint values set as the 25th, 75th, and 50th percentiles, respectively:

Once you’ve defined both fields, your conditional formatting rule will be applied to your worksheet. Take a look at the image below to see how the rule defined above is applied to our hiring budget worksheet:

Notice how the color scale defined in Formatting color scale (2) is being applied to the “Bonus” column as specified in Formatting location (1). As you continue to add rows to your worksheet, your conditional formatting rule will take into account the new values and automatically adjust the color scale appropriately.

Managing Your Conditional Formatting Rules

Ordering Rules

In some cases where you create more than one conditional formatting rule, multiple rules may be affecting the same cell. The order in which rules are applied is determined by the order in which they appear in the Conditional formatting rules dialog; rules that are higher in the list take priority over those that are lower in the list.

You can change the order of your conditional formatting rules by dragging and dropping them in the list with the handle at the left side of the rule:

In the animation above, notice how the order of the rules affects the way in which they are applied. When our column formatting rule is at the top of the list, it takes priority and overrides the row formatting rule. When the row formatting rule is at the top of the list, it takes priority and overrides the column formatting rule.

Activating and Deactivating Rules

The checkbox on the right hand side of your conditional formatting rule allows you to toggle it on or off.

When the box is unchecked, the conditional formatting will be removed from your spreadsheet, but the rule will remain in the conditional formatting dialog if you need to reactivate it later:

Deleting and Duplicating Rules

The menu on the right hand side of your conditional formatting rule allows you to either delete or duplicate your rule.