How to count characters in a cell in Google Sheets
Web agency » Digital news » How to Automatically Highlight Values ​​in Google Sheets

How to Automatically Highlight Values ​​in Google Sheets

Conditional formatting is an easy way to make numbers stand out in Google Sheets. You can automatically apply formatting like 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 changes to your sheet, that formatting is automatically applied. So as your values ​​change, you can see those numbers appear without any extra work.

We've shown you how to use date-based conditional formatting and how to use it for some text. So let's go back to the most basic use of the feature and automatically highlight values ​​with it.

Configure a conditional formatting rule

Go to Google Sheets, sign in, open the workbook you want to use, and choose a sheet. Any rules you configure apply only to the current worksheet.

Select the cells to which you want to apply the conditional formatting rule by dragging adjacent cells or by holding down Ctrl (Windows) or Command (Mac) while selecting nonadjacent 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 configure 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 list. You will see eight options for numbers like greater than, less than, equal to, not equal to, and between.

Criteria for numbers

For example, we'll choose Above to highlight sales above $500. After choosing the criteria, you will add the corresponding value(s) 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 wish. So you can choose both a bold font and a green cell color.

Bold font and green fill format

When you select formatting, you'll see your updated sheet for a nice preview of your selection. This allows you to make adjustments before saving the rule. When you are happy with the highlighting, click "Done" to apply the rule.

Preview rule in sheet

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

Conditional Formatting Rule with Sheet Changes

Examples of conditional formatting

Let's look at some other examples of value-based conditional formatting uses.

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 Is equals criterion with 400 as the corresponding value.

Equals rule 400

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

Is not between 100 and 500 rule

If you want to set up multiple conditional formatting rules 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 option

Edit or delete a conditional formatting rule

After setting up your rule, you can make changes to it or delete it altogether if you wish.

Go back to Format > Conditional Formatting to open the sidebar. You will see all the rules you have defined for this sheet.

  • To make a change, simply select the rule, edit whatever you want, and select "Done" to save it.
  • To delete a rule, hover your cursor 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 appear or try the color scale option.

★ ★ ★ ★ ★