Dropdown List in Google Sheets

Did you know that you could add a dropdown list of options to a cell (or¬†cells, plural) in Google Sheets?¬† This is nice for creating something like a “multiple-choice option,” but is also nice when you want to force your collaborators (or yourself!) to select from a specific list of choices.

It can be especially useful if you have formulas acting on that cell.  For example, if you were keeping track of a budget and wanted different things (i.e., adding versus subtracting) to happen if a row was marked as a deposit, purchase or interest.

Here’s an animated GIF about how to do it followed, as usual, by a list of the steps.

Sheets Dropdown Animation

Step 1: Click on the cell or cells that you plan to add the dropdown list to
Step 2: Right-click & select Data Validation
Step 3: In the dropdown by Criteria, select List of Items
Step 4: Type your options into the box, separating them with commas (I tend to alphabetize them)
Step 5: Click Save.

How Many Hot Dog Topping Combinations?

It was 6:12 PM EST. ¬†We were eating dinner on our deck. ¬†My sister messaged me. ¬†She had a very important question. ¬†Her and her colleagues were in a heated debate. ¬†Just how many topping combinations were there at Cleveland’s fun hot dog restaurants Happy Dog? ¬†I know, right? ¬†This is a big deal. ¬†Could I swoop in and save the day? ¬†Yes. ¬†Er, well, with the help of my trusty sidekick Google Sheets I could. ¬†(Excel would have worked, but what if I need to access the calculations on the go? ¬†or share them? ¬†Yup, I made the right choice. ¬†gSuite’s trusted cloud-based spreadsheet is the way to go here.)

So, I got the details.  There are 50 toppings possible.  No limits (you can do all 50, as my oldest son might choose) or minimums (0 toppings, as my youngest son prefers them, counts too).  Variations on the dog (veggie?  black bean!?) or bun (bleck, wheat?) were to be ignored.

I set right to it. ¬†I picked a trusty Google Sheets formula –¬†Combin¬†– and got to work. ¬†That formula deals with a common mathematics formula that finds the number of combinations of something. ¬†You need only know two things – how many possible things and how many are to be chosen (i.e., 50 toppings choose 1, 50 toppings choose 2, etc.). ¬†Now, don’t get this mixed up with permutations where order matters, because no one cares if you go peanut butter, sriracha, alien relish or alien relish, peanut butter, sriracha or … well … you get it.

COMBIN(n, k) where n is the size of the pool of objects to choose from and k is the number of objects to choose.

The rest is history.  Check it out in the GIF below.

Oh yeah, I almost forgot to tell you the answer:¬†1,125,899,906,842,620 –¬†one quadrillion, one hundred twenty-five trillion, eight hundred ninety-nine billion, nine hundred six million, eight hundred forty-two thousand, six hundred twenty combinations.

Side note to math teachers: I love how the numbers are symmetrical (i.e., there are 1,225 different 2-topping dogs and 1,225 different 48-topping dogs).  Could be a great discussion with math students.

Now, here’s how I did it:

Happy Dog Combinations Animations

Concat, Concatenate and &

Nope. ¬†They have nothing to do with cats. ¬†Sorry. ¬†If you’re here for the cat memes, you’re going to have to look elsewhere.

They are two Google Sheets formulas that are very useful if you’re organizing information in Google Sheets. ¬†Have first names in one column and last names in another, but need them combined? ¬†Concat¬†has your back. ¬†Have a student’s grade number, but need it to be written out (7th grade instead of just 7) for a mail merge? ¬†Concat is here to help.

Need to combine more than 2 things? ¬†i.e., first, middle & last name? ¬†Concatenate¬†can help you out. ¬†It’s the same as¬†Concat but it works for more than 2 items.

The & operator can perform the same tasks. ¬†It’s all personal preference.

Some tips are listed below the GIF.

Concat & Concatenate Animation

  • Basic format: =CONCAT(first thing, second thing)
    • =CONCATENATE(first thing, second thing, third thing, fourth thing….)
    • =first thing & second thing & third thing
  • The items in the formula can be cell references like here: =CONCAT(A1, B1)
  • The items in the formula can be regular text, just use quotation marks: =CONCAT(“me”, “ow)”.
  • When combining text strings, it puts them together with nothing in between them, so if you want a space, you’ll have to add it yourself – use ” ” if needed – the space is held between those quotation marks.

FILTER Formula in Google Sheets

You can filter using menu buttons and create filter buttons in Google Sheets, but sometimes it’s valuable to setup a FILTER formula. ¬†One such instance is shown in the GIF below: when you have a Sheet that contains data about many students across many grades or classes. ¬†Using a filter formula, you can create a tab for each class or grade. ¬†You can also create tabs for certain criteria (like lower scores that require follow-up).

=FILTER(range, condition1, [condition2, …])

Note from the formula above, that you can actually identify multiple criteria (such as Mr. Kotter’s students who scored below a 75%).

Filter Formula (Google Sheets) Animation