How to Create Drop Down Lists in Excel

Last week we began our new Excel series by explaining how to combine text quickly, and today we’re moving forward with a few more tips and tricks for you. 

We know Excel is still a struggle for many people. While we can’t offer a full Excel for dummies type of training here in our blog, we hope that this series helps you learn a few things that can help you right away. 

How do I create Drop Down Lists in Excel?

Have you ever wondered how to put a dropdown list in an Excel spreadsheet? Data validation is the way to do it. 

Start by listing all the data you want available in the dropdown list. Then you’ll select the cell (or cells) you want to use the list. How you get to it will depend on which version of Excel you are using, but you’ll want to Validate Data. A box will pop up. 

In the settings field you’ll select List, and then choose the source for the list (the list of data you entered previously). 

If you’d like, you can create an input message to appear.

 You can also change the default error message that pops up when someone tries to enter something other than the designated values in the cell.

It typically isn’t necessary to change the message or the alert, unless the directions are particularly tricky. All you really need is the settings tab to create a dropdown list. 

How do I quickly transpose data from a row to a column? 

Sometimes you spend time setting up an Excel spreadsheet only to realize partway through that it would look better if the data was displayed in columns instead of rows. When this happens, many people simply delete what they have and start over. 

But that’s the worst way to do it! You’ve already done a lot of the work, you just need a way to move all that data, right? 

You can do that by transposing it into columns. 

Copy the data you want to transpose.

Now, move the cursor to a blank location and select Paste Special. Choose Transpose. 

And look, all your data has been quickly moved exactly where you want it. 

How do I change the case of text? 

When you work with data compiled by others, there are usually things you need to change. For example, you might need text in all caps but it wasn’t provided that way. 

To capitalize all text, enter =UPPER(cell)

To convert text to all lowercase, enter =LOWER(cell)

To convert from all caps or lowercase to only the first letter capitalized, enter =PROPER(cell)

Pretty simple, right?

Excel can make your job so much easier once you understand how it works. That’s why we highly recommend taking full Excel training courses. You can learn advanced Excel skills here at Illumeo, but if you aren’t quite ready for that, follow along with this series and we’ll continue offering some basic Excel tips and tricks for you.