Excel Sort By Column, By Row, By Color, By Your Own Custom List

22 August 2017

Excel Sort By Column, By Row, By Color, By Your Own Custom List

'Sort' is one of the tricky and very useful tools available in Microsoft Excel. This tool has immense power to reduce our job by several times. In this article I will show you How to Sort data Column wise, Row wise, Color wise and How to Customize Sort with your own list and many more things in detail step by step.

So, lets' start from basic to advanced level of Sort.

How to sort by column alphabetically or in ascending / descending order

It is very simple, all you need to do is click the Sort A to Z or Sort Z to A buttons available on the Home tab in the Editing group and on the Data tab in the Sort & Filter group as shown in the below picture.

Basic Sort option in Excel

Microsoft Excel's Sort feature provides more options and powerful capabilities that are not very difficult to master. Let us discuss Sorting of Columns and Rows.

How to Sort by columns, More than One

Sorting one column is very easy as mentioned above now I'm going to show you how to sort Excel data by two or more columns step by step.

1. Go to Data Menu and Click the Sort button or Custom Sort on the Home Menu to open the Sort dialog. I personally prefer to use sort from Data menu. This will open a new window as shown below.

Sort Window in Microsoft Excel

2. Then click the Add Level (Circled in blue) button as many times as many columns you want to use for sorting

3. From the "Sort by" and "Then by" drop down lists, select the columns by which you want to sort your data.

In the below picture I have selected Region, Territory Code and Employee Code. Now here we get few additional options Sort On & Order, if you click on Sort On drop down which in the picture is beside 'Values' word, you will find few more options which we will discuss letter. Order is how you want your sorting to be, from A to Z or Z to A and for numbers either ascending or descending.

Sort on Multiple Column

4. Click OK and you are done, the table is now sorted.

Sometimes you may notice that Column Names are not showing in the Sort window, as shown in the below picture. To resolve this click to put a tick mark in the checkbox (my data has headers) at the right corner of this window circled by blue color and now Column headers will be visible.

Sort Multiple Column with Column Header

How to Sort by Rows

Generally we use sorting to data for columns only but some situations come when we need to sort data row wise, therefore, lets' see how it is done.

1. Go to Data Tab click on Sort option which will open Sort window.

2. Click on the Option button at the right corner of this window which will open another small window as Sort Options

Sort by Rows - Left to Right

3. Here you will find three one checkbox asking whether sorting to be done in Case sensitive mode or not.

4. Now there are two more options Sort top to bottom meaning Column wise sort and Sort left to right meaning row wise sorting. Select Sort left to right option and click Ok.

5. The next process is same as we have done for column wise sorting i.e., Add Level, Sort on and Order.

Please refer to the below image which I have sorted row wise (row 1, 2 & 3)

Sort by Row

How to Sort by Color

This part of Microsoft Excel is most interesting and amazing. While preparing data for analytical purpose this property of Sort will help you most, Sort by Cell color or Sort by Font color or Sort by Cell icon. For example let suppose I have one column "Product 1" in one table as shown below with four colors green, red, blue and yellow.

Sort by Cell Color

Now if I need to sort it by Blue, Green, Yellow and lastly Red then firstly I have to open Sort window from Data and then by clicking on Sort button.

Now select Product 1 from the Sort by Drop down, then click on Sort on drop down list and select Cell Color, a new option will appear "No Cell Color", click in this drop down button.

Now you can see the same four colors which were applied in the Product 1 column. Select any one color, in my case it is Blue. Now you need to insert the same level, lets' use a shortcut, click on Copy Level, the same level will appear, select your color order one by one, click OK and you are done.

How to Sort by Cell Color

How to Sort data in custom order (using your custom list)

If you want to sort your data in some custom order other than alphabetical, you can use the built-in Excel custom lists or create your own. With built-in custom lists, you can sort by days of the week or months of the year. Microsoft Excel provides two types of such custom lists - with abbreviated and full names, as shown below

Sort by Custom List

Suppose we need a custom list to sort on regions as North then East then West and lastly South.

To do this we need to do the following steps

1. Open the Sort window from Data then Click on Sort button,

2. In the Sort by box, select the column you want to sort by, in our case it is Region. Then choose Custom List under Order as shown below

Sort by Custom List

3. In the Custom Lists dialog box, click on New List, and enter the list items, in our case these will be North, East, West, South, separated by comma. Click OK.