Learn how to Set Up Conditional Formatting in Google Sheets

With conditional formatting in Google Sheets, you can also make textual content or cells stand out primarily based on standards that you simply arrange. When your stock falls beneath a certain quantity, a invoice has handed its due date, or you’ve gotten empty cells from lacking information, you can also make these objects simple to identify.

Tip: use comparable conditional formatting guidelines in Excel.

Create a Conditional Formatting Rule in Google Sheets

For every of the rule varieties described beneath, observe the identical steps to create the conditional formatting rule.

Choose the cells you wish to apply the rule to, whether or not a column, row, cell vary, or the whole sheet. Click on Format -> Conditional formatting to open the Conditional Format Guidelines sidebar, the place you’ll arrange the rule.

On the high of the sidebar, choose the Single colour tab for one textual content format or cell fill colour or the Colour scale tab for gradient shading.

Notice: use the Single colour tab for all guidelines beneath, besides the final rule for making a colour scale.

Verify the cells within the Apply to vary subject, or choose the Grid icon to edit the vary.

Use these format guidelines to arrange conditional formatting primarily based on textual content, numbers, dates, empty cells, or a customized formulation. You’ll additionally learn to create a colour scale.

Create a Rule for Textual content

Maybe you’d wish to see cells that include particular textual content stand out, in case you’re on the lookout for emails, internet hyperlinks, cities, or names. You possibly can apply formatting to cells with textual content that comprises or doesn’t include, begins or ends with, or precisely matches sure textual content.

For instance, we wish to see these imported e-mail addresses which might be lacking the @ image.

Within the Format cells if drop-down field, choose Textual content doesn’t include, and enter “@” within the field beneath. Within the Formatting model field, select the cell colour or textual content format. We’re choosing a light-weight blue fill colour.

Click on Finished on the backside of the sidebar to use the rule.

The e-mail addresses that don’t include the @ image are highlighted.

Create a Rule for Numbers

Do you wish to spot one thing like gross sales beneath a certain quantity, bills above an quantity, or grades between two others? Format cells containing numbers which might be higher than, higher than or equal to, lower than, lower than or equal to, are equal to, aren’t equal to, are between, or aren’t between different numbers.

On this instance, we wish to spotlight stock that is lower than or equal to 100.

Within the Format cells if drop-down field, select Textual content doesn’t include, then enter “100” within the field beneath. Within the Formatting model field, choose the cell colour or textual content format. We’re selecting a crimson daring font colour.

Click on Finished to use the rule.

It reveals 4 stock quantities lower than or equal to 100.

Create a Rule for Dates

If you wish to rapidly see payments which might be late, duties which might be coming due, or objects you renewed previously month or 12 months, use conditional formatting for dates. Select dates that fall earlier than or after a day or are a precise match to a different date.

On this instance, we’re on the lookout for duties which have deadlines after tomorrow.

Within the Format cells if drop-down field, choose Date is after, and within the drop-down field beneath, select tomorrow. Notice you can additionally select actual date and enter the subsequent day’s date within the field beneath. It is a good choice if you wish to maintain the date static.

Within the Formatting model field, decide the cell colour or textual content format. We’re selecting each a crimson font and yellow fill colour.

Click on Finished to use the rule.

It reveals three upcoming duties which might be due later than tomorrow.

Tip: routinely add dates to Google Sheets paperwork. If in case you have a listing, you may as well type by date in Sheets.

Create a Rule for Empty Cells

While you share a Google Sheet with others or carry out an information import, you could possibly expertise lacking information. You could have cells that ought to include some type of information however are clean. To rapidly spot the blanks, arrange a conditional formatting rule to search out empty or not-empty cells.

For this instance, we imported gross sales from our nationwide places however are lacking some cities, states, and quantities.

Within the Format cells if drop-down field, select Is empty. Within the Formatting model field, choose the cell colour or textual content format. We’re selecting a purple fill colour.

Click on Finished to use the rule.

The info we’re lacking will be seen simply.

Create a Rule for a Customized Method

If you happen to’re accustomed to utilizing formulation in Google Sheets, think about benefiting from that function along with conditional formatting. This allows you to apply a format to cells primarily based on the formulation outcomes.

For a helpful instance, we’ve a to-do listing arrange in Google Sheets utilizing checkboxes for accomplished objects. To make the unfinished duties simpler to see, we’re crossing off and dimming the finished duties, primarily based on when the checkbox is marked.

Within the Format cells if drop-down field, decide Customized formulation is. Within the field beneath, enter your formulation. The formulation we’re utilizing is =($S2=TRUE), which interprets to the field in cell S2 is checked.

Within the Formatting model field, select the cell colour or textual content format. We’re choosing strikethrough and lightweight grey for the font.

Click on Finished to use the rule.

Our accomplished duties are crossed off and dimmed, whereas remaining duties keep the identical, to simply see that are incomplete.

Create a Colour Scale Rule

Yet one more helpful conditional formatting rule makes use of a colour scale. That is ultimate in case you’re making a warmth map the place the colours fluctuate primarily based on the values within the cells. You possibly can have a light-weight shade for decrease values and a darkish shade for the upper values, or vice versa.

On this instance, we’re utilizing a colour scale to show our state’s gross sales in various colours. These with the bottom gross sales seem white, common gross sales are in pink, and the best gross sales are in crimson.

Within the Colour scale tab, choose Preview to select the colour theme. We’re selecting the White to Crimson scheme.

You possibly can choose the low, center, and excessive worth varieties utilizing the Minpoint, Midpoint, and Maxpoint drop-down packing containers. This allows you to select minimal and most values, numbers, percents, and percentiles. Alternatively, you possibly can have Google Sheets decide this routinely.

If you happen to select one of many quantity codecs, enter the corresponding worth to the correct. You may also choose a selected shade to the correct.

Click on Finished to use the rule and see your colour scale or warmth map.

Delete a Conditional Formatting Rule

You possibly can simply delete a conditional formatting rule that was arrange in Google Sheets.

Choose a column, row, or cell within the formatted vary, and go to Format -> Conditional formatting within the menu. Click on the Delete (trash can) button subsequent to the rule within the sidebar.

Utilizing the Google Sheets conditional formatting function, it can save you time and your eyes from having to manually scour your sheet for information. Let a rule you arrange routinely format the cells or font so that you can make that information pop.

Picture Credit score: Pixabay. All screenshots by Sandy Writtenhouse.