Create a drop down selection in Excel to customize cell data

Microsoft Excel is more than just a program to create lists and tables. It's way more powerful than that but for most of us we will never use all the advanced features Excel has to offer. To master Excel would take a lot of studying and practice and most people never get there.

If you are like the most of us and like to make lists and tables in Excel then there is a neat feature you can use if you have a lot of information that will be repeated or if you want to only allow certain information to be entered in a particular cell, row or column. This comes in handy when you have a spreadsheet that will be filled out by other people and you want to limit what goes into the cells and have it be only specific values.

This feature is commonly known as a drop down list and is relatively easy to setup and modify if needed. The first thing you should do is create your spreadsheet the way you want it will all the columns you need in place or at least the ones you want to have drop down choices for. Then you need a place to store the field choices for those columns. We find it easier if you make a separate sheet just to store this listing of field choices. Our main sheet is called Employees and our field information sheet is called Data and we want to have a drop down list for a column called Department.

Here is our main sheet with the Department column ready to be configured with a drop down list.

Microsoft Excel Drop Down List

Then we have our Data sheet with the column heading and department listings we want to use. You don't have to use the column heading in your data sheet unless you have multiple drop down lists and want to make them easier to keep track of.

Microsoft Excel Drop Down List

Microsoft Excel Drop Down List

Now it's time to configure the drop down list for the Department column. To do this simply highlight the cells that you want the drop down list to apply to and then click on the Data tab and then on the Data Validation icon and then choose Data Validation from the drop down menu. Next you will select List from the Allow menu and click on the source selection icon underlined in red below.

Microsoft Excel Drop Down List

Then go to your Data tab and highlight the cells in the Department column to add that range to the source. Then click on the source selection icon again and then click OK.

Now when you go back to your main sheet you will notice that when you click on a cell there will be a drop down arrow and the items from your Data sheet will be populated within the drop down options.

Microsoft Excel Drop Down List

If you need to add, remove or change the drop down items simply go back to the data sheet and make your changes. You can even sort them alphabetically etc. and they will be sorted in your dropdown list as well. If you add more items than you originally configured when you made your source selection you will have to redo the selection to add the extra cells so it will know to use them in your drop down.

If someone tries to enter a department etc. manually they will get an error that says “The value doesn't match the validation restrictions defined for this cell.”

Microsoft Excel Drop Down List

This is there so they can't just add things you don’t want which would defeat the purpose of having the drop down list to begin with.

Pin It

Join Us On FaceBook

Join Us On Twitter

Get insights into the computer industry and regular updates on our site. Click Here

OCT Youtube Channel

New tech tip videos posted on a regular basis. Subscribe today! Click Here

Sponsored

LATEST VIDEOS

Try out some interesting free software from Kaspersky ...

Learn How to Create a RAID 1 Mirrored Drive Set ...

Use the ipconfig command to troubleshoot network problem...

Avoid having to put in your password when you start your...

Kill stubborn tasks that refuse to die with Taskkill ...

Find out how much information Microsoft is gathering fro...

RECENT TIPS

Another utility you can use to fight off spyware As you...

Get those programs uninstalled completely with Revo Unin...

Make your computer easier to use with the Windows Ease o...

Learn the history of wireless technology and how it work...

Try out some interesting free software from Kaspersky ...

Tweak Windows 10 to have it work the way you want it to ...

NEWS

Microsoft to release Windows 10 Pro for Workstations If...

Microsoft wants to change how we use Windows 10 with Win...

FCC challenges the current net neutrality regulations A...

Mozilla takes things up a notch with its new faster and ...

Qualcomm Inc. has come out with a new server level proce...

Once again it's time for a Microsoft security breach tha...