Conditional formatting is very useful in making a cell stand out from the grid. You can use conditional formatting by setting Format Rules in the Google Sheets spreadsheet. If the formula applies to a cell, Google Sheets applies the set formatting. You can add multiple rules in the same worksheet in Google Sheets.
If you’re dealing with data like financial statements or mark sheets, conditional formatting can be especially handy for you. Stick around to learn more about how to use this feature and customize it according to your needs in Google Sheets.
How to Do Conditional Formatting in Google Sheets?
There are three steps you must follow to apply conditional formatting in Google Sheets.
Step 1: Open the Conditional Formatting Window
You need to open the conditional formatting window from the menu bar to set a rule and format.
- Open Google Sheets on your browser.
- Select the range you want to apply the format.
- Head to Format from the menubar.
- Choose Conditional formatting.
Step 2: Set Formula
Google Sheets has a set of library options you can choose between setting a formula for conditional formatting. However, you can also use a custom formula as a condition to format the referred cells.
- In the Single color tab, check the range under Apply to range.
- Under Format rules, select the fly-out menu for Format cells if.
- Choose one of the library formulas.
- Else, click Custom Formula is then, enter your formula in the Value or formula section.
Step 3: Apply Formatting
There are two types of formatting you can apply to your cells in your cells. You can either choose to apply a solid color or adjust the color according to the hierarchy of your value.
For example, if you’re calculating breakeven analysis, you can either choose to format the cells with profit with the same shade of green. In contrast, you could choose to represent a high-margin profit with a darker shade of green and a low margin with a lighter one.
Solid Color
- In the Single Color tab, locate the Formatting Style section.
- Select Default and choose one of the formats or Custom format.
- If you want to customize your format, use the formatting options below Default:
- B/I/U: These options apply the Bold, Italic and Underline format.
S: Select this option if you want to Strikethrough your cell content.- A: Choose the color you want to represent the formatted cell in.
- Fill color icon: Pick a color you want to fill the cell in.
- Click Done.
Gradient
- Head to the Color Scale tab.
- Under Format Rules, select Default and choose one of the options.
- If you wish to customize your formats, select your data type Minpoint, Midpoint, and Maxpoint.
- If your value is not the Min value/Max value, enter your data next to the empty box.
- Select the fill icon under each section to assign a color. Sheets will generate a gradient under Preview.
- Click Done.
How to Create a Custom Formula in Conditional Formatting?
You can use the built-in functions Google Sheets offers while customizing a formula for a conditional format.
You can add additional elements like wildcards and absolute cell references in your custom formula while using conditional formatting in Google Sheets.
Wildcard
Google Sheets supports the asterisk, question, and tilde wildcards while entering a custom formula in conditional formatting.
- Asterisk: The placement of an asterisk can represent any value before or after your entered value. For example, “Tom*” might return both “Tomato” and “Tommy”. Similarly, “*ly” may return “beautifully” and “lovely”.
- Question: You can use the question mark to fill in the missing elements in your formula. For example, “T?m” may return both, “Tim” and “Tom”.
- Tilde: Tilde is used to address the asterisk and question as normal elements and not as a wildcard. For example, “~*ly” returns “*ly” and not “lovely”. Similarly, “T~?m” returns “T?m” and not “Tom”.
Absolute Cell Referencing
You can use the dollar sign ($) to refer to a cell location or a range as absolute. This means when you use the dollar sign before a location, you lock it. When the cell content is shifted to another location, Sheets does not automatically change the formula.
