How to Use Conditional Formatting in Google Sheets

To use conditional formatting in Google Sheets, select your cells, open Format > Conditional formatting, choose a condition and formatting style, then click Done. Choose Single color to highlight matching cells or Color scale to compare numerical values with a gradient.

You can create and change rules on a computer or in the Android app without Gemini. On an iPhone or iPad, you can view existing conditional formatting, but use a computer to create or change its rules.

Select the cells you want to format

Open your spreadsheet on a computer and select the cells that should receive the formatting. If your column has a heading, select the data below it when the rule should apply only to your entries. For help keeping headings in place, see how to freeze your header row.

Choose Format > Conditional formatting to open the sidebar. Check Apply to range before setting the condition, because it determines which cells receive the rule. For example, C1:C100 targets the first 100 cells in column C.

You can also click the toolbar's Search button or choose Help > Search the menus. Search for Conditional formatting, select the matching command, and configure your rule in the sidebar.

Create single color rules or color scales

Choose Single color, open Format cells if, and select a condition for numbers, text, dates, or empty or nonempty cells. Enter the comparison value in Value or formula when required. To highlight numbers below a limit, choose a number condition that checks for values below your threshold and enter that threshold.

Under Formatting style, choose how matching cells should look, then click Done. For text matching, select the appropriate text condition and enter the text you want to match. If unexpected cells receive the color, reopen the rule and check its condition and Apply to range.

For a gradient, select your numerical range, open Format > Conditional formatting, and choose Color scale. Select a scale under Preview, then configure the minimum and maximum values and their categories. Add an optional midpoint if needed, then click Done.

Choose a single color rule when you want to identify values that meet a condition, and a color scale when you want to compare values across a range. If the gradient covers unintended cells, check Apply to range before changing the scale settings.

Use custom formulas for rows and duplicates

To highlight rows across columns A through E when column B equals Yes, select A:E and open Format > Conditional formatting. Under Format cells if, choose Custom formula is. Enter the following expression in Value or formula, keeping the dollar sign before B and the quotation marks around Yes.

=$B1="Yes"

Choose the formatting style, then click Done. The rule checks column B and formats the selected cells across each matching row. Selecting only column B would limit the formatting to that column.

Use the row number that corresponds to the beginning of your selected range. Here, A:E begins at row 1, matching $B1 in the formula. If the highlight follows the wrong row, check that starting reference and Apply to range.

You can also use a custom formula to highlight duplicates in Google Sheets. Open Format > Conditional formatting, set Apply to range to C1:C100, and choose Custom formula is under Format cells if. Enter this expression in Value or formula.

=COUNTIF(C:C,C1)>1

Choose Fill color, select your color, then click Done. The formula checks for repeated values throughout column C but formats only C1 through C100. A matching value elsewhere in column C can therefore cause a cell inside the selected range to be highlighted.

Highlighting duplicates keeps the repeated entries in your sheet. If you want to delete them, follow the steps to remove duplicates in Google Sheets. Choose that action only when you intend to remove data.

Fix unexpected or overlapping colors

Open Format > Conditional formatting and select an existing rule to edit it. Change its condition, range, or formatting style, then click Done. To add another condition, select Add new rule and configure it.

Sheets evaluates rules in their listed order, and the first true rule determines the formatting. If an expected color does not appear, check whether an earlier rule already matches that cell. Drag rules to reorder them so the condition you want to take priority comes first.

If changing the order does not resolve the problem, check the affected range and the condition's value or formula. To delete an unwanted rule on the web, hover over it and select Remove.

Copy conditional formatting to another range

Select the source cells and press Ctrl + C on Windows or ChromeOS, or Command + C on macOS. Select the destination cells, then choose Edit > Paste special > Paste conditional formatting only. This copies the conditional formatting without a normal paste of the source data.

Google limits Paste special to the same spreadsheet, including its other sheet tabs. After pasting, open Format > Conditional formatting for the destination and review the range and formula, especially if the formula refers to another column. For a different spreadsheet, create the rule there instead of relying on this paste option.

Use conditional formatting on your phone

In the Android app, select your cells and tap Format > Conditional formatting. Tap Add when a rule already exists. Under Single color, choose Format cells if, select a condition, set Formatting style, and tap Done.

For a gradient, choose Color scale and select a scale under Formatting style. Optionally set minimum, maximum, and midpoint values, then tap Done. For a formula rule, choose Custom formula under Format cells if, enter it in Value or formula, choose your styling, and tap Done. The row example above also works on Android: select A:E and use =$B1="Yes".

To manage Android rules, open Format > Conditional formatting and choose Selected range or All. Tap a rule to edit it, use Move and drag to reorder it, or tap Remove followed by Done to delete it.

On an iPhone or iPad, open your spreadsheet to see its existing conditional formatting. Google does not allow rule editing in those apps. Use Sheets on a computer to create or change a rule.

Apply conditional formatting with Gemini

If your account has eligible Gemini access, open your spreadsheet on a computer and select Ask Gemini. Type Highlight values below 100, review the Action preview card, and select Apply. When available, use Settings to adjust the rule or Undo to reverse the action.

You can also ask Gemini for instructions by entering Teach me how to use conditional formatting with a color scale. Follow the resulting instructions and check the settings in the conditional formatting sidebar.

If you have an Excel file open in Sheets, select File > Save as Google Sheets before using Gemini formatting. Open Ask Gemini in the resulting native spreadsheet and request the formatting you want.

Fix missing Gemini access

Gemini in Sheets requires eligible access, and language or administrator restrictions can affect availability. For an eligible account, turn on Smart features in Google Workspace through one of the account settings paths below, then click Save. These settings govern Gemini access; ordinary conditional formatting does not require them.

• In Gmail, open Settings > See all settings > General > Google Workspace smart features > Manage Workspace smart feature settings.
• In Drive, open Settings > Settings > Privacy > Manage Workspace smart feature settings.
• In Calendar, open Settings > Settings > General > Google Workspace smart features > Manage Workspace smart features settings.
• In Chat, open Settings > Data Privacy > Google Workspace smart features > Manage settings.
• In Meet, open Settings > General > Google Workspace smart features > Manage.

If you administer a supported Workspace account, open the Admin console and select Menu > Generative AI > Gemini for Workspace > Feature access. Optionally select an organizational unit or configuration group, then click Edit beside the service grouping containing Sheets. Choose On or Off, then click Save.

You need the Gemini Settings administrator privilege to change that control. It applies to Enterprise Standard/Plus, the Teaching and Learning add-on, Education Plus, and Google AI Pro for Education.

Automate rules with Apps Script or the Sheets API

If you have spreadsheet editing permission and Apps Script access, open Extensions > Apps Script in desktop Sheets. Create your conditional formatting function there. You can reopen the project through the same menu or the Apps Script dashboard.

Inside the function, get the sheet and target range, then start a rule with SpreadsheetApp.newConditionalFormatRule(). Add a condition such as whenNumberBetween(1, 10), set the background with setBackground('#FF0000'), assign the range with setRanges([range]), and finish with build().

Retrieve the existing rules with sheet.getConditionalFormatRules() and append your new rule to that array. Apply the array with sheet.setConditionalFormatRules(rules), then run your function with authorized spreadsheet access.

For an integration using an authorized Sheets API client, call spreadsheets.batchUpdate. Use AddConditionalFormatRuleRequest to add a rule, UpdateConditionalFormatRuleRequest to replace or reorder one, or DeleteConditionalFormatRuleRequest to remove one. When defining a rule, specify its ranges and either a booleanRule or a gradientRule.

Frequently Asked Questions

Is there a keyboard shortcut to open conditional formatting?

You can open the tool finder with Alt + / on Windows or ChromeOS, or Option + / on macOS. Type Conditional formatting, press Down arrow to select the result, then press Enter. Shortcuts can vary with your keyboard layout.

Can conditional formatting match part of a text value?

Yes, choose Text contains or Text does not contain, then use ? for one character or * for any sequence of characters. Put ~ before either wildcard to match the character itself. Choose the formatting style and click Done.

Can a conditional formatting formula reference another sheet?

Yes, but references to another sheet require INDIRECT. Use that function within your custom formula when the condition depends on a different sheet tab.

Does a normal copy and paste include conditional formatting rules?

Yes, copying cells and pasting them normally carries their conditional formatting rules along with the data. This works on the web and in the Android app.

Does access to the Gemini app mean you can use Gemini in Sheets?

No, standalone Gemini app access does not establish access to the Sheets side panel. Google lists Sheets AI access for Google AI Pro/Ultra and eligible Workspace editions, with supported Workspace Experiments access providing another route.