If you caught last week’s video tutorial that walked you through how to organize your Pinterest boards and pins using spreadsheets, you may have noticed that there were drop-down lists in the premium version.

Perhaps you thought to yourself how marvelously wicked drop-downs were.

Maybe you even got a wee bit of spreadsheet envy?

After all, you know that your own spreadsheets would seriously benefit from drop-down lists.

Drop-down lists can:

  • Save you time (less typing)
  • Increase your efficiency (less typing, less thinking)
  • Reduce error (no auto-populating the wrong thing, reducing grammatical errors)
  • Reduce the number of unnecessary, similar entries

So, why aren’t you using them?

My guess is that you are just simply not aware of how ridiculously easy they are to create.

Here – let me show you how.

Add some magic to your spreadsheets with easy-to-create drop-down lists.Click To Tweet

How to create drop-down lists in Excel

Option A: Create your list in a separate spreadsheet (don’t worry – you can hide this spreadsheet so that it doesn’t clutter up your workbook).

  1. Create a new spreadsheet and rename it drop-down lists.
  2. Add a descriptive phrase to describe your list in A1 (or B1, C1, etc.).
  3. In A2, list the value you want displayed at the top of your drop-down list.
  4. Continue on in this vein (e.g. enter the value you want displayed in the #2 spot in A3) until you have entered every item you want displayed in your list.
  5. Now, go back to your original spreadsheet and select the cell or range of cells where you want your drop-down menu to appear. Then, click ‘Data Validation’ (or ‘Validate’) in the ‘Data’ tab => choose ‘List’ from the drop down list under the ‘Allow’ field => click in the ‘Source’ field => go back to your ‘drop-down lists’ spreadsheet and select your list, excluding the header text => hit ‘Enter’ => click ‘OK’.

Option A considerations: Create separate drop-down lists for each location (but keep them in the same spreadsheet), even if the values are the same. This will make it easier to see at a glance what your drop-down lists are, and it allows you to change individual lists as needed.

Here is a gif tutorial of this process:

If you aren't using drop-down lists in your spreadsheets, why aren't you? Having drop-downs in your spreadsheets can save you time, increase your efficiency, and reduce errors. Plus, creating them is easy to do! In this post, I show you how to add drop-down lists to your spreadsheets, gif tutorials included!

Option B: Create your list within the cell or range of cells.

Select the cell or range of cells where you want your drop-down menu to appear. Then, click ‘Data Validation’ (or ‘Validate’) in the ‘Data’ tab => choose ‘List’ from the drop down list under the ‘Allow’ field => click inside the ‘Source’ field and type in each word or phrase for your drop-down list followed by a comma (do not add a space after the comma) => click ‘OK’.

Option B considerations: I recommend using this option for short drop-down lists.

Here is a gif tutorial of this process:

If you aren't using drop-down lists in your spreadsheets, why aren't you? Having drop-downs in your spreadsheets can save you time, increase your efficiency, and reduce errors. Plus, creating them is easy to do! In this post, I show you how to add drop-down lists to your spreadsheets, gif tutorials included!

How to create drop-down lists in Google Sheets

Option A: Create your list in a separate spreadsheet (don’t worry – you can hide this spreadsheet so that it doesn’t clutter up your workbook).

  1. Create a new spreadsheet and rename it drop-down lists.
  2. Add a descriptive phrase to describe your list in A1 (or B1, C1, etc.).
  3. In A2, list the value you want displayed at the top of your drop-down list.
  4. Continue on in this vein (e.g. enter the value you want displayed in the #2 spot in A3) until you have entered every item that you want displayed in your list.
  5. Now, go back to your original spreadsheet and select the cell or range of cells you want your drop-down menu to appear. Then, right-click ‘Data validation’ => select ‘List from a range’ in the ‘Criteria’ field => click the grid icon => click back to the spreadsheet => click on the ‘drop-down lists’ tab => select all of the cells in the list, excluding the header => ‘OK’ => ‘Save’.

Option A considerations: Create separate drop-down lists for each location (but keep them in the same spreadsheet), even if the values are the same. This will make it easier to see at a glance what your drop-down lists are, and it allows you to change individual lists as needed.

Here is a gif tutorial of this process:

If you aren't using drop-down lists in your spreadsheets, why aren't you? Having drop-downs in your spreadsheets can save you time, increase your efficiency, and reduce errors. Plus, creating them is easy to do! In this post, I show you how to add drop-down lists to your spreadsheets, gif tutorials included!

Option B: Create your list within the cell or range of cells.

If needed, select the cell or range of cells where you want your drop-down menu to appear. Right-click ‘Data validation’ => select ‘List of items’ and then type in each word or phrase for your drop-down list followed by a comma (do not add a space after the comma) => click ‘Save’.

Option B considerations: I recommend using this option for short drop-down lists.

Here is a gif tutorial of this process:

If you aren't using drop-down lists in your spreadsheets, why aren't you? Having drop-downs in your spreadsheets can save you time, increase your efficiency, and reduce errors. Plus, creating them is easy to do! In this post, I show you how to add drop-down lists to your spreadsheets, gif tutorials included!

How to force your spreadsheet users to only select items from your drop-down list

Both Excel and Google Sheets allow you to force users to only select choices from the drop-down list. If you would like to enable this option, in the data validation pop-up:

  • In Excel: Click the ‘Error Alert’ tab and choose ‘Stop’ in the ‘Style’ drop-down list, then enter whatever error message you’d like your user to receive if something invalid is entered.
  • In Google Sheets: Click the ‘Reject input’ option.

That’s it!

There are a few other options you have when it comes to how your drop-down lists behave – just play around with them in the ‘Data Validation’ box if you are curious.


Did you find this tutorial helpful? Do you have any questions about creating drop-down lists in your spreadsheet program of choice? Leave your questions or comments below!

Add some magic to your spreadsheets with easy-to-create drop-down lists.Click To Tweet

And, if you haven’t already done so, why not sign up now to receive my weekly spreadsheet tip, blog post teaser, special offers, and other goodies, plus access to all of DLS’s wicked freebies? Just click here or sign-up below 😉 Woo-hoo!