Sorting Columns in Excel While Keeping Rows Together

Sorted Data

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.

Sort by Column But Keep Rows Using Sort Function

Step 1: Select the column that you want to sort

Select the data

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

 Select Sort 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

Sorted data

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.

Sort by Column But Keep Rows Using Sort Function

Step 1: Select the column that you want to sort

Select the column

Step 2: Now select the Sort option under the Data section

 Click on the sort option

Step 3: There will be a pop-up like this. Keep the Expand the Selection option and then click on the Sort button

Select the expand selection option

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

Select the other levels

Step 6: All the sorted data will be shown

Sorted Data

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

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

Select sort option

Step 3: Again the warning window will pop up. Click on the sort button

 Sort warning

Step 4: Then all the data will be sorted alphabetically

Sorted output

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.

How to Put Multiple Columns in Alphabetical Order and Keep Rows Together

Step 1: Select the dataset

Select the dataset

Step 2: Again, click on the Sort option

 Select sort option

Step 3: Select Name and Region on the Sort by option and then click on the OK button

 Select Name and Region on the Sort by option

Step 3: Select Name and Region on the Sort by option and then click on the OK button

Select Name and Region on the Sort by option

Step 4: All the data will be sorted

 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:

How to Sort Each Column Alphabetically and Keep Rows Together (Using Formula)

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

 Enter formula using Sort function

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.

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo