Tech

How to add conditional formatting to a Google Sheets drop-down list (and why you should)


Black woman's hand typing on keyboard

Getty/Jamie Bake

Add drop-down list in Google Sheets is a simple trick that can make it exponentially easier for collaborators to populate data from a handy drop-down list (instead of having to enter everything manually). This can also prevent users from entering the wrong information in an important field in your spreadsheet.

Also: How to create a drop-down list in Google Sheets

You can use another nifty trick to help highlight these cells once a collaborator has created a section. That trick is called Conditional Formatting.

What is conditional formatting?

Simply put, conditional formatting formats a cell based on specific conditions. In the example I’ll show you, conditional formatting adds a background color to a cell based on the color selected from the drop-down menu. For example, if red is selected from the drop-down menu, the background color for that cell will automatically be red, you guessed it, red.

Of course, you’re not limited to just adding color to your cells, as you can also use other (basic) formatting, such as bold, italic, underline, strikethrough, and change the color. of text.

Conditional formatting also allows you to apply formatting based on certain conditions, such as:

  • If a cell is/not empty.
  • Contains (or does not contain) specific text or formulas.
  • Text starts/ends with specific text or formula.
  • Day.
  • Greater than, less than, or equal to.

You can add a single rule or even multiple rules to your conditional formatting.

Let’s see how this is done.

Add conditional formatting to the drop-down list

Remember, in the tutorial on adding a dropdown, we created a list of the following colors:

  • blue
  • red
  • green
  • yellow
  • black
  • orange
  • purple
  • white

Now we will use conditional formatting to set the background color of the cell based on the selected color. So if the collaborator chooses green from the dropdown, the background color of the cell will be set to green.

Simple.

Select the cell containing the drop-down menu, and then press Format > Conditional Formatting.

Google Sheets Format menu.

Conditional formatting is found in the Format menu in Google Sheets.

Photo: Jack Wallen/ZDNET

When you click on conditional formatting, you’ll see a new sidebar on the right side of the window.

Google Sheets Conditional Formatting sidebar.

I added two rules, one for blue and red.

Photo: Jack Wallen/ZDNET

Click + More rules to open the Rules editor.

Conditional Formatting rule editor.

The Conditional Formatting Rule Editor makes it simple to create new rules.

Photo: Jack Wallen/ZDNET

In the Value or formula area, type green, then choose green from the color picker.

Conditional Formatting color picker.

Add the appropriate blue color to our conditional formatting rule.

Photo: Jack Wallen/ZDNET

Now the rule is ready. Continue creating more rules until you have a rule for each color in your drop-down list.

Now, when the user selects a color from the dropdown, the background color will automatically change based on the condition.

Conditional formatting as applied to a cell in Google Sheets.

Select green and the background color will turn green.

Photo: Jack Wallen/ZDNET

And that, my fellow Google Sheets users, is how you add conditional formatting to a drop-down list. You’re not limited to the drop-down menu for this feature as you can apply it to any type of call (and even a range of cells). Enjoy the fresh taste of automatic formatting.

news7f

News7F: Update the world's latest breaking news online of the day, breaking news, politics, society today, international mainstream news .Updated news 24/7: Entertainment, Sports...at the World everyday world. Hot news, images, video clips that are updated quickly and reliably

Related Articles

Back to top button