Tech Tips Home
The Best Tech Tips And Daily Deals
Newsletter On The Internet!

WorldStart Tech Tip And Store Search
Email: Password: Login Remember Me
looking for freeware

Like what you see here? Subscribe to the Tech Tips newsletter!   Email: Subscribe

How To Use Conditional Formatting In Google Sheets

Wednesday, November 4th, 2015 by | Filed Under: Free Downloads, Using The Internet

Conditional formatting is a way to distinguish cells in Google Sheets by shading them a different color if they meet specific criteria. Setting up the formatting isn’t much different than in Microsoft Excel. If you’ve switched from Microsoft Office or just prefer using Google Docs, you’ll be happy to know you can format your cells based on a wide variety of criteria.

Getting Started

Open your spreadsheet in Google Sheets. I recommend creating a backup until you get the hang of the formatting. It’s easier to go back to the original than try to undo all the changes you’ve made.

Access conditional formatting by selecting the Format menu and choosing Conditional Formatting.

Formatting Menu



You’ll then be able to select your cell range and criteria.

Setting Up Conditional Formatting

On the right side of the spreadsheet, you’ll see the conditional formatting window. Before selecting any criteria, choose either Single color or Color scale. The first option colors your cells a single color. For instance, if you wanted all cells between 50 and 70 to be formatted, you could make all those cells turn red.

If you choose color scale, you’re able to choose a range of colors for a gradient affect. This works well if you want the color to gradually change as the values of a cell increase or decrease.

Color Modes


Once you’ve chosen your color mode, you’re ready to set up the rest of your conditions.

Click inside the Apply to range text box and either enter your cell range or highlight the cell range on your spreadsheet. Only select the cells you want to be formatted. You can still base your conditions off of other cells.

If you’re highlighting a range, press OK in the What Data? box to approve your selected range.

Range Select



Select your condition using the Format cells if drop down menu. If you have a custom condition in mind, choose Custom Formula is at the bottom of the list.




Enter your criteria in the Value or Formula box that appears directly  under your chosen condition. If you’re doing a custom formula, enter it in this box.

You’ll see a preview of how cells will be formatted based on what you enter. This gives you an easy way to tweak the formula or condition to get it just right.

Set Formula


Next, select your formatting style. Basically, you can choose from either a colored cell or colored text.

Formatting Style


If you don’t like the default colors, click the paint can icon and choose your own color. If you’re only changing the color of the text, select the text color icon and choose your color.

Select Color


If you’re using the Color scale mode, you’ll need to select a color for your min and max values.

Click Done when you’re finished customizing your conditional formatting to create the rule and apply it to your sheet.

Save Rule


Whenever you need to change, delete or add a new rule, open the Conditional Formatting menu and click Add New Rule. If you highlight the same range of cells or any cells already affected by conditional formatting, you’ll see the existing rules listed for you to edit or delete.

All changes and formatting are saved moments after setting them. Play around with the different conditions to see how each affects your data. Remember, conditional formatting can be set on text, numbers and even blank cells.


Tags: , , , ,

One Response to “How To Use Conditional Formatting In Google Sheets”

  1. John says:

    Be aware that all conditional formatting created in Sheets document is lost when downloading the file to Excel!

Leave a Reply

Like these tips? Get them for FREE in your email!

WorldStart's Tech Tips Newsletter

  • Tech Tips Daily - Become a tech pro! Get the very best tech and computer help sent directly to your email every weekday!

  • Tech Tips Weekly - If you don't want our Tech Tips newsletter every day, then sign up for this weekly newsletter to get the best information of the week. Sent on Fridays.

Other Newsletters

  • WorldStart's Daily Deals - Every week, we send out great deals in our Daily Deals newsletter. Many of these deals are exclusively for our Daily Deals newsletter subscribers and can't be found with our regular specials.

  • Just For Grins - Each issue includes a couple clean jokes, some funny quotes, and a hilarious reader's story. Newsletter is sent five days a week.

Enter Email Address:


Your e-mail address is safe with us!
We only use it to send you the newsletters you request. It is NEVER disclosed to a third party for any reason, ever! Plus, if you decided you don't like our newsletters (don't worry, you'll love them), unsubscribing is fast and easy.

Free Newsletter Signup

Tech Tips Daily

Become a tech pro! Get the very best tech and computer help sent directly to your email every weekday!

Tech Tips Weekly

The week's best in tech and computer help. Get your issue sent to your email every Friday!

WorldStart's Daily Deals

The very best deals on the Internet! Get a new set of incredible sales every day of the week!

Just For Grins

Clean jokes, funny quotes, and hilarious comics. Sent 5 times a week straight to your email.


Love Worldstart? Refer A Friend!

looking for freeware
WorldStart's Premium Membership

Tip Archive