How to use conditional formatting in Google Sheets on Mac: rules, formulas and fixes

Google Sheets runs in your browser, so conditional formatting on a Mac happens in a browser tab. Google's help pages describe the steps for the browser version, and I found no Mac app for Sheets in them. Third-party guides say there is none. The steps below follow Google's own help page, and they call out the places where people get stuck.

What you need before you open the menu

• A Mac with a browser open to your Google Sheets file.
• The cells you want to format. A header row is fine, but you can leave it out.
• Columns with one data type each, such as all dates or all text, if you plan to use date or text rules.

If you plan to work offline, use Google Chrome or Microsoft Edge. Google's offline help page names those two browsers only. Private and incognito windows are not supported, so open a normal window before you turn offline access on.

Open Conditional formatting in Google Sheets on Mac

Start by selecting the cells. Click the first cell, then drag to the last one. To cover a whole column, click its letter at the top of the sheet. Conditional formatting only touches the cells you select, so a wrong selection is the first thing to check when a rule does nothing.

Next, click Format in the menu bar, then click Conditional formatting. A panel opens on the right side of the window. This panel is where you choose conditions, set colors and order your rules. Google's help page uses the labels Format cells if and Formatting style, and those labels appear in the panel.

You can open the Format menu from the keyboard with Ctrl+Option+O. Google's Mac keyboard shortcut page lists that combination. The same page lists no shortcut for conditional formatting itself, so you still click the item. Some websites list longer key sequences. I could not confirm those from Google's pages, so treat them as unverified.

Build your first rule with a condition

A rule has two parts. The condition decides which cells qualify. The formatting style decides how those cells look. Under Format cells if, open the list and choose the test you want. For a plain text test, choose Text contains, then type the text in the box that appears.

Under Formatting style, pick a fill color. The color fills the background of every matching cell. Then click Done to finish the rule, and check a few cells by eye before you add more rules.

A worked example: flag late orders

Say a Status column holds the words Late, On track and Done. Select only the Status cells, not the header. Open Conditional formatting, choose Text contains, and type Late. Pick a light red fill, then click Done. Every Status cell that contains Late now shows red, and the other cells keep their normal look.

Text contains matches the word anywhere in a cell. A cell that reads Late payment also turns red. If that is too broad, switch to a custom formula that checks the exact value. The next section shows how to write one.

Custom formula in Google Sheets for multi-column rules

A text or number condition only checks the cell it sits in. A custom formula can check other columns, and it lets you combine several tests. To build one, open Format cells if, choose Custom formula is, and type the formula into the Value or formula box. Set the look, then click Done.

The formula must match the top-left cell of the range you selected. If your selection starts at A2, write the formula for A2. Sheets then shifts the references for every other cell. The SheetSteps guide lists formulas anchored to the wrong row among the causes of failed rules.

For a row highlight, select the whole table and use a formula such as =$B2="Late". The dollar sign before B locks the column, so the test reads the Status column on every row. The row number still moves as the rule fills down.

Custom formulas can reference only cells on the same sheet. To test a value on another sheet, use INDIRECT to build the reference. Google's help page points to INDIRECT for other sheets, so keep the formula short and test it in a helper cell first.

Stack rules and set their order

Most real sheets need more than one rule. Click Add new rule to create another rule. Click an existing rule to edit it. Google's help page says Add new rule creates a rule, and clicking a rule edits it.

Order matters. Sheets checks rules from top to bottom, and the first rule that is true sets the format. Point to a rule and drag it above or below the others to change its priority.

If a cell matches both rules, the higher rule wins. Put the rule you care about most at the top. The SheetSteps guide lists overlapping rules and rule order among the causes of formats that fail, so check the list after each change.

To remove a rule, point to it and click the Remove icon. Then check the cells it used to color, since a higher rule can hide the rules below it.

Color scale in Google Sheets

A color scale fades the cell color across a range of numbers. Open Conditional formatting, then click the Color scale tab. Choose a scale, set the minimum and maximum, and set an optional midpoint. Click Done.

Set the minimum to the lowest value you care about, and the maximum to the highest. A midpoint marks where the color shifts from one shade to the next. Leave it off if you want a simple fade from low to high.

Text contains and wildcards

Text contains and Text does not contain accept wildcards. A question mark matches one character. An asterisk matches zero or more characters. A tilde escapes either symbol, so a tilde followed by an asterisk matches a literal asterisk.

Dates cause a different kind of trouble. A rule that expects a date skips a value stored as text, because Sheets treats it as a word. Check the column before you build the rule. Convert text that looks like dates into real dates, then test the rule on a few cells.

Mixed data types cause the same problem. A column with numbers, numbers stored as text and blank cells behaves differently under each condition. Clean the column first. The duplicates guide is a good place to start when the data has repeat rows.

Copying, pasting and where rules travel

Copying and pasting cells also copies their conditional formatting rules. If you paste a block into another area, its rules come with it. Check the rule list after each paste, and remove any rule you did not want.

Pasting values only, with ⌘⇧V, is a separate action. Google's Mac shortcut page lists it for pasting values only. Use it when you want the numbers or text and not the look of the source cells.

Keyboard shortcuts for Google Sheets on Mac

Google's Mac shortcut page lists Ctrl+Option+O for the Format menu, and ⌘/ to show the common shortcuts. It does not list a shortcut for conditional formatting. Use the menu for this feature, and use the shortcut list for the rest of the sheet. The guide to showing keyboard shortcuts in Google Sheets on Mac covers that list.

Sheets can also accept shortcuts from other spreadsheet apps. Open Help > Keyboard shortcuts and look at the bottom of the window for Enable compatible spreadsheet shortcuts. Google's page says the option is for shortcuts from other spreadsheet apps. It does not say whether the option works differently on a Mac.

Why conditional formatting does not work

1. Check the range first. A rule colors only the cells you selected.
2. Check the formula anchor. Write the formula for the top-left cell of the range.
3. Check the rule order. The first true rule sets the format.
4. Check the data type. Dates stored as text and mixed types stop conditions from matching.
5. Test the formula in a helper cell before you rely on it.

Google's help pages do not give version numbers for the web app, so labels in your panel can differ from the help text. Some guides call the range setting Apply to range. Google's page does not use that name. Match the meaning of each label, and check the live panel.

How common is spreadsheet struggle

Spreadsheet trouble is common, and the numbers below come from one survey. ThoughtSpot commissioned Sapio Research to ask 1,001 office workers in Australia, the UK and the US about their spreadsheets. The survey ran on October 27, 2022. It measured general struggle, not conditional formatting, so read the numbers as context.

The same survey found that formulas are among the most-used features, at 50%. It also reported that Google Sheets is used by 55% of respondents, and Excel by 85%. Those figures describe office workers, not Mac users, so they do not show how many Mac users apply formatting.

Mistakes people make with conditional formatting

• Selecting the header row, so the title row takes the rule's color too.
• Writing a custom formula for the wrong cell, so every row checks the wrong value.
• Stacking rules in the wrong order, so an early rule hides a later one.
• Expecting a Mac shortcut that Google does not document, and losing time looking for it.
• Pasting a block without checking which rules came with it.
• Using dates stored as text, then wondering why a date rule skips them.

Where to go next

Start with one rule on one column. Add a second rule only after the first one colors the right cells. Once the rules work, sorting makes the data easier to read. The sort guide covers that in Chrome.

If you analyze data for work, the Google Sheets page for data analysts covers cleanup and pivots on Mac. If you track tasks in rows, the freeze the top row guide keeps headers visible while you scroll.

Check offline access before a trip. Turn it on from Drive settings, or open the sheet, click the document status icon next to the title, and choose Turn on. Chrome or Edge must be your browser for this to work.

A few rules that you trust beat a long list that you do not. Keep the first rule simple, and build from there.