Which menu has Sort option in Excel?

Account InformationTechRepublic close modalShare with Your FriendsHow to use Excels advanced sorting options Your email has been sent{{#message}}{{{message}}}{{/message}}{{^me

Which menu has Sort option in Excel?

Account InformationTechRepublic close modal

Share with Your Friends

How to use Excels advanced sorting options

Your email has been sent

{{#message}}{{{message}}}{{/message}}{{^message}}Your submission failed. The server responded with {{status_text}} (code {{status_code}}). Please contact the developer of this form processor to improve this message. Learn More{{/message}}

{{#message}}{{{message}}}{{/message}}{{^message}}It appears your submission was successful. Even though the server responded OK, it is possible the submission was not processed. Please contact the developer of this form processor to improve this message. Learn More{{/message}}


Image of Susan Harkins

by Susan Harkins in Software on August 16, 2019, 2:51 PM PDT

How to use Excels advanced sorting options

When an ordinary alphabetical or numerical sort in Microsoft Excel won't do, check out the advanced sorting options or add a custom sort pattern.

Image: Microsoft

Excel has several advanced sorting options, but most of us never venture beyond the ordinary alphabetic and numeric sorts. If you need more than a simple sort, check out Excels advanced sorting options. In this article, well discuss several advanced options. Then, Ill show you how to create a custom sort for those times when even advanced settings arent adequate.

Im using Excel on Office 365 on a Windows 10 64-bit system, but you can apply these sorting techniques to earlier versions. Custom sorts arent supported in the browser edition. You can use your own data or download the demonstration .xlsx and .xls files.

LEARN MORE: Office 365 Consumer pricing and features

How to use advanced sorting options in Excel

Once youve mastered the simple sortsalpha and numericyou might want to check out the advanced sorting options. You might never need them, but if you do, theyre invaluable.

To find these options, click the Data tab and then click the Sort option in the Sort & Filter group. Then, click the Options button to launch the dialog shown in Figure A. (You must select a range of values to access these settings.)

Figure A

Display Excels advanced sorting options.

The three advanced options were going to discuss are:

  • Case sensitive
  • Sort top to bottom
  • Sort left to right

As you can see, only the Sort top to bottom advanced option is set by default, which makes sense. Generally, thats the direction we sort bytop to bottom (not left to right).

Lets start with the Case sensitive option. Figure B shows the results of two sorts: The default, which is case neutral, and the case sensitive sort. As you can see, the lowercase characters sort before the uppercase. (I selected the sort ranges before sorting, and if there are adjacent columns, youll be prompted to expand or keep the current selection.)

Figure B

Case sensitive sorts lowercase characters before uppercase.

Sort top to bottom is the default, and youre already familiar with it, so lets look at Sort left to right. Figure C shows the result of changing this option to Sort left to right. The result is the same as the case sensitive because I didnt uncheck that optionit remains set until you unselect it (you should watch out for this small gotcha). In contrast, the Sort left to right option remains set until you run a top to bottom sortExcel changes it for you automatically. When using the left to right sort, youll probably need to set the row in the Sort by control. If you receive an error, try again and check that option.

Figure C

You can sort from left to right.

Other advanced sorting options include cell and font color and conditional formatting icons. You can also add sorting layers to form subgroups. You probably wont use these advanced options frequently. None of these advanced options allow you to apply a custom sort order.

How to use Excels custom sort feature

Excels custom sort feature lets you determine the sort order because some data comes with a sort order thats not alphabetic or numericits sorted by business rules. For instance, a list of sizes (petite, small, medium, large, and x-large) could be sorted alphabetically, but it wouldnt sort by sizes, from smallest to largest and vice versa. For this, we can create a custom sort as follows:

  1. Click the File tab and select Options.
  2. Select Advanced in the left pane.
  3. In the General section, click Edit Custom Lists. Youll see Excels built-in listsyou probably rely on a few already and perhaps never knew you were using a custom sort.
  4. In the List entries control, enter the list in sort order (Figure D): Petite, small, medium, large, x-large. (If the list is long or already exists as natural data, you can specify the cell reference and click Import.)
  5. Click Add, and Excel will transfer the new custom sort to the Custom lists on the left.
  6. Click OK twice.

Figure D

Enter the sizes in the order you want them to sort.

To apply the custom soft, select the list you want to sort. Then, click the Home tab, click Sort & Filter in the Editing group, and choose Custom Sort from the dropdown list. If data exists in adjacent columns, youll be prompted to expand the selection, but dontchoose Continue with the current selection and click Sort. From the Order dropdown, choose Custom List (Figure E). Select the size list shown in Figure F and click OK twice. Figure G shows the sorted list.

Figure E

Choose Custom List.

Figure F

Select the custom list.

Figure G

Excel sorts the list by the custom list sort.

Anytime you have a sort pattern thats not supported by the built-in features, consider building a custom list to sort by.

Send me your question about Office

I answer readers questions when I can, but theres no guarantee. Dont send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, Please troubleshoot my workbook and fix whats wrong probably wont get a response, but Can you tell me why this formula isnt returning the expected results? might. Please mention the app and version that youre using. Im not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at .

Microsoft Weekly Newsletter

Be your company's Microsoft insider by reading these Windows and Office tips, tricks, and cheat sheets.Delivered Mondays and Wednesdays Sign up todaySusan HarkinsPublished: August 16, 2019, 2:51 PM PDT Modified: June 5, 2020, 1:32 PM PDT  See more Software

Also See

  • 50 time-saving tips to speed your work in Microsoft Office (free PDF) (TechRepublic)
  • Cost comparison calculator: G Suite vs. Office 365 (TechRepublic Premium)
  • Microsoft Office has changed, how you use it should too (ZDNet)
  • Best cloud services for small businesses (CNET)
  • How to use named ranges to quickly navigate an Excel workbook (TechRepublic)
  • How to add a drop-down list to an Excel cell (TechRepublic)
  • Office Q&A: How to combine formulas with Excel's data validation and a Word Replace trick (TechRepublic)
  • How to use Windows 10 Task Scheduler to run Office 365 tasks (TechRepublic)
  • Best to-do list apps for managing tasks on any platform (Download.com)
  • More must-read Microsoft-related coverage (TechRepublic on Flipboard)

Video liên quan