javascript hit counter
Business, Financial News, U.S and International Breaking News

Learn how to add conditional formatting to a Google Sheets drop-down record


Picture: James Martin/CNET

I just lately wrote a tutorial on including a drop-down record in Google Sheets. With this straightforward trick, you can also make it exponentially simpler for collaborators to fill in knowledge from a useful record of choices (as an alternative of getting to kind every thing manually). This could additionally stop customers from coming into the flawed info right into a vital area in your spreadsheet.

You should utilize one other nifty trick to assist make these cells stand out as soon as a collaborator has made a piece. That trick known as Conditional Formatting. 

Merely acknowledged, conditional formatting codecs a cell based mostly on particular circumstances. Within the instance I’ll present you, the conditional formatting will add a background shade to a cell based mostly on the colour chosen from the drop-down. For example, if the colour pink is chosen from the drop-down, the background shade for that cell will mechanically be, you guessed it, pink. 

In fact, you are not restricted to solely including colours to your cells, as you can even use different (fundamental) formatting, reminiscent of daring, italics, underline, strikethrough, and alter the colour of the textual content. 

Conditional formatting additionally permits you to apply the formatting based mostly on a number of circumstances, reminiscent of:

  • If a cell is/just isn’t empty.
  • Accommodates (or doesn’t comprise) particular textual content or formulation.
  • Textual content begins/ends with particular textual content or formulation.
  • Dates.
  • Better than, lower than, or equal to.

You possibly can add a single rule and even a number of guidelines to your conditional formatting. 

Let’s have a look at how that is executed.

Including conditional formatting to a drop-down record

Bear in mind, within the tutorial for including drop-down lists; we created an inventory of the next colours:

  • blue
  • pink
  • inexperienced
  • yellow
  • black
  • orange
  • purple
  • white

We’ll use conditional formatting to set the cell’s background shade based mostly on the colour chosen. So, if a collaborator chooses inexperienced from the drop-down, the cell background shade will likely be set to inexperienced.


1. Open the conditional formatting rule editor

Choose the cell that accommodates the drop-down after which click on Format > Conditional formatting (Determine 1).

The Google Sheets Format menu.

Determine 1: Conditional Formatting is discovered within the Formatting menu in Google Sheets.

Picture: Jack Wallen

Upon clicking conditional formatting, you will see a brand new sidebar on the fitting aspect of the window (Determine 2).

The Google Sheets Conditional Formatting sidebar.

Determine 2: I’ve already added two guidelines, one for blue and pink.

Picture: Jack Wallen

2. Create a brand new conditional formatting rule

Click on + Add one other rule to open the Rule editor (Determine 3).

The Conditional Formatting rule editor.

Determine 3: The conditional formatting rule editor makes it easy to create new guidelines.

Picture: Jack Wallen

You must have already got the right cell listed within the vary. From the Format cells, drop-down, choose Textual content accommodates. Within the Worth or formulation space, kind inexperienced after which choose the colour inexperienced from the colour picker (Determine 4).

The Conditional Formatting color picker.

Determine 4: Including the correct shade of inexperienced for our conditional formatting rule.

Picture: Jack Wallen

As soon as you have chosen the colour, click on Performed, and the rule is prepared. Proceed to create extra guidelines till you may have one for every shade in your drop-down. 

Now, when a consumer selects a shade from the drop-down, the background shade will mechanically change based mostly on the situation (Determine 5).

Conditional Formatting as applied to a cell in Google Sheets.

Determine 5

Choose inexperienced and the background shade will change to inexperienced.

Picture: Jack Wallen

And that, my fellow Google Sheets customers, is the way you add conditional formatting to a drop-down record. You are not restricted to drop-downs for this function, as you possibly can apply it to any kind of name (and even a spread of cells). Benefit from the recent style of automated formatting.

Extra how-tos

This text was initially revealed by Learn the authentic article right here.

Comments are closed.