How to make a dropdown list in Google Sheets

How to make a dropdown list in Google Sheets-1

Google Sheets is a free online version of Excel that is very useful for sharing, editing on the fly and light spreadsheet work. Sheets work much the same as in Excel and look remarkably similar. This is good news for anyone more familiar with Excel and getting to grips with Google Sheets won’t take long at all. For example, if you want to make a dropdown list in Google Sheets and already know how to do it in Excel, the process is almost identical.

One of the more powerful spreadsheet tools is the dropdown box. You can turn a simple spreadsheet into an interactive questionnaire, customer feedback form or whatever you like with dropdown boxes as they allow dynamic input from whoever you allow access to.

How to make a dropdown list in Google Sheets-2

Make a dropdown list in Google Sheets

Create a Sheet containing whatever data you need it to include. Then:

  1. Highlight the cells you want to include in the dropdown list by dragging the mouse or using Shift or Ctrl keys.
  2. Click the Data tab and then Validation, or right click the selected cells and click Data validation.
  3. In Cell range, add the cell coordinate(s) where you want your dropdown to appear.
  4. Select Create list from range and then the little cell box next to it.
  5. Manually select the data you want to appear inside the dropdown list here and click OK.
  6. Add Help text and error information if you like and click Save.
  7. The dropdown list should appear in the cell you entered in step 3.

As you can see, creating a dropdown list in Google Sheets is much easier than in Excel!

If you’re creating a customer facing Sheet, it might be useful to create a second sheet that contains the data you want to include in the dropdown list. This keeps the main sheet clean and tidy and separates the user from the data as much as possible.

How to make a dropdown list in Google Sheets-3

  1. Create your customer facing Sheet 1 and another Sheet 2 for data.
  2. Highlight the cells you want to include in the dropdown list by dragging the mouse or using shift or Ctrl keys on Sheet 2.
  3. Click the Data tab and then Validation, or right click the selected cells and click Data validation.
  4. In Cell range, add the cell coordinates on Sheet 1 where you want your dropdown to appear. This will presumably be next to the question requiring the response.
  5. Select Create list from range and then the little cell box next to it.
  6. Manually select the data you want to appear inside the dropdown list here and click OK.
  7. Add Help text and error information if you like and click Save.
  8. The dropdown list should appear in the cell on Sheet 1 you entered in step 3.
  9. Protect Sheet 2 by selecting Data and then Protect sheet.
  10. Fill in the form on the right by giving it a name, select Sheet 2 by selecting Sheet instead of Range and Sheet 2 from the dropdown box. Then Set permissions and click Save.

More help for Google Sheets can be found on the Docs editors help website here.

Posted by Jamie on November 11, 2016

Leave a Reply

Your email address will not be published. Required fields are marked *


TechJunkie Also Recommends