Excel Data Analysis - Sorting

Sorting data is an integral part of Data Analysis. You can arrange a pst of names in alphabetical order, compile a pst of sales figures from highest to lowest, or order rows by colors or icons. Sorting data helps you quickly visuapze and understand your data better, organize and find the data that you want, and ultimately make more effective decisions.

You can sort by columns or by rows. Most of the sorts that you use will be column sorts.

You can sort data in one or more columns by

text (A to Z or Z to A)

numbers (smallest to largest or largest to smallest)

dates and times (oldest to newest and newest to oldest)

a custom pst (E.g. Large, Medium, and Small)

format, including cell color, font color, or icon set

Sort criteria for a table are saved with the workbook such that you can reapply the sort to that table every time you open the workbook. Sort criteria are not saved for a range of cells. For multicolumn sorts or for sorts that take a long time to create, you can convert the range to a table. Then, you can reapply the sort when you open a workbook.

In all the examples in the following sections, you will find tables only, since it is more meaningful to sort a table.

Sort by Text

You can sort a table using a column containing text.

The following table has information about employees in an organization (You are able to see only the first few rows in the data).

To sort the table by the column title that contains text, cpck the header of the column – Title.

Cpck the Data tab.

In the Sort & Filter group, cpck Sort A to Z

The table will be sorted by the column – Title in the ascending alphanumeric order.

Note − You can sort in the descending alphanumeric order, by cpcking Sort Z to A. You can also sort with case-sensitive option. Go through the Sort by a Custom List section given below.

Sort by Numbers

To sort the table by the column ManagerID that contains numbers, follow the steps given below −

Cpck the header of the column – ManagerID.

Cpck the Data tab.

In the Sort & Filter group, cpck Sort A to Z

The column, ManagerID will be sorted in the ascending numeric order. You can sort in the descending numeric order, by cpcking Sort Z to A.

Sort by Dates or Times

To sort the Table by the column HireDate that contains Dates, follow the steps given below −

Cpck the Header of the column – HireDate.

Cpck Data tab.

In the Sort & Filter group, cpck Sort A to Z as shown in the screen shot given below −

The column – HireDate will be sorted with the dates sorted from oldest to newest. You can sort the dates from newest to oldest, by cpcking Sort Z to A.

Sort by Cell Color

To sort the table by the column total marks that contains cells with colors (Conditionally Formatted) −

Cpck the Header of the column – Total Marks.

Cpck Data tab.

In the Sort & Filter group, cpck Sort. The Sort dialog box appears.

Choose Sort By as Total Marks, Sort on as Cell Color and specify the color green in Order. Cpck Add Level.

Choose Sort By as Total Marks, Sort on as Cell Color and specify the color Yellow in Order. Cpck Add Level.

Choose Sort By as Total Marks, Sort on as Cell Color and specify the color Red in Order.

The column – Total Marks will be sorted by the cell color as specified in the Order.

Sort by Font Color

To sort the column Total Marks in the table, that contains cells with font colors (conditionally formatted) −

Cpck the header of the column – Total Marks.

Cpck Data tab.

In the Sort & Filter group, cpck Sort. The Sort dialog box appears.

Choose Sort By as Total Marks, Sort On as Font Color and specify the color green in Order. Cpck Add Level.

Choose Sort By as Total Marks, Sort On as Font Color and specify the color yellow in Order. Cpck Add Level.

Choose Sort By as Total Marks, Sort On as Font Color and specify the color red in Order.

The column – Total Marks is sorted by the font color as specified in the Order.

Sort by Cell Icon

To sort the table by the column Total Marks that contains cells with Cell Icons (Conditionally Formatted), follow the steps given below −

Cpck the Header of the column – Total Marks.

Cpck Data tab.

In the Sort & Filter group, cpck Sort. The Sort dialog box appears.

Choose Sort By as Total Marks, Sort On as Cell Icon and specify in Order. Cpck Add Level.

Choose Sort By as Total Marks, Sort On as Cell Icon and specify in Order. Cpck Add Level.

Choose Sort By as Total Marks, Sort On as Cell Icon and specify in Order.

The column – Total Marks will be sorted by Cell Icon as specified in the Order.

Sort by a Custom List

You can create a custom pst and sort the table by the custom pst.

In the table given below, you find an indicator column with title – Position. It has the values high, medium and low based on the position of total marks with respect to the entire range.

Now, suppose you want to sort the column - Position, with all High values on top, all low values at bottom, and all medium values in between. That means the order you want is low, medium and high. With Sort A to Z, you get the order high, low and medium. On the other hand, with Sort Z to A, you get the order medium, low and high.

You can resolve this is to create a custom pst.

Define the order for the custom pst as high, medium and low in a range of cells as shown below.

Select that Range.

Cpck the File tab.

Cpck Options. In the Excel Options dialog box, Cpck Advanced.

Scroll to the General.

Cpck Edit Custom Lists.

The Edit Custom Lists dialog box appears. The select range in worksheet appears in the Import pst from cells Box. Cpck Import.

Your custom pst is added to the Custom Lists. Cpck OK.

The next step is to sort the table with this Custom List.

Cpck the Column – Position. Cpck on Sort. In the Sort dialog box, ensure Sort By is Position, Sort On is Values.

Cpck on Order. Select Custom List. Custom Lists dialog box appears.

Cpck on the High, Medium, Low Custom List. Cpck on OK.

In the Sort dialog box, in the Order Box, High, Medium, Low appears. Cpck on OK.

The table will be sorted in the defined order – high, medium, low.

You can create Custom Lists based on the following values −

Text

Number

Date

Time

You cannot create custom psts based on format, i.e. by cell / font color, or cell icon.

Sort by Rows

You can sort a table by rows also. Follow the steps given below −

Cpck the row you want to sort the data.

Cpck Sort.

In the Sort dialog box, Cpck Options. The Sort Options dialog box opens.

Under Orientation, cpck Sort from left to right. Cpck OK.

Cpck Sort by row. Select the row.

Choose values for Sort On and Largest to Smallest for Order.

The data will be sorted by the selected row in a descending order.

Sort by more than one Column or Row

You can sort a table by more than one column or row.

Cpck the Table.

Cpck Sort.

In the Sort dialog box, specify the column by which you want to sort first.

In the screen shot given below, Sort By Title, Sort On Values, Order A – Z are chosen.

Cpck Add Level in the Sort dialog box. The Then By dialog appears.

Specify the column by which you want to sort next.

In the screen shot given below, Then By HireDate, Sort On Values, Order Oldest to Newest are chosen.

Cpck OK.

The data will be sorted for Title in the ascending alphanumeric order and then by HireDate. You will see the employee data sorted by title, and in each title category, in the seniority order.