How To Do Conditional Formatting In Google Sheets?

Conditional formatting may 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 formulation 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 could be particularly helpful for you. Stick round to study more about how to make use of this characteristic and customise it based on your wants in Google Sheets.

How to Do Conditional Formatting in Google Sheets?

There are three steps you could observe to use conditional formatting in Google Sheets.

Step 1: Open the Conditional Formatting Window

You have to 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 may select between setting a formulation for conditional formatting. However, it’s also possible to use a customized formulation as a situation to format the referred cells.

1. In the Single shade tab, verify the vary below 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 formulation within the Value or formulation part.

Step 3: Apply Formatting

There are two forms of formatting you may apply to your cells in your cells. You can both select to use a strong shade or modify the colour based on the hierarchy of your worth.

For instance, in case you’re calculating breakeven evaluation, you may both select to format the cells with revenue with the identical shade of inexperienced. In distinction, you might select to signify 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 selection if you wish to Strikethrough your cell content material.
   • A: Choose the colour you wish to signify the formatted cell in.
   • Fill shade icon: Pick a shade 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 is just not the Min worth/Max worth, enter your knowledge next to the empty field.
5. Select the fill icon below every part to assign a shade. Sheets will generate a gradient below Preview.
   

4. Click Done.

How to Create a Custom Formula in Conditional Formatting?

You can use the built-in capabilities Google Sheets gives whereas customizing a formulation for a conditional format.

You can add further parts like wildcards and absolute cell references in your customized formulation whereas using conditional formatting in Google Sheets.

Wildcard

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

•  Asterisk: The placement of an asterisk can signify 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 parts in your formulation. For instance, “T?m” could return each, “Tim” and “Tom”.
• Tilde: Tilde is used to deal with the asterisk and query as regular parts 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 check with a cell location or a spread as absolute. This means whenever 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 routinely change the formulation.