How to Add Colorful Drop-Down Lists in Google Sheets

Photo of author
Written By Chris

Just a simple "data guy". Changing the world one formula at a time.

Whether using Google Sheets or Excel, I’ve found immense value in utilizing drop-down lists for organizing and simplifying my work. But what if I told you that you can enhance these lists further by adding color to make them even more functional and easy to understand? 

Sounds exciting, right? Let me introduce you to the world of color-coded drop-down lists in Google Sheets!

So, the other day I was working on reconciling my expenses. Oh, so fun, isn’t it? Well, thanks to my growing anxiety, I realized that with so many categories, it was becoming difficult to visualize and distinguish between them. 

We’ve all been there. Each row seems to run together. The Amazon purchases (pet food category) start blending with the monthly Netflix bill (entertainment category). That’s when I decided that it was time to add a bit of color to my sheet before I started making mistakes.

Creating a Google Sheets drop-down list with color only requires two steps: select the cells with your list options and customize your colors with conditional formatting.

If a little dynamic configuration tends to scare you off, don’t let it. It’s easier than it sounds and significantly improves your interaction with your Google Sheets. Trust me! 

Now, sign in to your Google Sheets account and follow along as I show you.

Creating a Drop Down List in Google Sheets

Not going to lie; I’m really excited to show you how easy it is to create a drop-down list in Google Sheets! It’s a quickly learned skill you can use to organize your own data or show off at work with a spreadsheet makeover.

You can duplicate the exact steps I’m about to show you with your own sheet or download the file I’m using if that’s easier.

my drop down list

If you’re using my practice sheet, you’ll see a small grouping of The Office characters. I have six characters I want to rate: my favorite to least favorite. My coworkers are as fanatic about the show as I am, so why not share it and compare?

But knowing how these things usually go, I’ve created a drop-down list in column F so the ratings are consistent. Otherwise, we’ll end up arguing over the point conversion of 2.5 versus a thumbs up. 

By the way, do you like my five-star rating? I’ll tell you how to create your own at the end.

Selecting the Data Range

Let’s get started already.

First, I’ll need to select a cell or a range of cells where I want to insert the drop-down list. This is important because the drop-down list will only appear in the selected cells. I don’t want my list popping up in every cell, right?

To do this, I’ll simply click on a desired cell or click and drag to select multiple cells. In this case, I’ve chosen cells B7:B12 since that is where the ratings should be entered.

selecting a range for drop down options

Using Data Validation

For the second step, with the desired cells selected, I’ll proceed to the “Data” menu at the top of the Google Sheets window. From there, I’ll click on the “Data Validation” option. 

selecting the data validation

A new window appears on the right side, prompting me to add a new rule to set up my drop-down list. There are several criteria to help me create a customized list. I could go with text values, number values, or the two popular options of Dropdown from a range or Dropdown.

Dropdown From a Range

If choosing the Dropdown from a range, click on the table icon to the right and highlight your desired range to fill your list. For those following along with my practice sheet, you’ll find your cells ready for you between F2:F6.

selecting your range for list

After selecting your range and locking them in, go ahead and make sure the radio button to reject inputs under the Advanced options is selected. This will prevent users from entering a value not found in your list. Which is kind of the whole point, right?

You can also select a display style for your drop-down. I’ve chosen Arrow, which is a little more visually appealing, I think.

select display option

Now click done and marvel at what you’ve created!

Dropdown – List of Items

If you don’t have a range of selections prepared, you can create your own on the spot by opting for the criteria, Dropdown. This lets you enter your desired list by adding as many new unique items as you want. 

create your own list

Click Done and check that your list shows as expected and where you expected it to be. 

That’s it! You’ve created a nifty drop-down list in Google Sheets. But let’s add that splash of color you were looking for.

Coloring the Drop Down List

There’s something fun and exciting about adding color to my drop-down lists. Is that nerdy of me to say? So be it. 

Looking at rows of data all day can be as adrenaline-inducing as filing my taxes. Why not let the creative juices flow and add a little of yourself to your sheet?

Now, you may have noticed when creating the drop-down list that you could have chosen a bit of color for each item. But I’ve got a better idea. Time to bring some vibrancy to our drop-down lists!

Conditional Formatting Rules

conditional formatting rules

To start, we’ll need to set up conditional formatting rules. These rules allow Google Sheets to automatically apply different colors to our list of items based on specific conditions. 

Not only does this save time and effort, but it also makes the sheet easier to understand at first glance. It’s a real game-changer!

Fear not, you worrywarts; the process is simple. 

Generally, you’d start by selecting the range where the drop-down lists are located. Next, in the Format menu, choose Conditional Formatting. The possibilities here are endless, as I can create multiple rules based on various criteria, and Google Sheets will take care of the rest!

Let me show you how. I know you love my five-star rating, but let’s add another layer by giving our character names something extra to compliment their score.

Applying Colors Based on Selections

Understand that the following steps may be more complicated if you aren’t familiar with the lesser-known formulas in worksheets. But my color coding drop-down lists’ setup process is no different than applying conditional formatting for rules without custom formulas.

What I want to do is to apply a color scale to the rows based on the character rating. With my Google Sheets document still open, I perform the following steps:

  1. Select the range of cells containing the list of names and the five-star rating values, A7:B12.
  2. Click on the “Format” menu and select “Conditional formatting.”
  3. In the “Conditional format rules” sidebar, select “Single color” from the top menu. Not “Color scale.”
select single color

Unfortunately, I can’t set the color scale using this option since I chose a symbol instead of a clearly defined value for my rating. But if you’re using a different rating, like 1 through 5, you can use color scale formatting.

  1. In the “Apply to range” field, ensure the range of cells you want to apply the color scale to, A7:B12, is showing.
  2. In the “Format cells if” dropdown menu, select “Custom formula is.”
  3. In the “Value or formula” field, enter the following formula: =REGEXMATCH($B7,”★★★★★”)
  4. Select the color you want to use. For the top rating, I chose a darker green.
darker green for a top rank
  1. Next, click the “Add another rule” option. Your selections are the same as before, so delete one of the stars in your formula and select a different color.
  2. Repeat this step 3 more times to choose a unique color for each drop-down.
  3. Click on the “Done” button.
all my color lists

This formula uses the REGEXMATCH function to check if the value in the next column contains one to five stars symbols (represented by the “★” character). If it does, the conditional formatting with its chosen color will be applied to the row of names and ratings. 

It’s up to you to decide on a color scale, but I chose a simple traffic light pattern. Green is best, and red is my least favorite.

Let The Show Begin

Now that we’ve set up the conditional formatting rules let’s see how they work in action. Whenever I make a selection from the drop-down list, the cell’s color in both columns changes automatically based on the conditional formatting rule I created. Talk about a visual treat!

let the show begin

But wait a second! I’ve got six characters and only five different ratings. No worries, let’s add two more options to our drop-down list. One for a six-star rating and another so we can change back to an empty cell. It just makes our chart more well-rounded.

Edit your Drop-down list in Google Sheets

To edit and add to your drop-down list, do this:

  1. Go back to your drop-down list options in column F and copy and paste F2 to F7.
  2. Copy the single star in F6, click F7, and place the cursor at the end of the five stars in the formula bar.
  3. Hit CTRL + V to paste the last star.
  4. In cell F8, enter an apostrophe ( ‘ ). Don’t worry about it showing in your list. In Google Sheets, it just lets the data validation know you want this value to be blank.
editing and adding to drop down list
  1. Now, select “Data validation” under the “Data” tab to see your existing range. Click on it to open.
  2. Click the table icon under “Dropdown (from a range)” and highlight cells F2:F8, then hit Ok. 

You’re not quite done yet. 

  1. Go ahead and select the range of cells for your character ratings. Under the “Format” tab, select “Conditional formatting.”
  2. Click the “Add another rule” option, and under “Format cells if,” change the drop-down to “is empty” and the color to none.
  3. Add another rule, select “Custom formula is,” and input your formula with =REGEXMATCH($B7,”★★★★★★”)
  4. Change the color to something not already selected.
  5. Hit Done.

Just an FYI on a little hiccup with Google Sheets… 

Google Sheets processes rules in order of priority. So if your six-star rating is last in your conditional format rules list, your chart’s color won’t appear as expected.

The good news is you can reorder your rules by simply dragging them up or down within the conditional format rules window.

reorder your list for priority

With colors corresponding to specific items, I can easily analyze my data and draw conclusions quickly. It also ensures that my colleagues and I are on the same page when discussing reports. 

In this case, I can avoid the messy business of converting different values of rating (check marks to smiley faces).

But ultimately, a well-organized and visually appealing sheet leads to better workflow and productivity, making it a fantastic addition to my reporting toolkit.

So, what do you think of my ranking? And don’t get me wrong, I love Andy! But someone has to be last.

what do you think of the final product

Final Thoughts on Google Sheets Drop-Down Lists

Google Sheets provides a great way to customize the appearance of our drop-down lists. This helps us to efficiently organize data and spot important information at a glance. 

Hopefully, I’ve helped you gain some comfort with using them. Feel free to experiment and ask for help when you need it. I try to respond to emails when I can. Or, if you have a tip to share I didn’t mention, leave them in the comments below.

And before you go, review some of the quick tips below to learn how to enhance your drop-down lists beyond their structure and top-layer appearance.

Tip #1: Try customizing the font and size of your drop-down lists to make them more readable. 

Select the cell or range with the drop-down lists, and then use the toolbar at the top of the screen to select a new font style and size that suits your needs.

Tip #2: Add borders and background colors to visually segment your data or highlight certain portions that require attention. 

Select the cell or range containing the drop-down lists, click on the ‘Borders’ icon in the toolbar to apply your desired border style and thickness, and then click on the ‘Fill Color’ icon to choose a background color for your selection.

adjust your borders of your chart

Tip #3: Use named ranges to quickly and easily reference a specific cell range throughout your spreadsheet.

Once you’ve set up named ranges, you can use them in your drop-down lists to make them more flexible and easier to manage.

Tip #4: Employ array formulas to apply a single formula to an entire range of cells, saving you time and effort.

This functionality can also be applied to your colored drop-down lists.

Tip #5: Connect your drop-down lists to external data sources to create diverse and dynamic lists that are always up-to-date. 

Use various Google Sheets functions, such as IMPORTRANGE, IMPORTHTML, and IMPORTDATA, to import data from another Google Sheet, a website, or even an API.

Tip #6: Finally, double-check your data validation source range and conditional formatting rules if you encounter common issues, such as incomplete lists or incorrect colors (as I mentioned above). 

And make sure to select colors that are easily distinguishable from one another to avoid confusion while using your drop-down list. Otherwise, you’re going from boring to bewildered.

Inserting Stars into your Google Sheet

I almost forgot! If you’re interested in creating a file with the same stars found in my practice sheet, Google Sheets doesn’t let you insert this automatically as Excel does.

What you can do is open up a Google Doc, and under the “Insert” tab, click on “Special characters.” In the new window, type in star in the search box and select the one you want.

creating a special character from Google Docs

Now you only have to copy it within the Google Doc and paste it into a Google Sheets cell.

Finito!

Leave a Comment