Sorting Columns in Excel While Keeping Rows Together

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

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)