Tech News Today
  • Hardware
    • Motherboards
    • CPUs
    • Graphic Cards
    • RAM
    • SSDs
    • Computer Cases
    • Monitors
    • Peripherals
    • Power Supply Unit
    • PC Builds
    • Computer Tips
  • Software
  • Operating System
    • Windows
    • Mac
    • Linux
  • Gaming
  • Mobile
  • Console
  • More
    • Internet
    • Networking
    • Security
    • Buyer’s Guide
    • Gadgets
    • Laptops
    • Reviews
    • How To
    • News
Facebook Twitter Instagram
Tech News Today
  • Hardware
    • Motherboards
    • CPUs
    • Graphic Cards
    • RAM
    • SSDs
    • Computer Cases
    • Monitors
    • Peripherals
    • Power Supply Unit
    • PC Builds
    • Computer Tips
  • Software
  • Operating System
    • Windows
    • Mac
    • Linux
  • Gaming
  • Mobile
  • Console
  • More
    • Internet
    • Networking
    • Security
    • Buyer’s Guide
    • Gadgets
    • Laptops
    • Reviews
    • How To
    • News
Tech News Today
Home»Software»Excel»How to Do Conditional Formatting in Google Sheets?

How to Do Conditional Formatting in Google Sheets?

AsmiBy AsmiDecember 23, 2022
How to use conditional formatting

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.

Table of Contents

  • How to Do Conditional Formatting in Google Sheets?
    • Step 1: Open the Conditional Formatting Window
    • Step 2: Set Formula
    • Step 3: Apply Formatting
  • How to Create a Custom Formula in Conditional Formatting?
    • Wildcard
    • Absolute Cell Referencing

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.

  1. Open Google Sheets on your browser.
  2. Select the range you want to apply the format.
    Sheets cell range
  3. Head to Format from the menubar.
  4. Choose Conditional formatting.
    conditional formatting feature

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.

  1. In the Single color tab, check the range under Apply to range.
    Apply to range conditional formatting
  2. 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.custom conditional formatting formula

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

  1. In the Single Color tab, locate the Formatting Style section.
  2. Select Default and choose one of the formats or Custom format.
    Default Formatting style
  3. 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.Formatting features Single color Sheets
  4. Click Done.

Gradient

  1. Head to the Color Scale tab.
  2. Under Format Rules, select Default and choose one of the options.
    Default Formatting style
  3. If you wish to customize your formats, select your data type Minpoint, Midpoint, and Maxpoint.
    Set point conditional formatting rules
  4. If your value is not the Min value/Max value, enter your data next to the empty box.Set value conditional formatting
  5. Select the fill icon under each section to assign a color. Sheets will generate a gradient under Preview.
    Select color for color scale conditional formatting
  1. Click Done.

How to Create a Custom Formula in Conditional Formatting?

absolute cell referencing

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.

how-to
Asmi

Hello! I'm Asmi, and I've been writing professionally since 2020. I'm here to help you learn your way around Microsoft Office-based programs. Besides Office, I also write on technical fixes and general how-to(s). Apart from writing, I like giving back to the community by volunteering.

Related Posts

how to find unique values in excel

How to Find Unique Values in Excel

January 15, 2023
how to find links in excel

How to Find and Edit External Links in Excel

January 13, 2023
reference isn't valid excel

Reference Isn’t Valid Excel Error – 6 Ways to Fix it

January 6, 2023
how to set print area in excel

How to Set Print Area in Excel

January 5, 2023
excel find function not working

Excel Find Function Not Working? Here’s How to Fix it

January 4, 2023
excel trim function not working

Excel Trim Function not Working? Try these Fixes

January 3, 2023
Add A Comment

Leave A Reply Cancel Reply

Latest Posts
what is ghost of tsushima legends

What is the Ghost of Tsushima Legends Mode

January 12, 2023
raid 5 vs raid 10

RAID 5 Vs RAID 10 – Which One Is Better?

January 12, 2023
best-tv-settings-for-gaming

Best TV Settings for Gaming

January 9, 2023
You may also like
tv-screen-goes-black-randomly

TV Screen Goes Black Randomly? Try These 11 Fixes

January 15, 2023
how to find unique values in excel

How to Find Unique Values in Excel

January 15, 2023
how-to-open-and-use-chrome-settings

How to Open and Use Chrome Settings

January 16, 2023
Recommended
Cookie Clicker Garden Guide

Cookie Clicker Garden Guide to Unlocking Every Seed

September 26, 2021
monitor no signal

Computer Turns On But Monitor Says No Signal (9 Ways To Fix)

November 10, 2022
Facebook Twitter Pinterest
  • Home
  • About Us
  • Editorial Guidelines
  • Fact-Checking Policy
  • Privacy Policy
  • Affiliate Disclosure
© 2023 TechNewsToday.

Type above and press Enter to search. Press Esc to cancel.