How to sort by row, column and custom list in Excel

Contents

Sorting on multiple columns
Sort data in Excel by row and column headings
Random sorting (custom list)
- Sort data by your own custom list

In this article, I will show you how to sort data in Excel by multiple columns, by column headings in alphabetical order, and by values in any row. You will also learn how to sort data in non-standard ways when sorting alphabetically or by numerical value is not applicable.

I think everyone knows how to sort by a column in alphabetical order or ascending/descending order. This is done with the click of a button. AND I (AZ) and Z-A (ZA) in section Editing (Editing) tab Home (Home) or in the section Sort and filter (Sort & Filter) tab Data (Data):

However, sorting in Excel has many more customizable options and modes of operation that are not so obvious, but can be very convenient:

Sorting on multiple columns

I will show you how to sort data by two or more columns in Excel. The work of the tool is shown on the example of Excel 2010 – this version is installed on my computer. If you’re using a different version of the application, you shouldn’t have any problems because sorting works in much the same way in Excel 2007 and Excel 2013. The difference can be seen only in the colors of the dialog boxes and the shape of the buttons. So let’s get started…

Click on the button Sorting (Sort) tab Data (Data) or by button Custom sorting (Custom sort) tab Home (Home) to open the dialog Sorting (Sort).
In the dialog box that appears, by clicking the button Add level (Add Level), set the desired number of columns by which sorting will be performed.
In drop down lists Sort by (Sort by) и Then by (Then by) select the columns by which you want to sort the data. For example, you are planning how to spend your weekend and you are looking at a list of hotels offered by a travel agency. Do you want to sort this list first by geography (column Region), then according to the power system (column Board basis) and finally by price (column Price), as shown in the figure below:
Click OK and see what happens:
- Column values sorted first Region – In alphabet order.
- Then the values in the column are ordered Board basis so hotels offering All Inclusive (AI) are grouped at the top of the list.
- And finally by the cost in the column Price the data is in ascending order.

Sorting data by multiple columns in Excel was not difficult at all, right? However, in the dialog box Sorting (Sort) has much more possibilities. Later in this article, I’ll show you how to sort by row instead of column, and how to sort data on a worksheet alphabetically by column headings. You will also learn how to sort data in non-standard ways when sorting alphabetically or by numerical value is not applicable.

Sort data in Excel by row and column headings

I believe that 90% of the time Excel sorts data by value in one or more columns. However, sometimes there are not so simple datasets that need to be sorted by row (horizontally), that is, reorder columns from left to right, based on column headings or values in a particular row.

Here is a list of cameras provided by your local dealer or downloaded from the internet. The list contains a variety of features, specifications, and prices, and looks something like this:

We need to sort this list of cameras according to the parameters that are most important to us. For example, first of all, let’s sort by model name:

Select the range of data to be sorted. If we need to change the order of all columns as a result of sorting, then it is enough to select any cell within the range. But in the case of our data set, this method is not allowed, since in the column A camera specifications are listed, and we need it to stay where it is. Therefore, we select the range, starting from the cell B1:
On the Advanced tab Data (Data) press the button Sorting (Sort) to open the dialog box of the same name. Pay attention to the parameter My data contains headers (My data has headers) in the upper right corner of the dialog box. If there are no headers in your data, then there should not be a checkmark there. Headings are present in our table, so we leave this checkbox and press the button Parameters (Options).
In the opened dialog box Sorting options (Sort Options) in the section Sort (Orientation) choose an option Range columns (Sort left to right) и жмём OK.
The next step is in the dialog box Sorting (Sort) under heading Line (Row) in drop down list Sort by (Sort by) select the row by which values will be sorted. In our example, we select the line 1in which the names of the cameras are recorded. In the dropdown list under the heading Sorting (Sort on) must be selected The values (Values), and under the heading Order (Order) establish From A to Z (A to Z).

As a result of sorting, you should get something like this:

In our example, sorting by column headings has no serious practical value and is done only to show you how it works. In the same way, we can sort our list of cameras by the row that specifies dimensions, resolution, sensor type, or any other parameter that we deem more important. Let’s do another sort, this time by price.

Our task is to repeat steps 1 – 3 described above. Then at step 4, instead of the line 1 select a line 4, which contains retail prices (Retail Price). The sorted table will look like this:

Please note that the data was sorted not only in the selected row. Whole columns are swapped, but the data is not shuffled. In other words, the screenshot above is a list of cameras, sorted from cheapest to most expensive.

I hope it’s now clear how sorting by row works in Excel. But what if our data needs to be sorted in a non-alphabet and non-ascending/descending order?

Random sorting (custom list)

If you need to arrange the data in some special order (not alphabetically), then you can use the custom lists built into Excel or create your own. With built-in customizable lists, you can sort by days of the week or months of the year, for example. Microsoft Excel offers two types of such ready-made lists – with abbreviated and full names.

Let’s say we have a list of weekly chores around the house and we want to sort them by day of the week or by importance.

We start by selecting the data to be sorted and opening the dialog box Sorting (Sort), just like in the previous examples − Data > Sorting (Data > Sort).
In the Sort by (Sort by) select the column by which you want to sort. We want to organize our tasks by day of the week, that is, we are interested in the column Day. Then in the dropdown list under the heading Order (Order) choose an option Custom List (Custom list) as shown in the screenshot below:
In the dialog box Lists (Custom Lists) in the field of the same name, select the desired list. in our column Day the abbreviated names of the days of the week are indicated – click on the corresponding list option and click OK.

Ready! Now household chores are ordered by day of the week:

Note: If you plan to make changes to this data, keep in mind that new data added or changes to existing data will not be sorted automatically. To redo sorting, click the button Retry (Reapply) section Sort and filter (Sort & Filter) tab Data (Data).

As you can see, sorting data in Excel according to a custom list is not a difficult task at all. Another trick we need to learn is sorting data into our own custom list.

Sort data by your own custom list

Our table has a column Priority – it indicates the priorities of tasks. To use it to organize your weekly tasks from most important to least important, follow these steps:

Repeat steps 1 and 2 from the previous example. When the dialog box opens Lists (Custom Lists), in the column of the same name on the left, click NEW LIST (NEW LIST) and fill in the required values in the field List items (List entries). Carefully enter the elements of your list in the exact order in which they should be sorted.

Press Add (Add), and the list you created will be added to the existing ones. Next click OK.

This is what our list of household chores looks like, sorted by importance:

Tip: To create long custom lists, it’s easier and faster to import them from an existing range. This is discussed in detail in Create a custom list from an existing Excel sheet.

With custom lists, you can sort on multiple columns by using a different custom list for each column. To do this, follow the same sequence of steps as when sorting by multiple columns in the previous example.