How to automatically sort a pop-up menu list under Cell Data Formatting in Excel?

Maybe it's not the best system, but what I use is to keep a column with all the names adding, from time to time, the new ones. I order the column and renew the drop-down menu by selecting the ordered column. It's not automatic as what you would like, but quite practical.

Below are some steps for creating and modifying pop up menus. Compared to Excel, this is not so easy. But it is easier than it seems looking at all the steps.

Creating a pop up menu from a column of data:

1. In a table, create a column of items for the pop up menu.
2. Select all of them. If a blank cell is included, the pop up menu will naturally be "start with blank".
3. Format as pop up menu. All of the cells will be pop up menus that include all the items in the order they appear in the table.
4. Select the one to be used as the main one (often this is the blank one)
5. Copy
6. Paste it to where it is needed

How to edit the pop up menu list to create a new pop up:

1. Select the column of items  
2. Format as automatic
3. Edit the items (add new items, delete items, sort, etc.)
4. Select them and format as pop up. This does not change any of the pop ups in your other table, it only creates a new one you can copy/paste.

If you don't need all your "used" pop up's to have the new list, you can simply paste the blank new pop up to the lower rows of your other table and go on from there.

If you want all of the pop ups (even the ones you have used already) to have the new list while retaining their current values:

1. Copy the pop up that matches the top row of your other table
2. In your other table, select the column of pop ups and format as automatic
3. Paste the new pop up over the top one
4. Select the column and format as pop up. If you deleted an item from the pop up but it appears in the column, that item will go to the bottom of the pop up menu.

Note: This works best if the pop up menu items are "automatic" format. With newer versions of Numbers, if you try to format a pop up menu as "automatic" and the selected item (the value showing in the cell) is some mixed-up format like a number formatted as text, it will remain a pop up. If you need numbers or dates or times formatted as text, format them as text but wherever I say "format as automatic" in the steps above, format as text instead. The text format has to apply to all the items in the pop up.

Let me know if you run into any problems. Something changed in Numbers from how this used to work so I had to modify the instructions. It seems to work fine for automatic and text formats.

Aleksander wrote:

Is there a way to automatically sort this pop-up menu without manually moving every new entry?

No automatic sort. And if you build a sorted list elsewhere, format as Pop-Up Menu and paste into your existing cells you'll lose the original values those cells were displaying.

Pop-Up Menu is useful if you have a limited number of possible values. But if you have lots of values that scroll down out of sight then you might consider not using Pop-Up Menu and instead turning on 'Show suggestions when editing table cells' at Numbers > Settings.

Then when you start entering a new value in the column Numbers will give you a list of existing values in that column to choose from. You can choose one of those, or enter a new value.

SG