Conditional Formatting

Section header image mobile

Conditional Formatting changes the appearance of a cell/range of cells based on criteria or conditions that are specified in Google Sheets (or Excel). Conditional Formatting can be a single color, color scale, or even a custom formula.

ℹ️ Click here for more information on Conditional Formatting. 

How to use Conditional Formatting?

Single Color

Color Scale

Custom Formula

How to use Conditional Formatting?

Single Color

Single Color - Conditional Formatting evaluates each individual cell in a specific range and if that cell meets the criteria, then the formatting style is applied to it. (Example: Highlight all the cells in Column H that have the value “PQL” → All the “PQL” values are highlighted).

Example: A user has pulled an import from Salesforce of her Opportunities. She wants to use Conditional Formatting to color-code all the “Closed Lost” values red in Column D.

1. From the Google Sheets menu bar, click on "Format" from the tabs, and select "Conditional Formatting".ConditionalFormattingSelection.png

2. The “Conditional format rules” allow you to set up and customize your Conditional Formatting. You can select the Type (Single Color or Color Scale), the Range, and the Format rules. In the example below, I have it set to look at a specific range (D3:D15) and color all the “Closed Lost” Opportunities red with white text. (Make sure you click “Done” or else your customizations will not save).SingleColorFormatting.png

3. You can also create/add a Conditional Formatting rule for each value or scenario in your sheet as shown below.SingleColorFormatting2.png

ℹ️ Pro Tip: Use Absolute Reference to essentially “lock” the range you want the Conditional Formatting to apply to. Example: $F3:$F402. Using Absolute Reference will ensure that any new rows/columns that are added to your sheet will not have the Conditional Formatting Rules applied (as opposed to using Relative Reference F3:F402).

Color Scale

Color Scale - Conditional Formatting evaluates a range of cells and applies a color gradient based on the Max and Min values you assign. (You can also assign a Midpoint if desired - optional)

Example: A user would like to apply Conditional Formatting  (Color Scale) to his Salesforce Import based on the Amount (column F) in this Opportunities import with Coefficient. 

1. From the Google Sheets menu bar, click on "Format" from the tabs, and select "Conditional Formatting". Then select "Color Scale from the options presented.Screenshot 2023-10-03 at 8.27.10 PM.png

2. Designate the Range (F3:F28), the Color Scale format, and the values for, “Minpoint”, “Midpoint”, and “Maxpoint”. Select “Done” when customization is completeScreenshot 2023-10-03 at 8.31.58 PM.png

3. Now we can see the Color Scale showcasing the minimum value (the lightest color) and increasing gradually to the maximum value (the darkest color). (We sorted the values to make it easier to see below).Screenshot 2023-10-03 at 8.34.12 PM.png

Custom Formula

Custom Formula allows you to apply your Conditional Formatting to one or more cells based on the formula you entered.

Example: Highlight the entire row if the Opportunity Amount is less than (or equal to) a specific value ($100,000).

1. From the Google Sheets menu bar, click on "Format" and select "Conditional Formatting". Under the "Single color" tab, go to the “Format Rules” section and select “Custom formula is”ConditionalFormattingCustomFormula.png

2. Enter the range (A3:H28), then add the custom formula. In this case, we will add, =$F3 <= 100000. Don't forget to select a color for this formula! Now you can easily see what Opportunities are less than or equal to $100,000. 🙌🏻 ConditionalFormattingCustomFormulaResults.png

Was this article helpful? 0 out of 0 found this helpful