In Excel, you can sort text, numbers, date and time. But Excel goes beyond this and also allows you to sort data by cell color, font color and custom list etc. It is also possible to sort rows in Excel. In the following tutorials, we will use a sample Excel datasheet. You can download this sheet so as to follow the sort examples given below. This sheet contains sample data related to students of a class.
Quick Sorting Excel Data
Before we plunge into any fancy sorting, let’s first understand how to quickly sort a column in Excel. There are buttons in Data tab (Sort & Filter group) for quickly sorting a column.
Sort Warning
When you do quick sort in Excel, chances are that you will receive a sort warning. This warning is very important and you must understand what it says otherwise your data may become inconsistent. The warning reads like: Here Excel is trying to advice you that you should expand your selection. Since you’ve selected only one column, and if only that column gets sorted —then the overall data will become meaningless. Actually, you should select all the data (i.e. all the columns) while using sort command. But if you don’t select all the columns, Excel will warn you. TIP: to quickly select all the data, select any cell within the data and then press Ctrl+A. In most cases you’ll need to select the “Expand selection” option. Excel will automatically select all the columns adjacent to the column you had selected. Then Excel will sort the column selected by you and also change data in other columns to preserve the data integrity. Only in rare cases you would want to use the second option of “Continue with the current selection”. So, be careful here!
Sort the Whole Excel Sheet
Sort Two or More Columns
More often than not, we need to sort two or more columns in Excel. For example, we may want to sort Student Name column and then sort Marks Secured column. What we want here is that first all the student names should get alphabetically sorted and then the Marks Secured column should get sorted. In our example, the datasheet has been sorted by Student Name and then by Marks Secured columns.
Sort a Cell Range
Excel allows you to sort not only the entire column but also a selected cell range. Sometimes you may want to sort only a portion of columns. For this you can select a cell range and do the required sort within that cell range. As you can see, names of students are sorted and then course names are are also sorted. CAVEAT: You should be careful while sorting a cell range. As you can see, the above sort process has made the overall data in rows wrong! Gia who had actually scored 87 marks in Physics, now has 91 marks in that subject. This happens because Excel has sorted only the selected cell range. The adjacent data remained unmoved while the data in cell range changed due to sort function. So, be careful and sort a cell range only when you know what you’re doing. Mostly such sorting is done only to be undone. Usually people sort a cell range to see the result and then press Ctrl+Z to undo such sorting.
Create Your Own Sort Order With Custom List…
This is very interesting facility provided by Excel. We have seen that Excel can sort data in ascending or descending order. But sometimes we do not want to sort our data with the regular definition of order. We want to create our own custom order and then sort the data with this custom order. To demonstrate this, we have added a new column in our sample data sheet. Name of this new column is Course Level. There are three possible values for the Course Level column; Beginner, Intermediate and Advanced. We have assigned the appropriate values to various student records. The updated data sheet now looks like this: As we know, Beginner level comes first and then comes the Intermediate level. At the end Advanced level comes. But if we alphabetically sort this data on Course Level column, the sort order would be Advanced, Beginner, Intermediate. This is clearly not what we want! We want to sort data so that all the Beginner level rows come first followed by Intermediate rows and the rows with Advanced level should come in the end. This is where custom sort can help us! Following example demonstrate how you can do custom sorting:
Sort by Cell Formatting in Excel
Yet another great facility provided by MS Excel is that you can sort a column on the basis of the cell formatting. For example, you can ask Excel to sort by cell color or font color or cell icon. To give you a demonstration of this feature, we will sort our sample datasheet by House Assigned column. This column contains cells of various colors which represent the House to which a student is associated. Let’s say we want to see on top all the rows where student belongs to the Blue house. Similarly, you can also sort data by Font Color and Cell Icon.
How to Sort by Rows in Excel
So far we have seen various methods of sorting Excel data by columns. But is it possible to sort Excel data by rows? Yes! You can easily sort data on the basis of rows and the steps are almost same as in case of sorting columns. Sorting by rows means that the process is done from left to right instead of top to bottom. We can also choose between ascending and descending order. If we select ascending order, then the smallest value will be found of the left-most side and the biggest value will be on the right-most side. Descending order will be opposite of this.
More Tips for Excel Data Sort
We hope that this article on sorting of Excel data was helpful for you. Although sorting is a simple and often used function, still there could always be new things that you can learn about simple procedures! Should you have any questions regarding this topic, please feel free to ask in the comments section. We will try our best to assist you. Thank you for using TechWelkin!
