Excel - COLUMN Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I have Mac Excel 2016. I need to sort a column in a way which is neither ascending nor descending, and it doesn't concern days of week or months. It won't let me create my own order in custom sort.
Solved by C. A. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
User 26/03/2018 - 01:04
2016 on a Mac
Excelchat Expert 26/03/2018 - 01:05
Welcome, Thanks for choosing Got It Pro-Excel
User 26/03/2018 - 01:05
Excelchat Expert 26/03/2018 - 01:06
In other words you want us help you do a custom sort?
User 26/03/2018 - 01:07
Basically, except I can get it into custom sort. But when I click Sort button, then under Order, Custom List, it only lets me click months, days, etc. It doesn't let me create my own unique order, which is what I need
User 26/03/2018 - 01:08
For example, I need city names in custom order of: Boston, Chicago, Atlanta, Memphis.
Excelchat Expert 26/03/2018 - 01:09
Alright, please give a moment to solve your problem. Thank you!
User 26/03/2018 - 01:09
User 26/03/2018 - 01:10
By the way, this first time really is free, right? And this isn't one of those sites which automatically subscribes you and makes you pay for future services, right?
Excelchat Expert 26/03/2018 - 01:13
Yeah, but for more inquiries regarding the the services at Got It! contact them through: sheets.support@gotitapp.co
User 26/03/2018 - 01:13
Excelchat Expert 26/03/2018 - 01:15
Sorting the problem in such an order which is not specified in the Custom list, we will need to create our own NEW LIST by adding it in the custom lists.
Excelchat Expert 26/03/2018 - 01:15
This is done as follows:
Excelchat Expert 26/03/2018 - 01:17
Open the Custom List dialog box found under the Data tab.
User 26/03/2018 - 01:18
I don't see that. Do you mean the Sort button, which then gives you custom list option?
Excelchat Expert 26/03/2018 - 01:18
We can collaborate online, but in order to work on a this feature perfectly on this, then I have to work on this locally and send you a file.
Excelchat Expert 26/03/2018 - 01:18
Yes I mean first go to the Sort Button and then the Custom list button.
User 26/03/2018 - 01:18
Did that
User 26/03/2018 - 01:19
What now?
Excelchat Expert 26/03/2018 - 01:21
I want you to notice that, in the list they have provided, there is a "NEW LIST" option in among the lists like days, months and etc
User 26/03/2018 - 01:22
My professor talked about that and showed a picture that has the "NEW LIST" option. Mine looks exactly the same, but doesn't have that "NEW LIST" option. I'm thinking it might be because of the model for whatever reason (2016 Excel, on Mac)?
Excelchat Expert 26/03/2018 - 01:25
Okay. I get you, if that is the case this can be resolved by ensuring that we have the correct settings for our worksheet.
Excelchat Expert 26/03/2018 - 01:25
Follow these steps:
Excelchat Expert 26/03/2018 - 01:29
i). On the Menus, select Tools
Excelchat Expert 26/03/2018 - 01:29
ii). Click the Excel Options
User 26/03/2018 - 01:30
I don't see that. Do you mean Tools>Excel Add-ins?
Excelchat Expert 26/03/2018 - 01:31
I mean File > Options
Excelchat Expert 26/03/2018 - 01:32
Can you locate the File tab?
User 26/03/2018 - 01:32
I don't see any "Options" under File
Excelchat Expert 26/03/2018 - 01:32
It's is the last one before 'Exit'
User 26/03/2018 - 01:33
The last one under File for me is Preferences. There is no Exit or Options on mine, I just looked
User 26/03/2018 - 01:34
*I mean Properties, not Preferences
Excelchat Expert 26/03/2018 - 01:38
There is a couple of ways to locate this:
Excelchat Expert 26/03/2018 - 01:38
Click the File tab jumping into backstage view; Then you will view the Options button at left bar. Click it into Excel Options window.
Excelchat Expert 26/03/2018 - 01:38
Excelchat Expert 26/03/2018 - 01:38
Click the File tab; Click the Help button at left bar; In the middle pane, you will view the Options button under the Tools for Working With Office.
User 26/03/2018 - 01:40
I don't see any Help button under File.
Excelchat Expert 26/03/2018 - 01:42
Let me upload files for you and you will be in a position to follow thank you!
Excelchat Expert 26/03/2018 - 01:42
Once this is located, Under the advanced category go to General section and locate the Edit Custom Lists... Click the Edit Custom List button, and you will view the Custom List dialog box. In this dialog box, you can add, delete, import custom lists.
User 26/03/2018 - 01:45
Have you uploaded them yet? I don't see any files
Excelchat Expert 26/03/2018 - 01:45
Taking them please
User 26/03/2018 - 01:50
When they do get uploaded, could you please let me know?
Excelchat Expert 26/03/2018 - 01:51
The Excel List dialog box is:
[Uploaded an Excel file]
Excelchat Expert 26/03/2018 - 01:52
Sorry that is the way the Custom List dialog box.
Excelchat Expert 26/03/2018 - 01:52
The Excel Options dialog box looks like this:
[Uploaded an Excel file]
User 26/03/2018 - 01:53
Okay, but I don't know how to get to Excel options.
Excelchat Expert 26/03/2018 - 01:53
So basically, we use the NEW LIST option in the list provided to add custom our list.
User 26/03/2018 - 01:54
Mine just does not have the NEW LIST option. It looks like that picture, but that option isn't available.
Excelchat Expert 26/03/2018 - 01:54
This will help us get the custom list you mentioned for the cities. Boston, Chicago, Atlanta, Memphis....
User 26/03/2018 - 01:55
Okay, so even though mine doesn't have NEW LIST option under Order-Custom, do you still want me to click Custom?
User 26/03/2018 - 01:56
See, this is what I'm talking about: no NEW LIST.
User 26/03/2018 - 01:57
Never mind, I tried to just send you a screen shot, but it deleted all of the past comments.
Excelchat Expert 26/03/2018 - 01:57
Always we use Custom sort, but it is sounding that the problem you are encountering is more of a tech problem; it is not within the scope of functionality of Excel.
Excelchat Expert 26/03/2018 - 01:58
Unfortunately, tech issues are out of our scope.
User 26/03/2018 - 01:58
So there's no way to circumnavigate my Excel's Custom Order not having the NEW LIST option?
User 26/03/2018 - 01:59
(Meaning that I can only customize within default lists, like months of year or days of week(
Excelchat Expert 26/03/2018 - 02:00
But what you should know that, the new option in Mac Excel 2016 is found under the Custom command.
User 26/03/2018 - 02:00
So are you saying that my model should have the NEW LIST option?
Excelchat Expert 26/03/2018 - 02:01
Exactly, it ought to have.
User 26/03/2018 - 02:01
Any ideas why it doesn't, how to get it, or how to find a way around it?
Excelchat Expert 26/03/2018 - 02:02
Currently we are not allowed to solve tech and software related issues; they are out of our scope.
Excelchat Expert 26/03/2018 - 02:02
We apologize for that.
User 26/03/2018 - 02:03
It happens I guess
User 26/03/2018 - 02:03
But do you know any ways to still make a customized list without using the NEW LIST option?
Excelchat Expert 26/03/2018 - 02:04
Once again, once you have the correct software setup, the option NEW LIST will be available and you will be able to custom your sorts according to the city.
User 26/03/2018 - 02:04
Okay, thank you

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc