Sort by color in Excel (Table of Contents)
Sorting in Excel
Sorting in excel is nothing but arranging data stored in excel according to your requirement. It can be done by depending on the value type stored in cells. Some common examples include sorting alphabetically (A to Z or Z to A), by value (largest to smallest or smallest to largest), by day of the week (Mon, Tue, Wed..), or by month names (Jan, Feb..) etc. Sorting in Excel can be done by Filter Method depending on the type of data (number or text), there are many ways to sort data using the filter method. With basic sorting, there are few problems like multiple columns in sort not available, customized list sorting cannot be done with basic sorting. To overcome this, Advanced sorting option is available in Excel, by using that we can eliminate the above-mentioned limitations, also it has wonderful features of sorting depending upon cell color and cell icon. (Explained later in this guide), Multi-level sorting can be done by advanced sorting. You can also build your own criteria in Excel for sorting. To sort any particular dataset, select the entire dataset, go to Data tab click on SORT Option. The hotkey is (Alt key + A+ SS).
Examples of Sorting in Excel
Let’s understand the following examples of sorting in excel.
Example#1
- Suppose you have data as shown below:
- Now the data in the above image can be sorted based on employee name i.e. alphabetically (A to Z or Z to A) or on the basis of Total sales. (numerically- lowest to highest or highest to lowest).
- While in the above case, sorting is done on basis of one column, but what if you have data something like in the below image, So to do sorting on basis of 2 or more columns, you need to take some additional steps.
- In the above illustration, you want to sort data at multiple levels, E.g. You want to first sort data by reporting manager, then by employee name and then by order total (highest to lowest). Why it is required? Suppose you have to find out which employee of every manager has done the maximum order sales.? You can find out answers to queries like this by using multi-level sorting. This can be only be done by advanced sorting. So to sort data at multiple levels. Simply select the entire dataset, and Press (Alt key +A+ SS).
- This will open a dialogue box like this:
- In the above dialogue box, you have to fill your requirements for sorting in cells and add levels one by one.
- Now as shown in the above image, I have applied sorting criteria, it will give results something like this:
Options available in sorting dialogue box:
- Add Level: to add different levels to mention sorting criteria depending on the dataset.
- Delete Level: to delete any particular level
- Level: to copy any particular level. (Generally used when sorting is required on the same column but with different values.)
- Arrow Buttons: to move any level above or below.
- Options: Here you can select the level orientation of sorting and if values are case sensitive or not.
- My Data Headers: Check if your dataset has headers.
- Columns: This will contain all headers of your data.
- Sort on: Here you can mention you want to sort data on basis of cell values, cell color, Font color or conditional formatting.
- Order: Data should be sorted A-Z, Z-A, smallest to largest, largest to a smallest or customized list.
You can look on some of them in below Screenshots.
Sorting Options
The column having all headers
Sorting Options
Example#2
- Now taking the same data as given in the first example, you are going through all the orders and you have doubt that few orders are wrongly punched or you have some issues with it. You have highlighted all the orders having issues with yellow color. Suppose,
- Orders wrongly punched marked with red.
- Orders with a total value less than 1000 are marked with yellow.
- So we will select the data from the sheet.
- Now you want to sort data so that you can find out which employee of each Reporting Manager has punched wrong orders and orders with the value less than 1000 you will give sorting criteria.
- And the sorted data will be like this:
There is another way to sort data at multi-levels(Explained later). However, the above-mentioned method has more steps as compared to multi-level sorting method, it is still recommended to use this one because it is less confusing. Multi-level sorting can be done in two ways: –
- By using Dialogue box (Discussed in above 2 Examples)
- Using Sort Icons
Example#3
- We will use the same data of previous examples, now using the sorting icon. Both the methods mentioned above works in the same way, the only difference is that here in the sorting icon method, the last level is sorted first and then goes to first level Viz. sorting is done from bottom to top. Confusing? Isn’t it?
- So now, select the column you want to sort last. (E.g. Order Total in our case).
- Now go to Data Table and Sort and click on Z-A to. (This will sort the order total from largest to smallest). Hot Key (Alt Key +A+ SD).
- Excel is smart enough to recognize that you have data stored in column behind or next to it. After clicking the sorting icon, a pop-up window will appear, it will ask you whether to expand the selection? Or continue with current selection? Make sure to expand the selection.
- And the result is shown in the below image.
- Now, select the Employee name column and repeat the above steps.
- Here click on A-Z option in Sort & Filter. Hot Key (Alt Key +A+ SA).
- And the result is shown below.
- And same steps for Reporting Manager select the Reporting Manager Column.
- And then click on A-Z option in Sort. Hot Key (Alt Key +A+ SA).
- And the result is shown below.
While both the methods work fine, but it is still recommended to using dialogue box method because of it is lesser prone to errors, as we can see clearly which level is getting sorted in which order.
Things to Remember about Sorting in Excel
- Sorting in Excel is arranging data according to our requirements.
- It can be done alphabetically or numerically.
- Basic Sorting works when sorting is to do on only one column.
- Advanced Sorting is used in multi-level sorting viz sorting required in 2 or more than 2 columns.
- Advanced Sorting can be done by two methods:
- Sorting Dialogue Method
- Sorting Icon Method
Recommended Articles
This has been a guide to Sorting in Excel. Here we discuss the Sorting in Excel and how to use the Sorting in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
