Sorting data is an integral part of Data Analysis. You can arrange a list of names in alphabetical order, compile a list of sales figures from highest to lowest, or order rows by colors or icons. Sorting data helps you quickly visualize 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 list (E.g. Large, Medium, and Small)
- format, including cell color, font color, or icon set
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, click the header of the column – Title.
- Click the Data tab.
- In the Sort & Filter group, click 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 clicking Sort Z to A. You can also sort with the 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 −
- Click the header of the column – ManagerID.
- Click the Data tab.
- In the Sort & Filter group, click Sort A to Z
The column, ManagerID will be sorted in the ascending numeric order. You can sort in the descending numeric order, by clicking 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 −
- Click the Header of the column – HireDate.
- Click the Data tab.
- In the Sort & Filter group, click Sort A to Z as shown in the screenshot 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 clicking Sort Z to A.
Sort by Cell Color
To sort the table by the column total marks that contain cells with colors (Conditionally Formatted) −
- Click the Header of the column – Total Marks.
- Click the Data tab.
- In the Sort & Filter group, click Sort. The Sort dialog box appears.
- Choose Sort By as Total Marks, Sort on as Cell Color, and specify the color green in Order. Click Add Level.
- Choose Sort By as Total Marks, Sort on as Cell Color, and specify the color Yellow in Order. Click 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.
