How to Do Conditional Formatting in Google Sheets

As a digital marketer, I rely heavily on spreadsheets for data organization and analysis. One of my favorite features in Google Sheets is conditional formatting. This tool allows you to highlight cells that meet specific criteria, making it easier to identify patterns and trends in your data.

Step 1: Select the Data Range

Start by selecting the data range you want to apply conditional formatting to. This can be a single cell, a row, a column, or an entire table. Click on the first cell of your selected range, hold down shift, and click on the last cell to highlight the entire range.

Step 2: Open the Conditional Formatting Menu

With your range selected, click on "Format" in the top menu bar and select "Conditional formatting" from the dropdown menu.

Step 3: Choose a Formatting Rule

Next, choose a formatting rule that best suits your needs. You can select from a variety of pre-set rules including:

  • Color scale: Highlight cells with color gradients based on their values
  • Text contains: Highlight cells that contain specific text or characters
  • Date is: Highlight cells that meet a particular date or time criteria
  • Custom formula is: Create a custom formula that determines which cells to highlight

Step 4: Define the Formatting Style

Once you've chosen your rule, you can customize the formatting style to match your preferences. This might include changing the font color, adding borders, or adjusting the cell background color. Take some time to experiment with different styles until you find one that works best for you.

Step 5: Apply the Formatting Rule

When you're ready, click "Done" to apply the formatting rule to your selected range. From now on, any cells that meet the criteria you defined will be highlighted with the formatting style you selected.

Examples of Conditional Formatting

Now that you know how to apply conditional formatting, let's take a look at a few examples of how it can be used.

Highlighting Goals Scored in Soccer Matches

Imagine you have a spreadsheet that tracks the scores of soccer matches. You want to highlight any matches where more than three goals were scored. Here's how you would do it:

  • Select the column that contains the "goals scored" data
  • Click "Conditional formatting" and choose "Custom formula is"
  • Enter the formula =B:B>3 (assuming the "goals scored" data is in column B)
  • Select your desired formatting style and click "Done"

Now, any matches with more than three goals will be highlighted in your chosen style.

Color-Coding Priority Tasks

Let's say you have a list of tasks that you need to complete, but some are more urgent than others. You can use conditional formatting to color-code the tasks based on their priority:

  • Select the column that contains your task names
  • Click "Conditional formatting" and choose "Text contains"
  • Enter the keyword for your high-priority tasks (e.g. "URGENT")
  • Select your desired formatting style and click "Done"

Now, any tasks with the keyword "URGENT" in their name will be highlighted in your chosen style.

Tips for Using Conditional Formatting

Here are a few tips to keep in mind as you start using conditional formatting in your own spreadsheets:

Be Selective

While conditional formatting can be a powerful tool, it's important not to overuse it. Keep in mind that too much formatting can make your spreadsheet harder to read and understand.

Stay Consistent

If you're using conditional formatting to highlight certain types of data, be sure to stay consistent in your approach. This will help avoid confusion and make it easier to spot patterns.

Experiment with Different Styles

Don't be afraid to try out different formatting styles until you find one that works best for your needs. You might be surprised at how much of a difference a simple change in color can make!


Conditional formatting is a powerful tool for organizing and analyzing data in Google Sheets. By taking the time to select the right formatting rule and style, you can make your spreadsheets more easy to read and understand. Give it a try and see how it can help you in your own work!
By clicking “Accept”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. View our Privacy Policy for more information.