Software & Apps Google Drive How to Use Conditional Formatting in Google Sheets Change background color and add a professional touch by Scott Orgera Writer Scott Orgera is a former writer who covering tech since 2007. He has 25+ years experience as a programmer and QA leader, and holds several Microsoft certifications. our editorial process Facebook Twitter Scott Orgera Updated on October 01, 2020 Google Drive Sheets Docs Slides Tweet Share Email Conditional formatting in Google Sheets lets you add a professional touch to your spreadsheets. Modify the look and feel of cells, rows, or columns based on certain criteria. Plus, find specific data types. For example, use conditional formatting to highlight duplicate data in Google Sheets to spot trends. Here's how to apply conditional formatting in Google Sheets on a computer or an Andriod device. While you can view conditional formatting rules on iOS devices, you can't create or edit these rules. gilaxia / Getty Images How to Use Conditional Formatting in Google Sheets Conditional formatting means that when specific conditions are met, the background and text color in designated Google Sheets cells instantly changes. This is useful when you want to see certain information or call out specific data. Set Conditional Formatting With Google Sheets on a Desktop Here's how conditional formatting works for Google sheets on a Windows PC or Mac using Chrome, Firefox, Safari for Mac, or IE 11 and Edge for Windows. Google Sheets may work in other browsers, but all features may not be available. Select the cell range where you want to apply conditional formatting. This example uses a spreadsheet with salespeople's conversion rates. Select Format from the top menu bar. Select Conditional Formatting. The Conditional format rules dialog box appears on the right side of the screen. Select the Format cells if drop-down menu and choose a condition. If you're following this example, choose Less than. Choose from a variety of self-explanatory conditions, or select Custom to create a condition. In the Value or formula box, enter the condition's criteria. For this example, enter 30% to highlight salespeople whose conversion rates are less than 30%. Under Formatting style, select a predefined background color or select Custom format to choose colors and effects, including bold and italics. To further enhance a conditional effect, select the Color Scale tab. Select a gradient. The color on the left applies to the lower number values in the selected cell range. The color on the right affects higher values. You'll see a live preview of the gradient colors when you select a color scale. When you're happy with your conditional formatting choices, select Done. The spreadsheet reflects your settings. To apply multiple formatting conditions to the same cell range, go to Format > Conditional Formatting, and select Add another rule. Google Sheets processes multiple rules in priority order from top to bottom. Reorder rules by dragging a rule up or down in the list. Conditional Formatting With Google Sheets on an Android Device Here's how conditional formatting works for Google Sheets on an Android device. Launch the Google Sheets app and open a new or existing spreadsheet. Select the cell range you want to format. Tap the Format button, represented by the letter A, near the top of the spreadsheet. You'll see the Create a rule interface. Scroll down and select Conditional Formatting. Select the Format cells if drop-down menu and choose a condition. Adjust the visuals you want to apply to cells that meet your condition. In the Formatting style section, tap one of the six options or choose Custom to select colors and effects. Tap the Color scale tab to apply gradient colors to cells. Choose the numeric values and colors you want to use. Tap Save to apply your choices. You'll see your new rule on the Conditional Formatting screen. Tap the check mark to exit and return to the spreadsheet. Tap Save and New to add another rule. Conditional Formatting Using Custom Formulas Google Sheets provides more than a dozen formatting conditions dealing with text strings, dates, and numeric values. You're not limited to these default options. Use a custom formula to apply a condition to a cell range based on values from other cells, which isn't an option with the predefined selections. This example uses a custom formula to show when the same value appears in more than one cell using the COUNTIF function. Open a spreadsheet and select the cell range you want to format. This example selects cells B2 through B15. Go to Format > Conditional Formatting. Under Format cells if, choose Custom formula is. Enter the formula into the Value or Formula field. For this example, use the formula: =COUNTIF(B:B,B2)>1 If your range of cells isn't in column B, change it to your column, and change B2 to the first cell in your selected range. Select Done. Any duplicate information in your spreadsheet is highlighted. How to Remove Conditional Formatting It's easy to delete a conditional formatting rule. Using Google Sheets on the Desktop in a Browser Select the cell range where you'd like to remove one or more conditional formatting rules. Select Format. Choose Conditional Formatting. You'll see any current conditional formatting rules. To delete a rule, hover the cursor over the rule and select the trash can icon. Using an Android Device Select the cell or cells where you'd like to remove one or more conditional formatting rules. Tap Format (represented by the letter A). Select Conditional Formatting. You'll see a list of current rules. To delete a rule, tap the garbage can icon next to it.