close
close

How to Automatically Highlight Values ​​in Google Sheets

An easy way to make numbers stand out in Google Sheets is with conditional formatting. You can automatically apply formatting such as a bold font or cell color when certain values ​​meet your criteria.

One thing that makes conditional formatting a great option is that when you make edits to your sheet, that formatting is applied automatically. So as your values ​​change, you can see those numbers appear without any additional work.

We show you how to use date-based conditional formatting and how to use it for certain text. So let’s go back to the most basic use of the function and automatically highlight values ​​with it.

Set up a conditional formatting rule

Visit Google Sheets, sign in, open the workbook you want to use, and choose a sheet. Any rules you set apply only to the current spreadsheet.

RELATED: How to highlight a row in Google Sheets using conditional formatting

Select the cells you want to apply the conditional formatting rule to by dragging adjacent cells or by holding down Ctrl (Windows) or Command (Mac) while selecting non-adjacent cells.

Selected cells in Google Sheets

Go to the Format tab and choose “Conditional Formatting”.

Conditional formatting in the Format menu

This opens a sidebar on the right for you to set up a rule. Select the Single Color tab at the top and confirm the cells in the Apply to Range box.

Apply to Range field

Then choose the criteria you want to use from the Format Cells If drop-down box. You will see eight options for numbers such as greater than, less than, equal to, not equal to, and between.

Criteria for numbers

As an example, we’ll choose Greater Than to highlight sales greater than $500. After choosing the criteria, you will add the corresponding values ​​in the box below.

More than 500 criteria

You can now choose the formatting style using things like bold, italic, or underline for the font, or a fill color for the cells. You can also use a combination of styles if you like. Therefore, you can choose a bold font and a green cell color.

Bold font and green fill formatting

As you select the format, you’ll see your sheet update to give you a good preview of your selection. This allows you to make adjustments before saving the rule. When you’re happy with the highlight, click “Done” to apply the ruler.

Ruler preview on sheet

You should see the cells that meet your criteria highlighted. And, if you make changes to those cells that affect the criteria, they update automatically.

Conditional formatting rule with sheet changes

Conditional Formatting Examples

Let’s look at more example uses for value-based conditional formatting.

RELATED: How to highlight duplicates in Google spreadsheets

You can use the Less Than or Equal To criteria to highlight the smallest values ​​in the range. Here we can see our lowest sales, those equal to or less than 50.

Rule less than or equal to 50

Maybe you want to see specific values. Here, we want to see all sales that are exactly $400 using the Equals criteria with 400 as the match value.

is equal to rule 400

You can use the Not Between criteria to detect those numbers outside of a range. Here, we enter 100 and 500 to find those values No between those numbers. This allows us to see our highest and lowest sales at the same time.

It is not between 100 and 500 rule

If you want to set up more than one conditional formatting rule for your sheet, you can do that too. Here we have our highest values ​​with a green fill color and our lowest with red.

Two conditional formatting rules on one sheet

To add more rules, select “Add another rule” at the bottom of the current rule’s sidebar or when you first open it.

Add Another Rule Options

Edit or delete a conditional formatting rule

After setting up your rule, you can modify it or remove it entirely if you wish.

Go back to Format > Conditional Formatting to open the sidebar. You’ll see all the rules you’ve set up for that sheet.

  • To make a change, simply select the rule, edit what you want, and select “Done” to save it.
  • To delete a rule, hover over it in the list and click the trash can icon that appears.

Conditional formatting rules for a sheet

Conditional formatting in Google Sheets gives you a great way to see the values ​​you want at a glance. You can make negative numbers stand out or try the color scale option.

Leave a Comment