Using Conditional Formatting in Google Sheets

Applying conditional formatting to your spreadsheets can really add a professional touch by instantly changing the appearance of cells, rows, and/or columns based on certain criteria. When certain conditions are met, the background and text color of the corresponding cells, or even cells elsewhere in your spreadsheet, can change instantly. This can be helpful in several ways, some of which are detailed in the examples in this guide.

Follow the steps below to apply conditional formatting in Google Sheets on your computer and Android device. While you can view conditional formatting rules on iOS devices (iPad, iPhone, iPod touch), you cannot create or edit them.

Content
  1. Basic rules for conditional formatting
  2. Conditional formatting with custom formulas
  3. Remove conditional formatting

Basic rules for conditional formatting

Adding basic conditional formatting rules to a range of cells in Google Sheets starts with a menu Format

Desktop/Laptop (most web browsers; Google Chrome preferred)

  1. Select one or more cells to which you want to apply conditional formatting. In this example, we chose each seller’s conversion rate.

  2. Click Format is located in the Sheets menu at the top of the screen.

  3. When the drop-down list appears, select Conditional Formatting

  4. Couple Conditional Formatting Rules should now appear on the right side of the worksheet with some customizable options regarding the formatting of the selected cells, which are displayed in Apply to Range † Select the drop-down menu labeled Format cells as… which provides several obvious formatting conditions that can be applied to the above range of cells. For the purposes of this example, we chose to format cells as their content is less than 30% conversion rate.

  5. Now that you’ve specified the condition, it’s time to set up the visuals that you want to apply to the cell(s) if it’s met. Click on the drop-down menu labeled layout style , after which you will be asked from several predefined text and background colors to choose from. If none of these options apply, then the Custom Size lets you choose your own colors and effects, including bold, italic, underline, and strikethrough text.

  1. If applying just one formatting style to a cell isn’t enough to meet your needs, worksheets can display different shades of a particular color, depending on how close a number or percentage is to the minimum point, midpoint, or maximum point. To apply this progressive fill to the background of your cells, click the tab header color scale in the interface Conditional Formatting Rules and enter the desired numerical values ​​and colors in the fields. as long as.

  2. After making the above changes, you will notice that they are applied to the selected range of cells immediately. However, these changes are not permanent and can be canceled by clicking the button Cancel † To keep them in place, select Finished to close the window Conditional Formatting Rules and commit the new format.

You can apply multiple formatting conditions to the same range of cells by repeating the above steps 1-3 and choose the option Add new rule † When multiple rules are applied to the same cell, they are processed from top to bottom in order of priority. They can be changed by simply dragging them up or down in the list.

android

  1. Launch the Google Sheets app.

  2. Open a new or existing spreadsheet.

  3. Select one or more cells to which you want to apply conditional formatting.

  4. Press the button Format marked with the letter “A” and at the top of the table.

  5. The format interface should now be visible at the bottom of the screen. Scroll down and select Conditional Formatting

  6. The interface should now appear. Create rule which contains a number of customizable options regarding the formatting of selected cells, which are presented in the section Apply to Range † Select the drop-down menu labeled Format cells as… which provides several obvious formatting conditions that can be applied to the above range of cells.

  7. Once you’ve specified the condition, it’s time to set the images you want to apply to the cell(s) if it’s met. Click on one of the six options below layout style .If none of them meet your needs, press the button Amended lets you choose your own colors and effects, including bold, italic, underline, and strikethrough text.

  1. If applying just one formatting style to a cell isn’t enough, worksheets can display different shades of a particular color, depending on how close the number or percentage is to the minimum point, midpoint, or maximum point. To apply this progressive fill to the background of your cells, tap the tab header color scale and select the desired numerical values ​​and colors in the corresponding fields.

  2. If you are happy with your choice, click the button SAVE to apply them. To add a second rule (the conditions are described above), select SAVE AND ADD NEW

  3. The screen will appear Conditional Formatting , which lists your new rules. Click the checkbox in the top left corner of the screen to return to the table.

Conditional formatting with custom formulas

Google Sheets offers over a dozen different formatting conditions for text strings, dates, and numeric values ​​— as we noted above. However, this functionality is not limited to these default options, as you can also use your own formula to determine whether to format a range of cells.

To do this, follow the same steps as for enabling the main condition, with one important exception. When you get to the option selection point in the drop-down menu Format cells select Custom formula † Then type the desired formula in the input field below the menu.

One of the nice things about using a custom formula is that you can apply conditioning to a range of cells based on values ​​found elsewhere in the current spreadsheet. In this example, we applied the following formulas to each of the respective cells in column E, coloring the cell in green only if the conversion rate in the previous column is greater than 40%: =$D2>0.4,=$D3> 0 .4 = D4 > 0.4 = D5 > 0.4 = D6 > 0.4.

So what we’ve done here is add conditional formatting to a range of cells based on percentages found in another range of cells, which cannot be achieved with the predefined options.

Remove conditional formatting

Removing conditional formatting rules from a cell or group of cells is a very simple process.

Desktop/Laptop (most web browsers; Google Chrome preferred)

  1. Select the cells where you want to remove one or more conditional formatting rules.

  2. Click Format is located in the Sheets menu at the top of the screen.

  3. When the drop-down list appears, select Conditional Formatting

  4. Couple Conditional Formatting Rules should now appear on the right side of the worksheet, listing the lines currently associated with the selected range of cells. Hover your mouse over the line you want to delete to make the trash can appear. Click this button to delete the corresponding rule.

android

  1. Select the cells where you want to remove one or more conditional formatting rules.

  2. Press the button Format marked with the letter “A” and at the top of the table.

  3. The format interface should now be visible at the bottom of the screen. Scroll down and select Conditional Formatting

  4. The screen will appear Conditional Formatting with a list of rules currently applied to the selected range of cells. To delete a specific rule, tap the trash can button to the right of its name. A confirmation message will briefly appear at the bottom of the screen, followed by a button labeled CANCEL † If you make a mistake, press this button quickly before it disappears.

Leave a Reply

Your email address will not be published.