How to Sort Columns in Excel without Mixing Data (3 Ways)

How often do you need to sort in Excel? The generic answer is, “Every now and then”. Sorting is one of the most common tasks while dealing with data. To help you sort correctly, today we are going to show you how to sort columns in Excel without mixing data.

First things first, let’s get to know about today’s practice workbook.

Excel sheet - How to Sort Columns in Excel without Mixing Data

We have a basic table that contains the name of students and their respective scores in three courses. Using this relationship table, we will see how to sort columns without mixing data.

Note that this is a basic set of dummy data to create a simple scenario, in real cases you may encounter a much larger and complex data set.

Practice Workbook

You are welcome to download the practice workbook from the link below.

Sort Columns without Mixing Data

1. Sort with Excel Tools

Excel provides you the option to sort the data using its built-in tool. You can easily sort your data using the tools.

Select the range or column you want to sort.

Select column - How to Sort Columns in Excel without Mixing Data

Here we have selected the Student Name column. We intend to sort the name by A to Z.

After selecting the column, explore the Data tab. You will find the Sort & Filter option there. You will find several options there. Select as per your need.

Sort & Filter - How to Sort Columns in Excel without Mixing Data

Here our choice was A to Z.

Our data has been sorted, see the image below.

Error sort - How to Sort Columns in Excel without Mixing Data

Oh, dear! Though we have found the name sorted, the scores are not sorted. The scores are improper for each student now.

So what can we do now?

Let’s roll back to the initial stage of our data.

Select all the data you want to count within your sort.

Select Columns - How to Sort Columns in Excel without Mixing Data

Here we have selected all the columns except Serial No. Serial should remain as it is.

Then select the Sort option from the Sort & Filter section of the Data tab.

Select Sort - How to Sort Columns in Excel without Mixing Data

A dialog box will come in front of you. You can choose your sort preference there.

Sort Dialog box - How to Sort Columns in Excel without Mixing Data

Make sure to check the My data has headers. We have selected 4 columns here, so we can sort by either of them.

Select column to sort by - How to Sort Columns in Excel without Mixing Data

Drop down, you will see the header options. Select what you want.

Select column - Sort on & order - How to Sort Columns in Excel without Mixing Data

Here we have selected the Student Name column. Our sort was on the Cell Values and order was A to Z. You can change these by clicking the drop-down icon of the respective selection.

Sort properly - How to Sort Columns in Excel without Mixing Data

Now our data has been sorted. We found the name alphabetically and the corresponding scores are also intact.

I. Sort Warning

Do you know good things about Excel? Yes, there are many, but right now our consideration is all about sort.

The good thing for sort in Excel is that it will give you a warning before triggering the sort.

Select any of the columns. Then explore the Sort & Filter section from the Data tab.

Sort Warning - How to Sort Columns in Excel without Mixing Data

Here we have selected the Student Name column. And to sort it alphabetically we are going to click A to Z.

Once you don’t select the entire table while sorting, Excel will pop up a warning.

Sort Warning dialog box - How to Sort Columns in Excel without Mixing Data

Here we have two options. If you explicitly need to continue with the selected column (s) then select the Continue with the current selection. This option will only filter the selected cells.

Usually, you need to use the Expand the selection, this option will count all the columns in your table.

Here we are using Expand the selection.

Sort Expand selection - How to Sort Columns in Excel without Mixing Data

Here the name has been sorted alphabetically. And all the other values have also been changed their places.

II. Custom Sort to Prevent Mixing Data

You can sort the data by your own choice. Excel has the feature of the custom sort to help you regarding that.

Here we have changed our data a little to show you examples.

Change in Data - How to Sort Columns in Excel without Mixing Data

We have added a new column that contains the month when students appeared for their respective exams.

As we said earlier, we don’t want to change the serial number (since it’s already in a sequence), we are selecting other columns apart from that.

Select Sort - How to Sort Columns in Excel without Mixing Data

Select the Sort from the Sort & Filter. A Dialog box will come in front of you.

Select Custom List-How to Sort Columns in Excel without Mixing Data

From Order click the drop-down icon, you will see an option there called Custom List. Click that.

A new Custom lists dialog box will appear.

Select list from custom list

For your convenience, Excel listed a couple of lists there. Here we have selected the months from there. You can choose depending on your needs.

Sort by - order

Our selected column was Exam Schedule and the order was by months from January to December. You can set your custom list there.

Custom sort result

Now our data has been sorted with the column Exam Schedule by globally sequential months.

2. SORT Function to Sort Columns Without Mixing Data

If you are using Excel 365, then you can use a function called SORT. 

The SORT function sorts the contents of a range or array in ascending or descending order.

SORT (array, [sort_index], [sort_order], [by_col])

array: The range, or array to sort

sort_index: A number indicating the row or column to sort by. This is an optional field. The default value is 1.

sort_order: A number indicating the desired sort order. 1 = Ascending, -1 = Descending. This is also an optional field. The default value is 1 (ascending).

by_col: A logical value indicating the desired sort direction. TRUE = sort by column. FALSE = sort by row. Default is FALSE

To know more about the function please visit the Microsoft Support site.

Make use of this function in Excel.

SORT function

Here we have entered all the values from the Student Name column to the History column. This was our range. We have several columns within our range, as we wanted to sort by name, which was our 1st column in the range, we inserted 1 as sort_index. 

Read more: How to Sort by Last Name in Excel

We can skip this since the default value is 1 for that field. Our Data has been sorted with alphabetically ordered names.

SORT function 2

For ascending order we can insert 1 in the sort_order field as well (you can see in the above image).

For any reason, if you need to sort in descending order use -1 instead of 1 in the sort_order field.

Sort descending order

Here we have used -1 as sort_order and the names have been sorted from Z to A.

3. Sort Columns Without Mixing Data using SORTBY Function

You can use the SORTBY function to sort your data. This is a family function of the previously discussed SORT function.

The SORTBY function sorts the contents of a range or array based on the values from another range or array.

SORTBY (array, by_array, [sort_order], [array/order], ...)

array: Range or array to sort

by_array: Range or array to sort by

sort_order: The order to use for sorting. 1 for ascending, -1 for descending. Default is ascending

array/order: Additional array and sort order pairs.

The latter two are optional.

To know more about the SORTBY function, visit the Microsoft Support site.

Write it in Excel.

SORTBY function

Similar to the previous, we have selected the array. Our by_array was the Student Name column. Can you relate SORT and SORTBY?

In SORT we have provided the column number, which was from our selected array, here in SORTBY we have provided the column differently (as an array).

SORTBY allows you to sort depending on any outside column or range.

Let’s change the by_array.

SORTBY different columns

Here we have selected the Math column as our by_array. The Math column had been sorted in ascending order. All the other column values have been changed places respectively.

Conclusion

That’s all for today. We have listed several ways to sort columns without mixing data. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know which of the methods you are going to use. You can also notify us of any of the approaches we might have missed here.


Further Readings

Shakil Ahmed

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo