Conditional Formatting alters the appearance of a cell or range of cells based on specified criteria or conditions in Google Sheets or Excel. In Google Sheets, Conditional Formatting can be applied as a single color, color scale, or through custom formulas. Microsoft Excel offers various types of conditional formatting rules, providing multiple options to highlight your data effectively.
ℹ️ To learn more about Google Sheet's Conditional Formatting feature, click here.
ℹ️ To learn more about Microsoft's Excel Conditional Formatting feature, click here.
Conditional Formatting for Google Sheets
Conditional Formatting for Microsoft Excel
Conditional Formatting for Google Sheets
Single Color
Single Color - Conditional Formatting evaluates each individual cell in a specific range, and if that cell meets the criteria, the formatting style is applied to it.
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 "Format" and select "Conditional Formatting".
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.
Example: The range selected below is (D3:D15), and the "Closed Lost" color is red with white text. (This can be repeated for each value in your column with a different color/text color.)
ℹ️ Pro Tip: Use Absolute Reference to "lock" the range you want the Conditional Formatting to apply to. For example, $F$3:$F$402. Using Absolute Reference ensures that any new rows or columns added to your sheet won't have the Conditional Formatting Rules applied, unlike Relative Reference (F3:F402).
Color Scale
Color Scale - Conditional Formatting evaluates a range of cells and applies a color gradient based on your assigned Max and Min values. (You can also assign a Midpoint if desired - optional)
Example: A user wants to apply Conditional Formatting (Color Scale) to the Amount column (column F) in their Salesforce Opportunities import.
1. From the Google Sheets menu bar, click "Format" and select "Conditional Formatting". Next, choose "Color Scale" from the available options.
2. Designate the Range (F3:F28), the Color Scale format, and the values for, “Minpoint”, “Midpoint”, and “Maxpoint”. Then select "Done".
3. The Color Scale is now visible, displaying the minimum value with the lightest color and gradually increasing to the maximum with the darkest color. (We sorted the values for easier visualization below.)
Custom Formula
A Custom Formula lets you apply Conditional Formatting to one or more cells based on the specific formula you enter.
Example: Highlight the entire row if the Opportunity Amount is less than (or equal to) $100,000.
1. From the Google Sheets menu bar, click "Format" and select "Conditional Formatting". Under the "Single color" tab, go to the “Format Rules” section and select “Custom formula is”.
2. Enter the range (A3:H28), then add the custom formula. Don't forget to add the color/styling.
Example: we will add, "=$F3 <= 100000".
Below you can see the rows with Opportunities less than or equal to $100,000.. 🙌🏻
Conditional Formatting for Microsoft Excel
Excel's conditional formatting feature enables you to dynamically format a cell or range of cells in your sheet by selecting a background or text type/color based on the rules you set. Excel offers various types of conditional formatting rules that function like if/then logic statements.
Highlight Cell Rules
"Highlight Cell Rules" lets you choose predefined criteria (such as Greater Than, Less Than, Equal To, Between, Text Contains, etc.) and format your cells accordingly.
Example: In your Salesforce opportunities import, you need to highlight all the opportunities under the Stage column that have a value of "Closed Won" with a green background.
1. From the Home tab on the ribbon, click the "Conditional Formatting" icon, choose "Highlight Cell Rules," and then select the "Text that contains..." option.
2. In the Conditional Formatting panel on the right side, choose or input the values listed below:
|
Apply to range: D:D (this will be the Stage column on your sheet). Rule Type (4th text box): Closed Won Format with: Green fill with dark green text |
3. After applying the conditional formatting, all cells within your chosen range will be highlighted in green.
Top/Bottom Rules
"Top/Bottom Rules" assist in identifying and highlighting the top or bottom performers within your selected range of cells. This feature is particularly useful for monitoring team performance or identifying top revenue clients.
Example: You've imported all your customer accounts and want to identify or highlight the top 10% of customers based on their "Annual Revenue."
1. Go to the Home tab on the ribbon, click the "Conditional Formatting" icon, select "Top/Bottom Rules," and then choose the "Top 10%" option.
2. In the Conditional Formatting panel on the right side, choose or input the values listed below:
|
Apply to range: D:D (this will be the Annual Revenue column on your sheet). Rule Type (Top/Bottom): Top 10% Format with: Green fill with dark green text |
3. After applying the conditional formatting, the top 10% of your customers based on Annual Revenue are highlighted in green.
Color Scales
The "Color Scales" rule type automatically applies a color system once you set the rule range and color type, eliminating the need to manually adjust formatting styles. The "Green - Yellow - Red Color Scale" is a popular option, assigning a green background to high values, a red one to low values, and shades of orange and yellow to intermediate values.
Example: You operate a Shopify store and wish to track your product inventory quantity using your Coefficient import and Excel's conditional formatting.
1. Highlight the Inventory Quantity column in your import, then click the "Conditional Formatting" icon followed by "Color Scales". Choose the "Green-Yellow-Red Color Scale" option (usually the first one).
2. After applying the conditional formatting, the selected cells will be highlighted on a color scale based on their values. The highest value will be displayed in green, while intermediate values will receive shades of orange and yellow.