They’ll become our options for the answer to the first question. What we can use for drop-down items here are the months of the year.
HOW TO MAKE A DROP DOWN MENU IN EXCEL 2016 HOW TO
We’ll use this short questionnaire to see how simple it is to create a drop-down menu and you’ll be able to use these steps to create a list with any data you need.įirst, we’ll have a look at how to create a longer drop-down list with more options. It helps me out and lets me know the direction I should take for future articles that I write.2 How to Create a Short Drop-Down List in Excel If you think this article was helpful, please do click the Thumbs Up icon to the bottom left of this text.
If you have any questions Don't hesitate to use the blue " Ask a Question" button at the top of the page, or comment on this article! If I use Excel for this, I tend to make one tab that houses all my lists (I also tend to name the list ranges to make it a little easier to read the formulas - "=totalfunds" is easier to understand than "=AA72"). That said, using dropdowns or lookup tables is the best way to assure data integrity. If you are at all confused or wish to watch how this type of dropdown is created, check out the mico-tutorial video I made illustrating this.Īlthough this article shows you how to create dropdown lists in Excel, if you are doing so, it means that you should probably be using a relational database (Microsoft Access, is one of the easier ones to use). If the tab is deleted and there are formulas pointing to it, you will see the dreaded "#REF!" result in the spreadsheet - which means the formula REFers to something that is no longer there! It is important to check BEFORE deleting the original tab that any formulas are now pointing to the new tab (click in the cell and look in the bar at the top to check the formula, you can also do a search on the name of the tab using CTRL-F). Once you have determined that everything is working as expected, you can delete the original tab as outlined in the previous paragraph. If you like the changes you can always delete the original first and rename this tab to the original’s name by deleting the “ (2)”. The new tab will be named the same as the old one followed by “(2)” to denote it is the second tab with that name. Highlight the sheet you want the new copy to appear BEFORE (I always recommend selecting the tab you are copying).Right click on the source tab and choose “Move or copy…”.Also, before making any changes to an existing spreadsheet tab always make a copy and make the changes in the copy. It is best to use this alternative method on a new worksheet WITHOUT data in it. Using the format painter does NOT work.Īlternatively, you can highlight an entire section in step 3 above, instead of one cell. Copying is the easiest way to do this (you can use the key combinations CTRL-C to copy and CTRL-V to paste). Now that you have created a cell with a dropdown list, you might want to copy the result to the next cell or group of cells. Highlight the list (this makes the formula in the Data Validation box).Go back to the tab with the list of names.Click the icon next to “Source” to get the single line dialog (2nd Picture below).Note that if the cell already has data in it, it will keep it as long as that data item is in the list created in step 2 above.In the original tab click on the cell you want to have the dropdown list appear in.In the new tab list each of the items you want in the list (this can be changed later).The procedure for doing this is as follows (The images and steps used are from Excel 2016): In this way each time she chose a class it would be the same. I suggested that she put all the class names in another tab in the spreadsheet and make the class name field a dropdown list.
I suggested a solution – one that I had learned a previous time we sat down together. Because each class name had been entered manually, there were errors in spelling, formatting, etc. The answer to this seemed relatively simple, query the class name and return the class total if it matched certain criteria. The way the data was set up listed each class separately with a column denoting how many people were in each class. She wanted to find out how many classes there were in total and how many people there were in each type of class. Recently, she brought home a problem from work. Every time I sit down with my wife I learn something new (that’s 40 years of new stuff!).
0 kommentar(er)
