Sorting is the best way to make the dataset more convenient and acceptable. This makes our work with datasets in various ways. MS Excel provides different ways to sort data for different purposes. In this article, I will show some ways to sort data by column while keeping the rows together.
Download the Practice Workbook
N Ways to Sort by Column Keep Rows Together
1. Sort by Column but Keep Rows Using Sort Function
Let’s assume we have a dataset of some salesman with their ID, Name, and number of Sales. Now our task will be to sort each column by keeping the rows together.
Step 1: Select the column that you want to sort
Step 2: Now click on any sort of option like Sort Smallest to Largest or Sort Largest to Smallest option. Here I have clicked on the first option
Step 3: There will be a pop-up like this. Keep the Expand the Selection option and then click
on the OK button
Step 3: Then all the data will be sorted
2. Sort by Column but Keep Rows Using Advanced Sort Function
Here we will do the same sorting operation for the previous dataset using the advanced sort of function. Let’s see how to do that.
Step 1: Select the column that you want to sort
Step 2: Now select the Sort option under the Data section
Step 3: There will be a pop-up like this. Keep the Expand the Selection option and then click on the Sort button
Step 4: Select the column that you want to sort and after selecting all the options as like the picture, then click on the OK button
Step 6: All the sorted data will be shown
3. How to Sort a Column Alphabetically and Keep Rows Together
Up to now, we have sorted the column which contained number values. Now in this section, we will sort columns which are containing strings and we will sort them alphabetically. Again, we will consider the same dataset.
Step 1: Select the Name column
Step 2: Then select any sort of option under the Data tab. I have selected the Sort A to Z (Ascending) option
Step 3: Again the warning window will pop up. Click on the sort button
Step 4: Then all the data will be sorted alphabetically
4. How to Put Multiple Columns in Alphabetical Order and Keep Rows Together
Let’s consider the above dataset with an additional column named Region. Now we will sort the whole dataset in alphabetical order using Name and Region two columns.
Step 1: Select the dataset
Step 2: Again, click on the Sort option
Step 3: Select Name and Region on the Sort by option and then click on the OK button
Step 3: Select Name and Region on the Sort by option and then click on the OK button
Step 4: All the data will be sorted
5. How to Sort Each Column Alphabetically and Keep Rows Together (Using Formula)
Here we will sort the above dataset using the formula in Excel. In the formula, we will SORT function. Let’s see the process:
Step 1: Enter the formula in cell G4 and press Enter
=SORT(B4:E13,4)
Formula Explanation
- In this SORT function, B4:E13 is our range of the dataset. And as we have four columns that’s why the second argument is 4.
- If you want to explore more about this function you can visit this link
Things to Remember
- Blank or hidden columns and rows
If there are blank or hidden rows and columns in the dataset, and if we select just one cell before clicking the sort of button, only the part of your data until the first blank row and/or column will be sorted.
The possible solution is to eliminate the blanks and unhide all hidden areas before sorting. Or select the entire table first, and then alphabetize. - Unrecognizable column headers
If the column headers are formatted differently from the rest of the data, Excel is smart enough to identify them and we need to exclude them from sorting. But if the header row has no special formatting, column headers will most likely be treated as regular entries and end up somewhere in the middle of the sorted data.
The solution is to select only the data rows, and then sort. - SORT function is only available in OFFICE 365.
Conclusion
These are some ways to sort by column with keeping rows together in Excel. I have shown all the methods with their respective examples but there can be many other iterations. I have also discussed the fundamentals of the used functions. If you have any other method of achieving this, then please feel free to share it with us.
Further Readings
- Sort Column by Value in Excel (5 Methods)
- How to Sort Rows by Column in Excel (4 Methods)
- Sort Two Columns in Excel to Match (Both Exact and Partial Match)
- How to Sort Columns in Excel without Mixing Data
- Sort Multiple Columns in Excel (5 Quick Approaches)
- How to Sort Multiple Columns in Excel Independently of Each Other
- Auto Sort When Data is Entered in Excel (3 Methods)