Creating Dropdown Choices in Excel and Google Spreadsheets
Data is very emerging today. It could move even in seconds in our life. There are many ways of collecting data. Traditionally, everyone writes their data, which will be input into the application to be processed. Right now, we easily fulfill data using an online form, such as google Forms, lime survey, etc. The big company doesn’t do that way, and their application already collects their consumer’s data. But let, don’t talk about it too far away. In this story, I want to share a very simple but make your Excel portfolio cool. Yeah, creating dropbox in excel or google spreadsheet.
For you, who don’t know dropdown, it’s a menu that restricts users from inputting only specific data. It makes our worksheet simple but powerful. It also helps the data processing steps to gain insight easier. If you create a form, a dropdown box always exists, but for usage in excel format, it’s unusual things. But I believe this will still be useful. Okay, let’s go.
The Data
I already created a worksheet to ensure we are in the same glass. Suppose we have data like this. Access Worksheet [Here].
The data above is just dummy data. As you can see, it contains five columns. Our task is to create a dropdown menu with the following requirements.
If you still can’t imagine our target, it will be like this.
The Gender, Role, and Status has a dropdown menu, as stated before. In this story, we want to create a dropdown menu to restrict users from adding more data that could decrease the data quality.
The Solution
From the title of this story, I want to share how to create a dropdown menu in Excel and Google Spreadsheets.
Microsoft Excel
1. For Microsoft Excel, the first thing you need to do is to block all the rows you want to add dropdown.
2. After the rows are selected, go to the Data tab.
3. On the right side, you will see Data Validation if you hover over it, you will see more information.
4. Click the Data Validation… like this.
5. It will prompt a new dialog box that looks like this.
6. On the Allow dropdown, find the List choices.
7. Put the values you want in the rows that we selected before. For the Status columns, we have to fulfill it with Online, Offline, and Vocation. Write it separated by commas. After that, click OK.
8. After the OK is clicked, you will see a small dropdown arrow in the rows below the Status columns. It contains the data we fill before.
9. Right now, a user only can choose specific values on that columns.
Feeling bored writing all values separated by commas? You also can block the selection of the value you want. In this example, I already created a Role containing four variables placed in columns J2:J5.
Block the rows you want to add the data validation; right now, at the dialog box, click the arrow up like this.
On the prompted dialog, select the rows as the value. You also can write it manually with the format, every character added with $, so if you have data located at J2:J5, you must write it into =$J$2:$J$5 .
You could review the data.
Right now, the Role is restricted to only four choices.
We can also delete the dropdown lists we already created by clicking Clear All after choosing all rows we want to clear out.
After the button is clicked, the data will be gone, and the dialog box will go to the default state.
As you can see above, the arrow for dropdown lists is gone.
Google Spreadsheets
The same goes for google spreadsheets; we can add the dropdown lists by clicking the Data tab and then choose Data validation after selecting rows that we want to create data validation lists.
Right now, there will be a coming Data validation rules window in the right section of the page.
If you click the + Add rule, you will see it will write your selected rows on the range page. Actually, it’s your choice whether you select the rows first or not.
Fill in the value as you need, like this.
Tadaaa, the dropdown lists are already created to choose Men or Women.
The specialty of Google Spreadsheets is we also can add color to every list we want to create.
Our worksheets will become like this.
Every rule we implement in the rows will be listed in the Data validation rules sections.
For removing the lists, we can remove them one by one or remove all.
The Conclusions
In this simple story, I was already sharing how to create dropdown lists in Excel and Google Spreadsheets easily. Maybe this is not the fancy tutorial you read. Still, I always try to improve how I write to ensure that everyone who reads this can easily understand and get the newest insight. I hope you got something useful from these stories. Thanks for reading. ✨
