How to Color Cells in Google Sheets Based on Text

Color-coding your data in Google Sheets not only enhances the visual appeal of your spreadsheets but also makes for easier navigation and understanding of complex information. One of the most common tasks is to color cells based on their text content. Here's a step-by-step guide on how to achieve this, along with some tips and tricks to make your work more efficient.

How to get the HTML color codes in Google Sheets - Plus every color in the color picker!
How to get the HTML color codes in Google Sheets - Plus every color in the color picker!

Before we dive into the specifics, ensure you are familiar with the basic conditional formatting rules in Google Sheets. If not, you can quickly learn them in the Google Support documentation.

How Do You Highlight Cells of the Same Color - Teacher Tech with Alice Keeler
How Do You Highlight Cells of the Same Color - Teacher Tech with Alice Keeler

Formatting Cells Based on Text

Google Sheets provides an easy-to-use interface to apply conditional formatting based on cell values. Let's explore how to color cells based on specific text they contain:

How to Change Your Google Sheets Theme Color
How to Change Your Google Sheets Theme Color

BeforeAfter
كلمة
princesa
_car
كلمة
princesa
_car

Using Text Contains

How to Color Code in Google Sheets - ModernSchoolBus.com
How to Color Code in Google Sheets - ModernSchoolBus.com

1. Select the cells you want to format. To apply the formatting to more cells in the future, make sure to adjust the range accordingly.

2. Go to the Format menu and click on Conditional formatting.

3. Under the 'Format cells if...' dropdown, choose Text contains.

Change Cell Colour using Conditional Formatting
Change Cell Colour using Conditional Formatting

4. In the 'Value or formula' field, enter the text you want the cells to contain. For example, if you want to color cells containing the word "princesa", just type princesa.

5. In the 'Formatting style' section, select the fill color you want to apply to the cells that match the condition. You can choose from the pre-defined colors or create a custom color.

6. Click Done to apply the conditional formatting. The cells containing the specified text will now be colored according to your formatting.

How to Highlight Duplicated Cells in Google Sheets
How to Highlight Duplicated Cells in Google Sheets

Using Regular Expressions

Google Sheets also supports regular expressions (regex) for more advanced text matching. Let's say you want to color cells containing words that start with "pr". You can use the regex ^pr.* to match any word starting with "pr". To do this:

The Ultimate Google Sheets Keyboard Shortcut Cheat Sheet for Beginners & Pros
The Ultimate Google Sheets Keyboard Shortcut Cheat Sheet for Beginners & Pros
Basic Formatting in Google Sheets
Basic Formatting in Google Sheets
Google Sheets: Alternate Colors - Teacher Tech with Alice Keeler
Google Sheets: Alternate Colors - Teacher Tech with Alice Keeler
How to Use Slicers in Google Sheets
How to Use Slicers in Google Sheets
How to hide cells in Google Sheets
How to hide cells in Google Sheets
How To Create A Circle Chart In Google Sheets
How To Create A Circle Chart In Google Sheets
How to Merge or Split Cells and Tables in Google Sheets
How to Merge or Split Cells and Tables in Google Sheets
Google Keep
Google Keep
three google sheets are shown with the words'3 google sheets formulas to know '
three google sheets are shown with the words'3 google sheets formulas to know '
Organelles Color Changing Digital Worksheet (AP Biology)
Organelles Color Changing Digital Worksheet (AP Biology)
How to Merge Cells in Google Sheets and Google Docs
How to Merge Cells in Google Sheets and Google Docs
Get Link to This Cell - Teacher Tech with Alice Keeler
Get Link to This Cell - Teacher Tech with Alice Keeler
How to Lock Cells in Google Sheets | ClickUp
How to Lock Cells in Google Sheets | ClickUp
How to Color a Drop Down List in Google Sheets
How to Color a Drop Down List in Google Sheets
Essential Google Sheets Text and Web Formulas for Productivity
Essential Google Sheets Text and Web Formulas for Productivity
How to Highlight a Row in Google Sheets Based on a Checkbox
How to Highlight a Row in Google Sheets Based on a Checkbox
Eight More Google Sheets Tips to Save You Time  – TCEA TechNotes Blog
Eight More Google Sheets Tips to Save You Time  – TCEA TechNotes Blog
Create a Beautiful Unique Dashboard in Google Sheets
Create a Beautiful Unique Dashboard in Google Sheets
Stop Feeling Lost: Labeled Visual Guide to Google Sheets Workspace
Stop Feeling Lost: Labeled Visual Guide to Google Sheets Workspace
An Introduction to Google Apps Script | Zapier
An Introduction to Google Apps Script | Zapier

1. Follow steps 1-4 from the previous section, but instead of entering plain text, enter the regex pattern.

2. Follow steps 5 and 6 to apply the formatting.

Regular expressions can help you create more sophisticated text-based conditional formatting rules. To learn more about regex patterns, check out the regex guide.

Color Scaling and Alternating Row Colors

In addition to text-based formatting, Google Sheets offers color scaling and alternating row colors features. These visual enhancements help you highlight important data and make your sheets more readable.

Color Scaling

Color scaling allows you to automatically adjust colors based on the values of your data. This is particularly useful when you want to visualize trends or patterns in your data. To apply color scaling:

1. Select the cells containing the data you want to format.

2. Go to the Format menu and click on Conditional formatting.

3. Under the 'Format cells if...' dropdown, choose Cell value.

4. In the 'Value or formula' field, enter your coloring rules. You can define minimum and maximum values, as well as more complex formulas. For example, enter Min=10, Max=100 to color cells based on their values between 10 and 100.

5. In the 'Formatting style' section, choose the color scale you want to apply. You can select from pre-defined color scales or create your own.

6. Click Done to apply the color scale to your data.

Alternating Row Colors

Alternating row colors is a simple yet effective way to improve the readability of your data by providing a cleaner structure. To apply alternating row colors:

1. Select the range of cells you want to format.

2. Go to the Format menu and click on Conditional formatting.

3. Under the 'Format cells if...' dropdown, choose Custom formula is.

4. In the 'Value or formula' field, enter the formula MOD(ROW(), 2) = 0. This formula uses the MOD function to check if the row number is even. If it is, it will apply the formatting.

5. In the 'Formatting style' section, choose the background colors you want to alternate. Typically, you would select two colors.

6. Click Done to apply the alternating row colors to your data.

And there you have it! With these step-by-step guides, you can now color cells in Google Sheets based on their text content, apply color scaling, and use alternating row colors to make your data more visually appealing and easier to understand. Happy formatting!

Related Articles

How To Remove Color In Excel Shortcut How To Mark Color In Excel Cell With A Formula How To Fill Color In Excel Keyboard Shortcut How To Sort By Color In Excel Table How To Fill Color In Excel Using Keyboard Shortcut How To Color Cells In Excel Based On Value Google Sheets How To Count Colored Cells How To Create A Formula In Excel To Color Based On Value What Color Is Turkey How To Sort Rows By Color In Excel