How to Use Excel Sort (Without Messing Up Your Data)

How to Use Excel Sort -magoosh

Sorting data is considered to be one of the most essential components of data analysis as it helps in organizing or arranging the data in a specified desirable order and aids the procedure of formatting and visualizing data. You might want to arrange a list of values alphabetically, compile a list of product values from highest to lowest, or order rows by their corresponding icons or colors.

Sorting data in Excel helps the user to understand and visualize data more effectively, facilitates investigation of data, aids the process of searching, organizing and formatting data and ultimately, helps in making efficient decisions. You can sort data by text (A to Z or Z to A), numbers (smallest to largest or largest to smallest), and dates and times (oldest to newest and newest to oldest) in one or more columns. You can also sort using a custom list you create (such as A, C, and B) or by the format of cells which can be done on the basis of cell color, font color, or cell icon.

The following article discusses the various methods and procedures of sorting data in specified order as well as various criterions that can be applied on the process to increase the orderly efficiency of the spreadsheet.

Sorting text in MS Excel

There might be times when values in cells are textual and need to be sorted accordingly, in order to make the spreadsheet look orderly. The need of sorting may also arise from the fact that it can facilitate analysis and investigation of data as well as aid effective decision making.

The following procedure needs to be performed to sort text in Excel:

  1. Select a cell or range of cells in the column which needs to be sorted.
  2. Click on the Data tab available in Menu Bar, and perform a quick sort by choosing any one of the options under the Sort & Filter group, depending upon whether you want to sort in ascending or descending order.

The text will be sorted in the column of spreadsheet accordingly.

Note: Check that all data in the column to be sorted is stored as text.  If the column that you want to sort contains values that are numerical, you need to change them into their corresponding text, failing to do so, the numbers would be stored as numbers are sorted before the numbers stored as text.

To format all the selected data in a particular column as text, press Ctrl+1 simultaneously on the keyboard that will launch the Format Cells dialog, click on the Number tab and then, under Category section, click on General, Number, or Text.

Remove any leading spaces:  In some cases, when data is imported or copied from any another application, the data values might have some leading spaces, which can produce undesirable sorting results, when the operation is applied. Thus, all the leading spaces need to be removed before sorting data. This can be done manually or using TRIM function.

Sorting numbers in MS Excel

There might be times when values in cells are numerical and need to be sorted accordingly, in order to make the spreadsheet look orderly. The need of sorting may also arise from the fact that it can facilitate analysis and investigation of data as well as aid effective decision making.

The following procedure needs to be performed to sort numbers in Excel:

  1. Select a cell or range of cells in the column which needs to be sorted.
  2. Click on the Data tab available in Menu Bar, and perform quick sort by choosing any one of the options under the Sort & Filter group, depending upon whether you want to sort in ascending or descending order.

The numbers will be sorted in the column of spreadsheet accordingly.

Note: Check that all the data to be sorted in the specified column contains values that are numerical. If the result is not desired, the column might contain values which are not numbers and are treated as text by the software. In that case, the data needs to be changed into numbers by pressing Ctrl+1 simultaneously on the keyboard that will launch the Format Cells dialog, clicking on the Number tab and then, under Category section, clicking on Number. Leading spaces in the data values imported or copied also need to be removed manually, which can be achieved manually or by using TRIM function.

Sorting Date or Time in MS Excel

There might be times when values in cells are dates or times and need to be sorted accordingly, in order to make the spreadsheet look orderly. The need of sorting may also arise from the fact that it can facilitate analysis and investigation of data as well as aid effective decision making.

The following procedure needs to be performed to sort date or time in Excel:

  1. Select a cell in the column which needs to be sorted.
  2. Click on the Data tab available in Menu Bar, and perform quick sort by choosing any one of the options under the Sort & Filter group, depending upon whether you want to sort in ascending or descending order.

The date or time in the column of the spreadsheet will be sorted accordingly.

Note: Check that all the data to be sorted in the specified column contains values that are either date or time. If the result is not desired, the column might contain values which are not stored as date and time but are treated as numbers or text by the software. In that case, the data needs to be changed into the date or time serial values. Leading spaces in the data values imported or copied also need to be removed manually, which can be achieved manually or by using TRIM function.

If you want to sort the values in the column by days of the week, format the cells in a manner to display the day of the week and convert them to text by using the TEXT function and perform the sorting operation accordingly.

Sorting Multiple Rows or Columns

There might be times when the data in MS Excel needs to be sorted by performing the operation on more than one row or column, in order to make the spreadsheet look orderly. The need of sorting may also arise from the fact that it can facilitate analysis and investigation of data as well as produce efficiency in the decision-making process. This case of sorting may especially arise when the sorting in one column (or row) may have equal values, wherein values can be textual, numerical or date or time, such that further sorting can be applied on another column (or row), leading to an ordered arrangement in the spreadsheet.

For example, in the spreadsheet Society Work, if the sorting is initially applied onto the alphabetic nature of the Departments’ employees are selected for, further sorting may be applied onto the column of Name, to make the arrangement ordered. In order to achieve best results of sorting in a spreadsheet, the range of cells on which sorting is applied should have column headings to make the arrangement highly ordered which can lead to a proper analysis of data and effective decision making.

The following procedure needs to be performed to sort multiple rows or columns in Excel:

  1. Select any cell within the data range wherein sorting needs to be applied.
  2. Click on the Data Tab on Menu Bar, and further click on Sort under Sort & Filter group.
  3. Sort dialog box opens up. Select the first column of priority which needs to be sorted under Column, in the Sort dialog box.
  4. Under Sort On List, select the type of sort that needs to be applied. Anyone of the following options can be selected:
    • Select Values to sort by value of text, number or date and time.
    • Select Cell Color, Font Color or Cell Icon to sort by the format applied on the column cells.
  5. Under Order list, select the way of sorting. Anyone of the following options can be selected:
    • For sorting text values, select A to Z or Z to A accordingly.
    • For sorting numerical values, select Smallest to Largest or Largest to Smallest accordingly.
    • For sorting date or time values, select Oldest to Newest or Newest to Oldest accordingly.
    • For sorting based on a custom list, select Custom List.
  6. Optional: Click on Add level button in the Sort dialog box to add another column to sort by and repeat the steps three to five to sort effectively.
  7. Optional: Select the entry and click on Copy Level button in the Sort dialog box to copy a column/field to sort by.
  8. Optional: Select the entry and click on Delete Level button in the Sort dialog box to delete a column/field to sort by.
  9. Optional: To change the order in which the columns or fields in the spreadsheet need to be sorted, select an entry of the Column Name and then click on the Up or Down arrow button, next to the Options button to change the order of sorting, provided entries higher in the list would be sorted before entries which appear lower in the list.
  10. Click on OK.

Sorted spreadsheet will be obtained.

Note: One entry for sorting the spreadsheet is essential in the dialog box.

Sorting by Cell Color, Font Color, or Icon in Excel

If there is a range of cells or columns which have been manually or conditionally formatted such that they have cells containing different cell colors, font colors or icons, they can be ordered into a specified arrangement even on the basis of their formats such that it can it can facilitate analysis and investigation of data as well as produce efficiency in decision making process.

The following procedure needs to be performed to sort cells by cell color, font color or icon in Excel:

  1. Select any cell in the column where sorting needs to be applied.
  2. Click on the Data Tab on Menu Bar, and further click on Sort under Sort & Filter group.
  3. Sort dialog box opens up. Select the column which needs to be sorted under Column list.
  4. Under Sort On list, select Cell Color, Font Color or Cell Icon, based upon the criterion of sorting.
  5. Under Order list, click on the arrow of the drop-down list and then, depending on the type of cells formats, select a cell color, font color, or cell icon, based upon which the formatting needs to be done.
  6. Select the order of sorting within the manner by performing the steps as desired:
    • To move the desired cell color, font color, or cell icon to the top or to the left, as per the desired order requirement, select On Top option for a column sort, and On Left option for a row sort.
    • To move the cell color, font color, or cell icon to the bottom or to the right, as per the desired order requirement, select On Bottom option for a column sort, and On Right option for a row sort.

    Note: There is no default cell color, font color, or icon sort order predefined by Excel, as there can be a large number of cell color, font color or cell icon defined by the user. Thus, the user must explicitly define the order of sorting in case of sorting by formatting of cells.

  7. Optional: Select the entry and click on Copy Level button in the Sort dialog box to copy a column/field to sort by.
  8. Optional: Select the entry and click on Delete Level button in the Sort dialog box to delete a column/field to sort by.
  9. Optional: To change the order in which the columns or fields in the spreadsheet need to be sorted, select an entry of the Column Name and then click on the Up or Down arrow button, next to the Options button to change the order of sorting, provided entries higher in the list would be sorted before entries which appear lower in the list.
  10. Click on OK.

Sorted Spreadsheet will be obtained.

Sorting using Custom List in Excel

A custom list is a dummy list defined by the user to sort the values of a list in an arrangement or order that is user-defined. This may also be considered as a special way of conditional formatting, wherein values contained in a custom list are textual, based on which further sorting is performed.

For example, consider a list defined by A, B and C group of employees, wherein the user-defined order wants to sort list A, C and B in the order of employees. This problem is compounded by the issue of not being able to sort employees on the basis of their alphabetical nature, as this order is neither ascending alphabet nor descending alphabet. Creating a user defined list, also known as a custom list, is a way to solve the problem and achieve the desired sorted spreadsheet.

The following steps need to be performed in order to create the custom list and perform sorting:

  1. Creation of Custom List:
    1. Enter the values that you want to sort the values by, in the order you want them, from top to bottom, in different cells in the range of cells.
    2. Select the range of cells of the order in which you want to sort.
    3. Click on File or Office Button on the top left corner of Excel Application program, click on Excel Options at the bottom.
    4. Excel Options dialog box opens up.
    5. Click on Popular Tab, and click on Edit Custom Lists button under Top Options for Working with Excel.
    6. In the Custom Lists dialog box, click on Import list from cells to import the values of the custom list by selecting the range of cells that decide the order and finally, click on OK twice, to create the requisite Custom List.

    Note: A custom list can be created by the user only based upon values such as text, number and date or time, and cannot be created based on a format of cell color, font color or cell icon. Also, the maximum length for a custom list that can be created by the user is 255 characters and the first character of the custom list cannot begin with a number or a special character.

  2. 2. Sorting using Custom List:
    1. Select the cell or the range of cells in the column which needs to be sorted.
    2. Click on the Data Tab on the Menu bar, and click on Sort under Sort & Filter section.
    3. The Sort dialog box opens up. Select the column that you want to sort by a custom list under the Column list.
    4. Under the Order list, click on Custom List.
    5. In the Custom Lists dialog box, select the custom list that you want to use for sorting. In the above example, the created list of value A, C, B needs to be used.
    6. Click on OK.

Sorted Spreadsheet using Custom List would be obtained.

Advantages of Excel Sort

  1. Produces ordered arrangement of spreadsheet.
  2. Facilitates efficient decision making process.
  3. Results in effective analysis and investigation of data.
  4. Aids the process of searching, organizing and formatting data.

In this post, you learned about how to use Excel sort functionality. Awesome! Make your work easier now by using the Excel sort you just learned! 🙂

Comments are closed.


Magoosh blog comment policy: To create the best experience for our readers, we will only approve comments that are relevant to the article, general enough to be helpful to other students, concise, and well-written! 😄 Due to the high volume of comments across all of our blogs, we cannot promise that all comments will receive responses from our instructors.

We highly encourage students to help each other out and respond to other students' comments if you can!

If you are a Premium Magoosh student and would like more personalized service from our instructors, you can use the Help tab on the Magoosh dashboard. Thanks!