How To Do Conditional Formatting In Google Sheets?

Conditional formatting could be very helpful in making a cell stand out from the grid. You can use conditional formatting by setting Format Rules within the Google Sheets spreadsheet. If the method applies to a cell, Google Sheets applies the set formatting. You can add a number of guidelines in the identical worksheet in Google Sheets.

If you’re coping with knowledge like monetary statements or mark sheets, conditional formatting will be particularly useful for you. Stick round to study more about how to make use of this function and customise it in keeping with your wants in Google Sheets.

How to Do Conditional Formatting in Google Sheets?

There are three steps you need to comply with to use conditional formatting in Google Sheets.

Step 1: Open the Conditional Formatting Window

You must open the conditional formatting window from the menu bar to set a rule and format.

1. Open Google Sheets in your browser.
2. Select the vary you wish to apply the format.
3. Head to Format from the menubar.
4. Choose Conditional formatting.

Step 2: Set Formula

Google Sheets has a set of library choices you possibly can select between setting a method for conditional formatting. However, you too can use a customized method as a situation to format the referred cells.

1. In the Single coloration tab, test the vary underneath Apply to vary.
2. Under Format guidelines, choose the fly-out menu for Format cells if.
   • Choose one of many library formulation.
   • Else, click on Custom Formula is then, enter your method within the Value or method part.

Step 3: Apply Formatting

There are two varieties of formatting you possibly can apply to your cells in your cells. You can both select to use a strong coloration or regulate the colour in keeping with the hierarchy of your worth.

For instance, for those who’re calculating breakeven evaluation, you possibly can both select to format the cells with revenue with the identical shade of inexperienced. In distinction, you possibly can select to characterize a high-margin revenue with a darker shade of inexperienced and a low margin with a lighter one.

Solid Color

1. In the Single Color tab, find the Formatting Style part.
2. Select Default and select one of many codecs or Custom format.
3. If you wish to customise your format, use the formatting choices beneath Default:
   • B/I/U: These choices apply the Bold, Italic and Underline format.
   • S: Select this feature if you wish to Strikethrough your cell content material.
   • A: Choose the colour you wish to characterize the formatted cell in.
   • Fill coloration icon: Pick a coloration you wish to fill the cell in.
4. Click Done.

Gradient

1. Head to the Color Scale tab.
2. Under Format Rules, choose Default and select one of many choices.
3. If you want to customise your codecs, choose your knowledge sort Minpoint, Midpoint, and Maxpoint.
4. If your worth isn’t the Min worth/Max worth, enter your knowledge next to the empty field.
5. Select the fill icon underneath every part to assign a coloration. Sheets will generate a gradient underneath Preview.

4. Click Done.

How to Create a Custom Formula in Conditional Formatting?

You can use the built-in features Google Sheets presents whereas customizing a method for a conditional format.

You can add extra components like wildcards and absolute cell references in your customized method whereas using conditional formatting in Google Sheets.

Wildcard

Google Sheets helps the asterisk, query, and tilde wildcards whereas getting into a customized method in conditional formatting.

•  Asterisk: The placement of an asterisk can characterize any worth earlier than or after your entered worth. For instance, “Tom*” may return each “Tomato” and “Tommy”. Similarly, “*ly” could return “beautifully” and “lovely”.
• Question: You can use the query mark to fill within the lacking components in your method. For instance, “T?m” could return each, “Tim” and “Tom”.
• Tilde: Tilde is used to deal with the asterisk and query as regular components and never as a wildcard. For instance, “~*ly” returns “*ly” and never “lovely”. Similarly, “T~?m” returns “T?m” and never “Tom”.

Absolute Cell Referencing

You can use the greenback signal ($) to discuss with a cell location or a variety as absolute. This means once you use the greenback signal earlier than a location, you lock it. When the cell content material is shifted to a different location, Sheets doesn’t robotically change the method.