Conditional Formatting In Google Drive Sheets or Gsheets Based On Cell Values

by Rohit Khurana on July 13, 2012

Advertisement

Google Docs which is now rebranded as Google Drive is a very useful service by Google. Earlier it was a online document storage and collaboration tool, but gradually it has changed into a full backup solution for almost any kind of data with Google Drive clients for computers, smartphone and tablet PCs which keep all your important files and data on the cloud in a safe and easy to access way.

Google Documents/ Drive have options to create spreadsheets very similar to what we create in Microsoft Excel as Excel Sheets. These Google Drive Sheets or Gsheets can be accessed by multiple users as same time and collaboration can be done on it. There are many additional features which these sheets offer, but in this article, I will share one very basic and useful tip which will come handy for many beginners who think that it takes some sort of magic to make sheets highlight in certain colours when a a particular value is in a particular range etc. This feature is called conditional formatting and is also available in Microsoft Office Excel, but in Google Drive its much simpler and easy to use. In this article I will give you a demo of how to use the basic conditional formatting in Gsheets or Google Docs Sheets.

Conditional Formatting In Google Drive Sheets or Gsheets (1)

For demonstration purpose, lets assume that we have a mark sheet of a student with subjects, marks, and remarks which say whether that subject is passed or failed by student. You can use this feature in a number of ways, say for tracking the progress of task item by highlighting pending items in a specific colour, or for any other purpose to visually understand profits, loss, growth and other data by using some sort of colour code for each data range.

Steps To Use Conditional Formatting In Google Drive Sheets or Gsheets

Step1: Enter the basic data which you want to enter the Google sheet first. You can use this feature on a cell as well, but in this demo we use this feature on entire column because it makes more sense in our example. In case you want to use it for a cell only, right click on the cell and you will get the conditional formatting option in the menu. For column formatting, click on the small arrow on the column and click on Conditional Formatting.

Conditional Formatting In Google Drive Sheets or Gsheets (2)

Step2 – Number Value Based Formatting: You can now start adding conditions to colour code the cells based on values. In this example, we make cells with less than 40 value as red, as it denotes failed in a subject. You can choose to highlighting the text colour, or background or both. For simplicity sake, we use only highlight background option.

Advertisement

Conditional Formatting In Google Drive Sheets or Gsheets (3)

Similarly click on Add another rule link if you want to add more conditions.

Conditional Formatting In Google Drive Sheets or Gsheets (4)

You can add multiple conditions, in this example we highlight a cell with red if its less than 40 in value, light blue if its between 40 and 60 and in green colour if its greater than 60.

Conditional Formatting In Google Drive Sheets or Gsheets (6)

Below is the result when you press the Save rules button.

Conditional Formatting In Google Drive Sheets or Gsheets (7)

Step3 – Number Value Based Formatting: For text based formatting, the process to set the formatting is same as in above steps but in this case the conditions are based on the text comparison. You can make many combinations of checks including “Text contains”, “Text does not contain”, “Text is exactly” and you even have an option to highlight empty cells.

Conditional Formatting In Google Drive Sheets or Gsheets (10)

The feature is very simplified in Google docs which makes its very easy and friendly to use.

Conditional Formatting In Google Drive Sheets or Gsheets (11)

Same way you can use the conditional formatting based on the dates to track tasks based on date related conditions. These formatting rules will work on the whole column, so if you add more valued in these columns they will be automatically highlighted and you need not make these rules again for the same column. So go ahead and make the fancy automatic highlighting sheets. Do let us know how you find this tip by leaving a comment below. Share it with your friends also as it will be helpful for them too. Check out more Tips and Tricks by TroubleFixers.

If you like this article or this article helped you, you can +1 to recommend this article on google plus.

You can follow us on Twitter or join our Facebook Fan Page. If you have a question to ask us, submit your question at Answers By Trouble Fixers.

Looking for something else? Search here :

Leave a Comment

Previous post:

Next post:

© TroubleFixers – All about fixing computer troubles2007-2014 . All Rights Reserved.