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.
How to Sort Columns in Excel Without Mixing Data (6 Easy Ways)
First things first, let’s get to know about today’s practice workbook.
We have a basic table that contains the names 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 more complex data set.
Now, 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.
- Here we have selected the Student Name column. We intend to sort the names 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.
- Here our choice was A to Z.
- Our data has been sorted, see the image below.
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? Keep reading this article to explore the solutions.
1. Selecting All Necessary Columns Before Sorting
Let’s roll back to the initial stage of our data. Now follow the steps below.
📌 Steps:
- Select all the data you want to count within your sort.
Here we have selected all the columns except Serial No. The serial should remain as it is.
- Then select the Sort option from the Data tab.
- A dialog box will come in front of you. You can choose your sort preference there.
- Make sure to check the My data has headers.
- We have selected 4 columns here, so we can sort by either of them.
- Drop down, you will see the header options. Select what you want.
- Here we have selected the Student Name column. Our sort was on the Cell Values and the order was A to Z.
- You can change these by clicking the drop-down icon of the respective selection.
Now our data has been sorted. We found the names alphabetically and the corresponding scores are also intact.
Read More: How to Undo Sort in Excel
Similar Readings
- Auto Sort in Excel Without Macros
- How to Sort in Excel by Number of Characters
- Excel Sort by Column Without Header
- Excel Sort Column by Value
- How to Sort Multiple Columns in Excel Independently of Each Other
- How to Sort Alphabetically in Excel with Multiple Columns
- How to Perform Custom Sort in Excel
- How to Add Sort Button in Excel
- How to Sort by Last Name in Excel
2. Using ‘Expand the Selection’ Option
Do you know good things about Excel? Yes, there are many, but right now our consideration is all about sorting.
The good thing about sorting in Excel is that it will give you a warning before triggering the sort.
📌 Steps:
- Select any of the columns. Then explore the Sort & Filter section from the Data tab.
- 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.
- Here we have two options. If you explicitly need to continue with the selected column (s) then select 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.
- Press the Sort option.
Here the name has been sorted alphabetically. And all the other values have also changed their places.
Read More: Excel Auto Sort when Data is Entered
Similar Readings
- Excel Auto Sort when Data Changes
- How to Sort Data in Excel by Value
- How to Sort Data in Alphabetical Order in Excel
- How to Put Numbers in Numerical Order in Excel
- How to Sort Alphanumeric Data in Excel
- How to Sort Multiple Columns in Excel
- How to Auto Sort Multiple Columns in Excel
3. Converting to Table
We can convert a normal data range to a table and then apply sorting to this. This will help to sort columns without mixing data. See the steps below.
📌 Steps:
- At first, select the cells in the B4:F14 range.
- Then, press the CTRL key followed by the T on your keyboard.
Immediately, the Create Table dialog box appears.
- Secondly, click OK.
As a result, the normal data range is converted into a table.
- Thirdly, select cells in the Student column.
- Then, go to the Data tab.
- Now, click on Sort A to Z.
You can see that the whole dataset became sorted along with this column.
Read More: How to Sort and Filter Data in Excel
Similar Readings
- How to Sort Two Columns in Excel to Match
- Excel Sort by Row not Column
- How to Sort Rows in Excel
- Excel Sort Rows by Column
- How to Sort Alphabetically in Excel and Keep Rows Together
- How to Arrange Numbers in Ascending Order in Excel Using Formula
- How to Sort by Date in Excel
- Excel Sort Dates in Chronological Order
- How to Sort Data in Excel Using Formula
- How to Sort Dates in Excel by Year
- Excel Sort by Date and Time
4. Creating a Custom Sort List to Prevent Mixing Data
You can sort the data by your own choice. Excel has the feature of the custom sort to help you with that.
Here we have changed our data a little to show you examples.
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.
📌 Steps:
- Select the Sort from the Sort & Filter.
- A Dialog box will come in front of you.
- From Order click the drop-down icon, and you will see an option there called Custom List. Click that.
- A new Custom Lists dialog box will appear.
- 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.
- Our selected column was Exam Schedule and the order was by months from January to December. You can set your custom list there.
Now our data has been sorted with the column Exam Schedule by globally sequential months.
Read More: How to Sort Dates in Excel by Month and Year
Similar Readings
- How to Sort Excel Sheet by Date
- How to Sort by Month in Excel
- Sort IP Address in Excel
- Random Sort in Excel
- How to Sort Birthdays in Excel by Month and Day
- How to Sort Excel Tabs
- How to Sort by Color in Excel
- How to Remove Sort by Color in Excel
- Advanced Sorting in Excel
- How to Sort Duplicates in Excel
- Excel Sort Unique
- How to Sort Numbers in Excel
5. Using SORT Function in Excel
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.
Syntax of SORT Function:
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. The default is FALSE.
📌 Steps:
- Make use of this function in Excel. The used formula is as follows.
=SORT(C5:F14,1)
- 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.
- We can skip this since the default value is 1 for that field. Our Data has been sorted with alphabetically ordered names.
- For ascending order, we can insert 1 in the sort_order field as well (you can see in the above image). Then the formula will be:
=SORT(C5:F14,1,1)
- For any reason, if you need to sort in descending order use -1 instead of 1 in the sort_order field.
=SORT(C5:F14,1,-1)
Here we have used -1 as sort_order and the names have been sorted from Z to A.
Similar Readings
- How to Auto Sort Table in Excel
- Advantages of Sorting Data in Excel
- Difference Between Sort and Filter in Excel
- Sort and Filter in Excel Not Working
- Excel Not Sorting Numbers Correctly
- Excel Sort by Cell Color Not Working
- Excel Sort Largest to Smallest Not Working
- How to Sort by Name in Excel
6. Sort Columns Without Mixing Data Using SORTBY Function in Excel
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.
Syntax of SORTBY Function:
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.
📌 Steps:
- To apply this formula, write the following formula in the desired cell:
=SORTBY(C5:F14,C5:C14)
- 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(C5:F14,D5:D14)
- 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.
Download Practice Workbook
You are welcome to download the practice workbook from the link below.
Conclusion
That’s all for today. We have listed several ways to sort columns in Excel 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.
Related Articles
- How to Remove Sort in Excel
- Excel Sort Not Working
- How to Sort Drop Down in Excel
- How to Sort Merged Cells in Excel
- How to Sort Merged Cells of Different Sizes in Excel
- Sorting Columns in Excel While Keeping Rows Together
- How to Use Excel Shortcut to Sort Data
- How to Sort by Ascending Order in Excel
- [Fix] Excel Sort by Date Not Working
- Excel Sort and Ignore Blanks
I was told that if you format the data as a table, then you can reorganise a column A-Z and the remaining data in the additional columns will follow. Is this correct? You don’t mention that method here
Hello REBECCA,
Thanks for your feedback. We are very glad to feel that our readers study our articles attentively. I am very sorry that method was not added. Now, I’m showing the way step by step for your convenience.
• At first, select the cells in the B4:F14 range.
• Then, press the CTRL key followed by the T on your keyboard.
Immediately, the Create Table dialog box appears.
• Secondly, click OK.
As a result, the normal data range is converted into a table.
• Thirdly, select cells in the Student column.
• Then, go to the Data tab.
• Now, click on Sort A to Z.
You can see that the whole dataset became sorted along with this column.
That’s all from me on this topic. Again, many thanks to you for your subtle observations. I’ll inform it to our editorial team to fix it. We always hope for such positive comments. Follow our website, ExcelDemy, a one stop Excel solution provider, to explore more.
Regards,
Shahriar Abrar
Excel & VBA Content Developer
Exceldemy