# How to Use Con­di­tion­al For­mat­ting in Google Sheets **URL:** https://www.compsmag.com/blogs/how-to-use-conditional-formatting-in-google-sheets/ **Author:** Ayushi Chauhan **Published:** 2022-10-20 **Updated:** 2022-10-20 **Categories:** Blogs **Tags:** Blogs Daily, Google tips and tutorials, guiderobert, Guides and Tutorials, guiding12, How To Guide, How To Guides, Tips, Tricks **Reading Time:** 8 min --- This tip is about the how to Use Con­di­tion­al For­mat­ting in Google Sheets. So read this free guide, How to Use Con­di­tion­al For­mat­ting in Google Sheets step by step. If you have query related to same article you may contact us. ## How to Use Con­di­tion­al For­mat­ting in Google Sheets - Guide Google Sheets offers many advanced features that help separate the meaning of a pile of data. One of them, basic and at the same time amazing, is the restrictive formatting. This helps transform dull rows and columns of black text on white backgrounds into a shady, outwardly appealing dataset. This saves time and also makes the data more discernible and meaningful. Spreadsheets contain large measurements of data. You can use pivot tables to add up this data, but imagine a scenario in which you need to gain some understanding just by looking at your spreadsheet. Conditional formatting in Google Sheets will give you that understanding. Conditional Formatting - Which Allows You feature cells that fit specific templates - can help you better understand worksheets initially and create more understandable worksheets for your entire group. Likewise, it's an amazing way to follow objectives, giving visual cues of how you're progressing with explicit measurements. Here, we'll look at the basics of conditional formatting in Google Sheets. To track, use our demo sheet. Copy the spreadsheet and then tweak it as we go along. tutorial. ## What is Conditional Formatting It is a feature which allows you to modify / change the appearance of a cell, based on a single or set of rules. You can change various aspects of a cell, such as its background color, text type (Bold / Italic / Underline). For rules, you can use the existing ones available in Google Sheets or create your own rules. Based on your requirements, you can apply conditional formatting to a single cell, group of cells or an entire row / column. ## Rules - What Does It Mean Before thinking about using conditional formatting, you first need to understand the structure of the rules. Each rule consists of the following three elements: Range - defines the scope of the rule. For example, let's say you'd like to format cells B2 through B22, based on a condition. In this case, the range is set to 20 cells. Condition - define trigger events. In the example above, let's assume you need to format cells if their value is greater than 10. Style - sets the cell style. For example, change the background color of cells to blue. ## How to Use conditional formatting in Google Sheets Now, let's take a closer look at the various types of conditional formatting that can be used in a cell, row, or column in Google Sheets. ### Conditional formatting with numbers Let's say you're evaluating someone in your company's monthly timesheet. And, you need to find out if he/she is logged in for at least 7 hours on every weekday that month. The timesheet looks like the one below: We will see how to use conditional formatting for this scenario. Open the timesheet file and navigate to Format->Conditional Formatting. Here, you will see a new window titled Conditional Format Rules on the right side of the screen. By default, the single color guide will be selected: In the drop-down box titled Apply to Range, click the Table icon, select the range as C4:C33, and click OK. From the Formatting Rules drop-down box, select Less than. Then enter 7 in the box titled Value or formula. Then, under Formatting Style, set the Fill Color to the desired color. Instead of changing the cell's background color, you can change the text color or set other formatting options such as Bold, Italic, Underline, and Strikethrough. Finally, click Done. Now, if you look at the image above, days with a working day less than 7 are highlighted (Column C). For conditional formatting with numbers, you can use any of the conditions listed below: Now, if you look at the image above, days with a working day less than 7 are highlighted (Column C). For conditional formatting with numbers, you can use any of the conditions listed below: Let's say your employee database spreadsheet has hundreds of entries and you want to highlight employees who reside in a specific location (for example, Virginia). You can do this easily using Conditional Text Formatting. Open the employee database file and navigate to Format-> Conditional Formatting and look for the window titled Conditional Formatting on the right side of the screen. In the drop-down box titled Apply to Range, click the Table icon, select the range as D4:D104, and click OK. In the Format Rules drop-down box, select Text contains and type Virginia in the box provided below. Then, under Formatting Style, you can change the cell text/background color or other options like Bold, Italic, Underline or Strikethrough. In this example, the Fill Color is set to Green. Click Done. Now, all employees who are located in Virginia are highlighted as follows: Other options available for conditional formatting with text are: The text starts with text ends with the text is exactly The text contains The text does not contain ### Conditional formatting with color scale Color-scaled formatting is useful if you need to analyze data for levels such as minimum, maximum, and average. For example, a commercial salesperson can use this formula to quickly analyze sales revenue in different locations. We will see how to do it. Open the file and navigate to the Conditional Formatting window. Then click on the tab titled Color Scale. Then select the range of cells for which you need to apply the rules. Here, you will see three drop-down boxes titled: Minpoint, Midpoint, Maxpoint. Based on your need, you can select Number, Percentage or Percentile. In case you want to set a specific value for Midpoint, Min point or Max point, you can do this by entering the numbers in the corresponding boxes. In this example, we selected 40 percent for the midpoint. Then select the desired colors for all three checkpoints if you don't like the default color settings. Finally, click Done. ### More Conditional Formatting Options In addition to number, text, color range, there are two more options available in the conditional formatting window: This is the simplest option in conditional formatting. If you want to highlight just a group of empty or non-empty cells, you can use this feature. To locate this option, simply click on the drop-down box titled Format Cells in the Conditional Formatting window. #### Meeting This formula will be useful, especially if you want to compare your data over a specific date range or a single date by clicking the drop-down box titled Format cells if: date is the date is before the date is after After selecting any of the date options above, you will see another drop-down box as shown below: Based on your need, you can select any of the options to create the date based rule. ## How to Use conditional formatting on an entire row in the Google Spreadsheet (custom formula) So far, in all the examples mentioned above, single or multiple cells in a column have been highlighted. But what if you need to conditionally format a row in Google Sheets instead of a cell? Thanks to the Custom Formula option, you can do this easily. Now let's consider the example mentioned in the Conditional formatting with text section. Instead of just marking the cell that contains the text “Virginia”, let's try to highlight the entire line. Navigate to the Conditional Formatting window after opening the file. Next, you must select the entire dataset for range, as we need to apply conditional formatting to an integer now. In this example, we select the range as A4:D20. Then select the Custom formula is option from the drop-down box titled Formatting Rules. Here, you need to enter the formula as =$D4 = "Virginia" in the box provided below. Now, let's try to understand how this custom formula works. =: Any custom formula must start with this character.D4: indicates your sample data. that is, D4 is the first cell in column D whose data is analyzed. = ”Virginia”: Defines the condition. As we need to highlight the lines of employees located in Virginia, = is used. If you want to tag employees who are not located in Virginia, you can use “Virginia”. Likewise, you can create conditions involving numbers using symbols such as >, =, Finally, click Done. ## Final note I hope you like the guide How to Use Con­di­tion­al For­mat­ting in Google Sheets. In case if you have any query regards this article you may ask us. Also, please share your love by sharing this article with your friends. --- *End of Article*