Applications - The Productive Engineer

Spreadsheets have been an integral part of business operations for decades now. Conditional formatting is a helpful tool to any spreadsheet, though it can be intimidating if you are unfamiliar with it. Let’s take the mystery out of using conditional formatting to make your spreadsheets in Google Sheets easier to read. What is Conditional Formatting? Conditional Formatting options in Google Sheets Conditional formatting in Google Sheets allows the editor of a spreadsheet to set formatting rules based on the content entered into the cells. If the information in the cell(s) matches the criteria, it will be formatted in one way; if it does not, Google Sheets can format it differently. Some examples of this might be a spreadsheet keep keeping track of sales for the month. Should sales fall below a certain margin, the color of the cell may turn red. If the sales should spike above a particular margin call, the cell may turn green. This type of Conditional Formatting has many functions. Not only can it allow the author to control how information is presented in the spreadsheet, but it can also allow for quicker dissemination of information. Because of the formatting differences, conditional formatting will quickly draw your eye to the information contained in the cells. The enhanced formatting will make it easier to notice and read important information at a glance. What Criteria can I use to Conditionally Format cells/rows in Google Sheets? Criteria Options for Conditional Formatting in Google Sheets Several preset formulas are available for you to use for conditional formatting. They cover the more basic functions that one would use to format their spreadsheet for easier reading and data recognition. Enclosed below is a table showing all the possible criteria you can use to conditionally format cells in Google Sheets: CriteriaDescriptionis emptyapplies formatting if the cell(s) is emptyis not emptyapplies formatting if the cell(s) is not empty (i.e. contains a value)Text containsapplies formatting if the type is text and containsa specific character or string of charactersText does not containapplies formatting if the type is text and does not contain a specific character or string of charactersText starts withapplies formatting if the text starts with a specific character(s)Text ends withapplies formatting if the text ends with a specific character(s)Text is exactlyapplies formatting if the text exactly matches the character(s)Date isapplies formatting if the date in the cell matches a specific dateDate is beforeapplies formatting if the date in the cell is before a specific dateDate is afterapplies formatting if the date in the cell is after a specific dateGreater thanapplies formatting if the value in the cell is greater than a definedvalueGreater than or equal toapplies formatting if the value in the cell is greater than or equal to a defined valueLess thanapplies formatting if the value in the cell is less than a definedvalueLess than or equal toapplies formatting if the value in the cell is less than or equal to a defined valueis equal toapplies formatting if the value in the cell is equal to a definedvalueis not equal toapplies formatting if the value in the cell is not equal to a definedvalueis betweenapplies formatting if the value in the cell is within a defined range ofvaluesis not betweenapplies formatting if the value in the cell is not within a defined range ofvaluescustom formula isapplies formatting based a custom defined formula created by the userList of criteria for conditional formatting in Google Sheets The final possibility in the “Format Cell if” drop-down menu is the Custom Formula. This is the area that will allow you to personalize your spreadsheet formatting to fit your specific needs. Using a formula approach, you can customize the Conditional Formatting to highlight information that might not be covered in the preset formatting functions. How to Access the Conditional Formatting Options in Google Sheets Open Google sheets Select the range of cells you want to format. In the main menu, select Format This will open a drop-down menu. Select Conditional Formatting Open Google Sheets a spreadsheet in Google Sheets Go to https://sheets.google.com. Open up the spreadsheet that you want to use conditional formatting in. In our tutorial, we will use this simple account tracking spreadsheet. Select the Range of Cells You Want to Format Select the cells you want to conditionally format in Google Sheets Click and drag to select the cell(s) you want to conditionally format. In the Main Menu, Go to Format -> Conditional Formatting Select Format -> Conditional Formatting As shown in the screenshot above, in the main menu, go to Format -> Conditional Formatting. Conditional format rules in Google Sheets You should now see the conditional formatting rules as shown in the screenshot above. How to change cell color based on the value of cells in Google Sheets. One of the most common things you will want to do is to change the color of the cell in Google Sheets based on the value of the cell. Listed below are the steps required to change the color of a cell in Google Sheets based on the value of the cell: Select the cell(s) you want to conditionally formatSelect Format -> Conditional Format in the main menuUnder Format Rules, click the drop-down box and select the criteria you want for the list of optionsIn the Formatting Styles, select the style of formatting you would like if the conditions were met.Click Done for changes to take effect. To illustrate how to do this, we will use our account activity spreadsheet and change the color of the cell to green for any value greater than zero. Select the cell(s) you want to conditionally format Select cell(s) to conditionally format First thing we need to do is to select the cells we want to evaluate and conditionally format. Click and drag your mouse across the cells you want to include in the evaluation. Select Format -> Conditional Formatting from the main menu Go to Format -> Conditional Formatting Once you have selected your cells, go to Format -> Conditional Formatting in the main menu to bring up the Conditional Formatting Rules menu. Under Format Rules, click the drop-down box and select the criteria you want for the list of options Greater than option in Google Sheets Conditional Formatting You should see a section of the Conditional Format Rules titled "Format Rules" with a drop-down menu. Click on the drop-down menu in this section to bring up the available options. For our example, we are looking to conditional format all values greater than zero so we will choose the "Greater than option as shown above. fill in the value you want to use We need to fill in a value for the greater than option. As we want to format all values greater than zero, we entered zero as shown in the screenshot above. setting the fill color in the conditional formatting menu in Google Sheets Next, you can set the fill color in the "Formatting style" sub-section of the "Format Rules" section as shown above. Click on the "paint bucket" button to bring up the color palette as shown above. Click on the color you want to use as your fill color. Click the "Done" button to save your conditional formatting rule in Google Sheets For our example, we chose green. Click the "Done" button as shown above to save your conditional formatting rule. As you can see, our rule now shows up in the "Conditional format rules" section and all of the cells we selected that are greater than zero as now shaded green. How to Conditionally Format An Entire Row in Google Sheets Click on the row number for that rowIn the main menu, go to Format -> Conditional formatting Under "Format rules", click on the "Format cells if..." drop-down menuSelect the condition option you wantUnder "Formatting style", select the formating you want to applyClick the "Done" button to save and apply your conditional formatting rule Let's dive deeper into each of these steps. Click on the Row Number click on the row number to select all the cells in a row in Google Sheets The first thing we want to do is select the row we want to conditionally format. To do this, simply click on the row number of the row as shown in the screenshot above. This will select all the cells in the row. Hold down the Command key on Mac to select multiple rows in Google Sheets If you want to select multiple rows, hold down the Command key on Mac or the Windows key on Windows and click each row number you want to select. In the Main Menu, go to Format -> Conditional formatting Selecting Conditional Formatting in Google Sheets Next, in the main menu, go to Format -> Conditional Formatting as shown in the screenshot above. Under "Format rules", click on the "Format cells if..." drop-down menu Click on the "Format cells if..." drop-down menu to set conditions in Google Sheets You should now see the "Conditional format rules" section as shown above. Click the drop-down menu underneath the "Format cells if..." subheading. Select the Condition Option You Want Select the condition you want in Google sheets Select the condition you want from the available options. For our example, we will conditionally format all cells in our selected rows that are not empty so we will select "is not empty" as shown above. Under "Formatting style", Select the Formatting You Want to Apply Set your formatting of your conditions in the "Formatting style" section in Google Sheets Next, apply the formatting you want to the cells that fit your conditional logic. For our example, we will bold the text and color the text blue. So we click the "B" button to bold the text and the "A" button and select blue from the color options as shown in the screenshot above. Click the "Done" Button to Save and Apply Your Conditional Formatting Rule Once you have the conditional logic and formatting setup, click the "Done" button to save and apply your conditional formatting. Your cells should now show the conditional formatting you applied. How to Use Multiple Conditional Formatting Rules in Google Sheets It is very common to have more than one Conditional Formatting Rule for one spreadsheet, even for one range of cells. It is possible to group more than one function per range of cells. Enclosed below are the steps required to apply multiple conditioning formatting rules to a group of cells in Google Sheets: Select the cells you want to formatGo to "Format" and Select "Conditional formatting"Go to the "Format cells if..." Section and Click on the Drop-down MenuSelect the Condition You Want in the "Format cells if..." optionSet Your Condition Parameters if NecessaryClick "+ Add another rule" to Create a Second Conditional Formatting RuleSelect and Configure the Condition CriteriaConfigure the Conditional Statement and Formatting Style and Click “Done” to SaveRepeat the Prior Steps for each New Rule You Want to Add Grade Spreadsheet in Google Sheets Here is a grade spreadsheet in Google Sheets. We will use multiple conditional formatting rules to assign specific colors based on the values in the cells we select. Select the Cells You Want to Format Selecting cells in Google Sheets The first thing you want to do is to select the cells you want to apply your conditional formatting rules to. Click and drag your mouse across the cells you want to use as shown in the screenshot above. Go to "Format" and Select "Conditional formatting" Select Conditional Formatting In the main menu, go to Format and select Conditional formatting from the available options as shown in the screenshot above. Go to the "Format cells if..." Section and Click on the Drop-down Menu Conditional formatting rules in Google Sheets In the "Conditional formatting rules" menu, go to "Format cells if..." section and click on the drop-down menu as shown above. Select the Condition You Want in the "Format cells if..." option "Is between" option in Google Sheets conditional formatting condition options Once the drop-down menu appears as shown above, select the condition you want. In our example, we want to conditionally format cells whose values fall within a range of numbers so we will select "Is between" from the available options. Set Your Condition Parameters if Necessary Configuring the "In between" condition in Google Sheets Some conditions require additional parameters or settings. In our example, we chose the "in-between" option to define a range of values. We need to add the range of values as shown above. Once you have set your condition settings, configure the formatting in the "formatting style" section. We will set our example for this rule to be the following: Bold and set the text color to green for any selected cell that has a value between 90 and 100. Once you have finished configuring the conditional and formatting, click the "Done" button to save and apply the conditional formatting rule. Click "+ Add another rule" to Create a Second Conditional Formatting Rule Click "+ Add another rule" button to add an additional conditional formatting rule in Google Sheets Now we need to add a second conditional formatting rule to our spreadsheet. In our example, we will add rules for the following grade ranges: 80-8970-7960-69 Each range will have its own rule. To get started adding an additional rule, select/reselect the cells and click the "+ Add another rule" button as shown in the screenshot above. Select and Configure the Condition Criteria Select the Condition for your Conditional Formatting in Google Sheets Like we did with the first conditional formatting rule, select the condition you want from the "Format cells if..." section. We will again choose "Is between" to define a second range of values that we can conditionally format. Configure the Conditional Statement and Formatting Style and Click "Done" to Save Configuring a conditional rule in Google Sheets Configure the conditional logic and formatting style for your second conditional formatting rule. In our example, we are going to do the following: Bold and change the text color to blue any cell that have a value between 80 and 90 Once you have finished setting up the rule, click the "Done" button to apply and save the rule. Repeat the Prior Steps for each New Rule You Want to Add Repeat the prior steps for each of the new rules you want to add. I have enclosed the screenshots of each additional rule I added to our example spreadsheet Configuring a conditional rule that applies to any cell that has a value between 70-80 Configuring a conditional rule that applies to any cell that has a value less than 70 Google Sheets Grade Spreadsheet with 4 conditional formatting rules applied I sincerely hope that this tutorial has taken some of the mystery out of using Conditional Formatting to enhance your Google Sheets experience! Have fun with it!