Technology

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 like 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 automatically applies. So as your values change, you can see those numbers pop without any extra work.

We’ve shown you how to use conditional formatting based on date and how to use it for certain text. So let’s get back to the most basic usage of the feature 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 up 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 through adjacent cells or holding Ctrl (Windows) or Command (Mac) as you select 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 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

Next, pick the criteria you want to use in the Format Cells If drop-down box. You’ll see eight options for numbers like 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 higher than $500. After you pick the criteria, you’ll add the corresponding value(s) in the box beneath.

Greater than 500 criteria

Now you can pick the Formatting Style using things like bold, italics, or underline for the font or a fill color for the cells. You can also use a combination of styles if you like. So, you can pick both a bold font and a green cell color.

Bold font and green fill formatting

As you select the formatting, you’ll see your sheet update for a nice preview of your selection. This lets you make adjustments before you save the rule. When you’re happy with the highlighting, click “Done” to apply the rule.

Rule preview in the sheet

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

Conditional formatting rule with sheet changes

Conditional Formatting Examples

Let’s look at more example uses for conditional formatting based on values.

RELATED: How to Highlight Duplicates in Google Sheets

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.

Less than or equal to 50 rule

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

Is equal to 400 rule

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

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 a sheet

To add more rules, select “Add Another Rule” at the bottom of the sidebar for the current rule or when you first open it.

Add Another Rule options

Edit or Remove a Conditional Formatting Rule

After you set up your rule, you can make changes to it or delete it completely if you like.

Head back to Format > Conditional Formatting to open the sidebar. You’ll see all 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 remove a rule, hover your cursor over it in the list and click the trash can icon that displays.

Conditional formatting rules for a sheet

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



File source

Tags
Show More

Related Articles

Back to top button
Close