• Facebook
  • Twitter

You need to create a document standing out very well organized, in which you can interact with the elements present in the cells, through the appropriate lists and dropdown menus? Well, it sounds like you need Excel’s list functionality. What is Just what I mentioned, is a function that allows you to group certain cells into different lists and create dropdown menus with the entries and values ​​in the spreadsheet.

If you want to know more, take five minutes of free time and discover How to make dropdown menus in Excel, following the instructions that I am going to give you. I assure you that this is a very simple operation to perform, you don’t have to be an Excel expert to be successful.

I bet you can’t wait to start reading all the how-tos to reach your goal, am I right? So let’s not waste any more time and get to work right away – you just have to sit comfortably at your PC and pay attention to all the operations that I will describe to you. Please note that the procedures apply to Excel for Windows, macOS, and Excel Online, but not to the Excel app for mobile phones and tablets. With that said, I can only wish you good reading and good work!

How to make dropdown menus with Excel

the Excel Dropdown Menu they can contain values ​​defined directly by the user (ie words, phrases or numbers present exclusively in the menus) or they can be created from values ​​already present in the spreadsheet. But let’s go in order.

To create a dropdown using values ​​defined directly by you, select the cell (or cells) in which to insert the menu, go to the tab data Excel, which you can find above, and click the button Data validation.

On the screen that opens, select the item list from the menu allow and type in the text field originall the values ​​you want to appear in the dropdown, separating them with a semicolon. Finally, click the button. Well and ready

At this point, select one of the cells in which you entered the dropdown menu and click on the icon , located next door. If you followed the operations that I indicated to the letter, you will be shown a box, through which you can select one of the values ​​that you defined. Comfortable, right?

If, on the other hand, your intention is make a drop down menu with excel Using the values ​​already present in the spreadsheet, the operation to perform is similar to the one described above. First, select with the mouse the cells that contain the values ​​to be inserted in the dropdown (the values ​​must be contained in the same column or in the same row and there must be no breaks between them), go to the tab formulas Excel you find at the top and click the button Define name.

On the screen that appears, type the name want to assign to the selected group of values, without entering any spaces, and then click the button Wellto save the changes.

At this point, select the cell (or cells) in which you want to insert your dropdown, go to the tab data Excel, above, and click the button Data validation.

On the new screen you see, select the item listusing the menu allow and then type in the field originthe symbol =followed by the group name of data that you created previously (for example, if you named the group of values ​​”group”, you would write = group ). At this point, all you have to do is press the button Wellto confirm the operations you have performed.

Very good! You have just created a new dropdown from the values ​​already present in the spreadsheet! To view them, select one of the cells in which you entered the dropdown menu and click the icon on the side: you will be able to choose one of the values ​​from the box that will be shown to you

Another important thing to know is that the dropdown values ​​will update in real time. This means that if you change one of the values ​​in the list in the spreadsheet in which you created the dropdown, the changes will automatically be made to the dropdown as well.

How to make a list with Excel

Now let’s see how to create a dropdown to filter and organize the values ​​in the spreadsheet.

if you want to learn how to make a list in excelall you have to do is open the document in which you want to insert your lists, click the button Format as a table content on the card home Excel, above, and choose your preferred style from the menu it displays.

At this point, hold down the left mouse button, select all the cells you want to include in your list, and click the button Well, to confirm. If you have also highlighted the column headings, place the check mark next to the item Table with headings.

That’s all! You just got a list in which it is possible to arrange the cell values ​​using special drop down menus. In fact, clicking on the icons placed in the cell relative to the column headers, you can arrange the values ​​in alphabetical order apply gods filters based on custom parameters, enable or disable the display of certain values ​​and much more.

If you want, you can change the style of your table by selecting one of the cells that are part of it and choosing the desired style from the menu Home> Format as Table.

If, finally, you want to cancel the creation of the table, you can click on the button Cancel of Excel (the arrow pointing left iconwhich you can find in the upper left corner, in the title bar of the program), or you can select all the cells that are part of it, go to the tab designat the top, and click the button Convert to range.

I warn you that you can get the same result even using the function filters Excel: select column header cell, go to tab home and click on the items Sort & Filter > Filterto activate the dropdown menu.

How to create a dynamic dropdown menu in Excel

If you like create dynamic dropdown menu in excel I warn you that this operation is not difficult, but you must be patient and pay close attention to the procedures that I will describe to you.

Actually, if you have already mastered the operations that I described in the previous chapters, you should be able to easily carry out this type of menu, since it is the combination of the two methods described above.

The first thing you need to do is highlight all the cells that contain the values ​​you want to insert into the dropdown. A dynamic menu is created from the data already present in the spreadsheet, and therefore you cannot directly define it yourself, unless you insert it into special cells.

After highlighting the cells, go to the tab homeat the top, and click the button Format as a table, choosing a style from those listed. If you also selected the column label, check the box table with headers and then press up Wellto confirm.

Now, highlight the cells of the newly created table, not including the column label (generated automatically if not already present) and reaches the card formulas. After that press the button define name and indicates a name to assign to the group of values, writing it in the field name. press then Well to confirm this operation as well.

At this point, select one or more cells in which you want to insert the dropdown and get to the tab data. Now press the button Data validation and, on the screen that appears, choose the item list from the menu allow. Then write the symbol = followed by group name that you defined previously. Also in this case, confirm the changes made with the key Well.

Good! If you have carefully followed the procedures that I have indicated, when you click on one of the cells in which the dropdown menu is present, you will find the icon next to it: interacting with the latter, you will be shown a box containing values ​​to select, which will correspond to those of the table you have created.

The particularity of this menu is being able to add values, without having to change any configuration parameter. Just get to the table, move to the last empty row, and add a new value. By doing so, the latter will also be available in the dropdown menu.

  • Facebook
  • Twitter