Excel Tutorial

Excel Tutorial Shortcut Keys in Excel Formatting in Excel Notes in Excel Formats in Excel Cells and Ranges in Excel Excel Function and Formulas Conditional Formatting in Excel Data Validation in Excel Charts in Excel Excel Ribbon Toolbar Basics of Excel Spell Check in Excel Data Analysis in Excel AutoFill in Excel Goal Seek in Excel Solver in Excel Pivots Table in Excel Go-To Special function in Excel Blank cells in Excel Count Cells with Text in Excel Date and Time in Excel-VBA Dependent Drop-down List in Excel Operators in Excel Dependent Combo box in Excel VBA Error Bar in Microsoft Excel Excel Axes Excel File using Password Excel Unique Values Frequency Distribution in Excel Gauge Chart in Excel Histogram in Excel Sum Every Nth Row in Microsoft Excel SumIF Formula in Microsoft Excel Multiplication in Excel Unique Values in Excel Trendline in Excel Excel Themes Copying formula in Excel Check Marks in Excel Calculating the Last Day of the Month in Excel Calculating Age in Excel Insert Row in Excel

Functions

Excel MAX() Function Excel INT() Function Excel MOD() Function Excel ROUND() Function Excel ROUNDUP() Function Excel AVERAGE() Function Excel COUNT() Function Excel COUNTA() Function Excel COUNTBLANK() Function Excel MIN() Function Excel EDATE() Function Excel EOMONTH() Function Excel HOUR() Function Excel MINUTE() Function Excel SECOND() Function Excel TIME() Function Excel WORKDAY() Function Excel WORKDAY.INTL() Function Excel DAYS() Function Excel WEEKNUM() Function Excel WEEKDAY() Function Excel SMALL() Function Excel LARGE() Function Excel LEFT() Function Excel RIGHT() Function Excel MID() Function Excel FIND() Function Excel SEARCH() Function Excel EXACT() Function Excel SUBSTITUTE() Function Excel TEXT() Function Excel VALUE() Function Excel AND() Function Excel OR() Function Excel IFERROR() Function Excel IF() Function Excel Nested IF’s Function Excel IFNA() Function Excel COUNTIFS() Function Excel VLOOKUP() Function Excel HLOOKUP() Function Excel INDEX() Function Excel MATCH() Function Excel OFFSET () Function Averageif Function in Excel

How To

How to import Microsoft Access data into the Microsoft Excel How to use TODAY function in Excel How to Alphabetize in Excel How to remove duplicate values from excel How to lock cells in Excel How to create drop down in excel How to Delete Row in Microsoft Excel How to Highlight Duplicates Words in the Microsoft Excel How to print titles in Excel How to make use of the Wildcard in Excel How to Make Use of the F-Test in Excel How to make use of the Excel Autofit in Excel How to generate random numbers in Excel How to apply Advanced Filter in Excel How to use Index and Match in Excel

Misc

Absolute Value in Excel Adding Column in Excel Converting Units in Excel Count Characters in Excel Custom Sort Order in Excel Decimals in Excel Division in Excel Locate Maximum Values in Excel Nearest Multiple in Excel Paste Options in Excel Quarter Dates in Excel Row Difference in Excel Separate Strings in Excel Reverse List in Excel Array Formula in Excel What if Analysis Data Table in Excel Excel Shortcut Keys What is a spreadsheet in Excel?

Custom Sort Order in Excel

Microsoft Excel Worksheet is widely used for office, personal and various usages. The data present in the worksheet is a combination of numbers and alphabets. If the worksheet contains more content, organizing the worksheet is more critical for a good and easy viewing of data. To manage the worksheet, Excel provides a default function called SORT, which is used to sort the data based on user preference. The content or data in the worksheet is sorted based on alphabetic, numeric and anyways. The Sorting process in Excel is done in multiple ways, like, sorting data, sorting range, and sorting a sheet and a cell.

What is Custom Sorting?

Excel provides default sorting option. But sometimes, the user needs to sort the data based on their preference. To sort the order based on the preference, Excel provides the option to create custom list for the desired sorting order. This process is called Custom Sorting.

Method #1. How to create a Custom Sorting?

To create a custom sorting, the steps to be followed are,

Step 1: Enter the data in the worksheet based on respective rows and columns.

Custom Sort Order in Excel

Step 2: From the data, select the desired cell in the column which needs to be sorted in the order. Here the cell F1 is selected.

Step 3: Choose the Sort Command from Data Tab.

Custom Sort Order in Excel

Step 4: A sort dialog box will display. In that enter the value in Sort by list. Here the value is entered as House, where the data are arranged based on the house size such as Small, Medium and Large. In the Sort on choose Values, and choose the Custom List from the order.

Custom Sort Order in Excel

Step 4: After selecting the Custom List, the custom list dialog box will appear, in that choose, New List from Custom List. Enter the respective entries in the “List the entries” dialog box. Here the entries are based on the house size, hence add the entries as Small, Medium and Large .Enter the individual entries by clicking the Enter key after entering the single entries. Choose the “Add” button, to add the entries in the Custom List. The new sort order will display in the Custom List box. Select the “New list” in the Custom list and click Ok.

Custom Sort Order in Excel

Step 5: The custom lists dialog box will close after pressing the OK button. Press OK in the Sort dialog box where the custom sort is performed.

Custom Sort Order in Excel

From the above worksheet, the data is arranged in a custom order like SMALL, MEDIUM and LARGE.

Method #2. How to sort the multiple columns in a data?

From the previous concept, the data is arranged based on custom order using the sort dialogue box. In that method, the values or data present within the column is interchanged. Here in this concept, the columns are sorted based on the values from smallest to largest and largest to smallest using add level option. To sort the single or multiple columns by using add level option, the steps to be followed are,

Step 1: Enter the data in the worksheet based on respective rows and columns.

Step 2: Here the column which contains serial number is selected for sorting.

Custom Sort Order in Excel

Step 3: Choose the Sort Command from Data Tab.

Custom Sort Order in Excel

Step 4: A sort dialog box will display. Enter the column name in the Sort by list. Here the column name is entered as Serial Number. If one more column needs to be added, choose the add list in the Sort dialog box.

Custom Sort Order in Excel

From the above method, the column name serial number is added in the sort by column. If the user wants to add more columns, click the add level option.

Custom Sort Order in Excel

The add level option is used to add one or more column. Here the column called “NAME” is chosen where the name is to be sorted from “A to Z”.

Custom Sort Order in Excel

From the above image, two columns are selected for sorting based on the order. After the column is selected, the data are arranged in the specified order.

Custom Sort Order in Excel

From the above worksheet, the Serial Number is arranged from smallest to largest order. To sort the name from A to Z, same method should be followed.

Summary

From the above method, the steps and methods to customize the data is explained briefly.