How to: Make Dynamic Dropdowns in Google Sheets

Gracia Kleijnen
Jul 28, 2020 · 5 min read

When you make additions in Google Sheets, are you often copy-pasting the same things over and over again? Can it be that you are accidentally overcomplicating things or doing more user actions than necessary?

There might be simpler ways to go about it all.

I will show you why I like to work with dynamic or dependant dropdowns in Google Sheets. And give you some tips to keep in mind when you’re playing with Google Sheets😉.

Let’s start with the first one.

⚠️Spoiler alert: No free template here. Just a how-to 😉

Tip 1: Are you repeating an action? Then it can probably be automated. This is a valuable lesson I learnt at one of the startups I worked at. Whenever I repeat myself, this lesson comes to mind, and off I go looking for ways to save myself clicks or user actions. This is not lazy, this is smart.

For the moment, you only take classes in two dance styles. To keep things fun, you take only beginners’ and intermediate classes.

I like to write out all values I use in other places in one tab. All other tabs will automatically pull data from this one tab. If I want to change something, I only have to do it here, once.

On to the template. In the tracking process, of course we can hand-write the days, level and styles of the classes. But this would violate almighty, golden rule and tip number 1. Why write it out each time in full when we can choose it from a dropdown?

Tip 2: Write out data or values that you will use in more than one place on one spreadsheet tab.

Let’s go to the tab for July.

If you want to make a dropdown, you would select a full column or row, scroll down to ‘Data validation’, choose ‘List of items’ and write out each item in here. I used to do this. The issue with ‘List of items’, is that when you need to make an adjustment to the list, you’d have to follow the same process again.

The steps I follow to create the dynamic dropdowns.

Instead of ‘List of items’, I prefer to choose ‘List from a range’.

Once in tab July, select the full column, starting from B2: Right-click, then scroll down to Data Validation. You will be brought to this box! Choose ‘List from a range’. Then, fill out the name of the tab you want to pull from, an exclamation mark, and the range you need.

In tab July column A, I selected the entire column, right-click, choose ‘Data validation’, and enter ‘List from a range’. I wrote the range Dropdowns!A2:A8. This means: from Dropdowns tab, copy all values in fields A2 until A8.

In tab July, column B, I do the same thing, up to the range. Here I fill out range Dropdowns:B2:B. This means: go to Dropdowns tab, and copy all values you find in fields B2, all the way down to B (if there are any).

Repeat for tab July column C. Of course, under List from a range, here you fill out Dropdowns C2:C.

Did you notice that for column A, I wrote out two values referring to exact fields (A2:A8), but for column B and C I want the range to take anything it finds from B2 or C2, all the way down the sheet? This is on purpose. There are only 7 days in the week, so I don’t need the range in column A to ever take more than 7 values from a range. In column B and C, I left it ‘open-ended’. Just in case I add more values, to the difficulty level, or when I decide to go wild and try out all kinds of dance classes.

It can very well be that we in future decide to challenge ourselves, and start taking advanced classes, or that we try out many more different types of dance.

When that time has come, we can easily add the name of the dance style to the ‘Dropdowns’ tab. Since we are pulling from a range that encompasses the full length of the columns in the Dropdowns tab, these new values will automatically appear in the July tab!

Before, there were only two. Then we added more styles. And they immediately appear in the dropdowns! Bliss.

Tip 3: When you’re customizing a monthly tab (or weekly), I suggest first finishing off how you’d like to have one tab. Once you’re done and happy with one tab, you can copy this tab for however many months or weeks you need it, and rename each one of them. Faster than making them from scratch.

Done making one tab pretty? Click on the downward arrow > Duplicate > Rename, and repeat for however many months or weeks you need ‘m.

In conclusion: I propose to always use dynamic dropdowns with List from a range. In case you need to adjust the values that will appear in your dropdowns, you’ll only have to change them in one location (that is your Dropdowns tab), and not manually go through every individual tab selecting ranges and repeating actions.

➡️ In case you want to play inside the Google Sheet, and add and remove and add values, go ahead in this link.

Ok ok, I know this was not a template. I thought it was useful to give you an example and show how you can apply such a dropdown, and save you a bit of time, the next time you have to craft yourself a template.

My question to you is as always: What would YOU like me to make? What do you need?

You came here just here for the free templates??😮

It’s one of the main reasons why this publication exists. A new template will eventually pop up. If you want to stay in the loop when it’s there, feel free to sign up for my newsletter.

Thank you for reading!

Google Sheets Geeks

Simple Google Sheets templates & how-to’s to help organise your life. Simple Google Sheets templates

Google Sheets Geeks

Simple Google Sheets templates & how-to’s to help organise your life. Receive free updates and templates straight into your inbox: http://bit.ly/GoogleSheetsGeeks-Newsletter

Gracia Kleijnen

Written by

Writing my way to progress. Topics: personal growth, life lessons, tooling & (failed) ventures. Owner: Google Sheets Geeks pub. https://bit.ly/GraciaWrites

Google Sheets Geeks

Simple Google Sheets templates & how-to’s to help organise your life. Receive free updates and templates straight into your inbox: http://bit.ly/GoogleSheetsGeeks-Newsletter