One of the common tasks you may have to do in Excel is sorting data. While performing sort you may face the situation where you need to organize data alphabetically as well as keep the rows together.
Our agenda for this article is to help you to perform sorting alphabetically and keep the rows together. Before diving into the big picture, let’s know about our example dataset.
Here we have a simple table of a few countries with their basic information (continent, capital, area, and population). You can see they are not organized alphabetically, we will sort the data alphabetically.
Note that, it’s a basic dataset to keep things simple. In the real-life scenario, the dataset can be a bigger one as well as more complex.
You are welcome to download the practice workbook from the link below.
Sort Alphabetically and Keep Rows Together
1. Quick Sort
The Quick Sort method provides the chance to sort the data with just one or two clicks. We can sort our data alphabetically using this type of sort.
I. Sort Without Empty Column
In this section, we are going to see how to sort when our dataset doesn’t have any empty column (s) in between.
First of all, select any of the column range you want to sort, here we are sorting on basis of the Country column.
Explore the Data tab and you will find several sorting options. There you will see an option called A to Z. Conventionally our alphabetical order is A to Z, so we will use this option.
Click the option (A to Z). Excel is a smart application when you select a single column from a couple of columns it will trigger a warning.
Since here we have selected only one column, a Sort Warning dialog box will pop up (follow the image below).
Here we will find two options: Expand the selection and Continue with the current selection. If we go with the second one our data will be sorted but only for the particular column that we have selected.
You can see the Country column has been sorted alphabetically. But values are mismatched for every row. This is not what we wanted.
Rollback a bit and select the Expand the selection option. And click Sort.
You will find the countries sorted alphabetically. And the rows are along with that.
So we have completed our desired task of sorting alphabetically and keep rows together. Mission accomplished! Haha!!
II. Sort With Empty Column
In the earlier section, we have seen how to sort when we don’t have any empty columns. But if we have an empty column (s) between the adjacent columns of a table, what then?
To demonstrate we have inserted a column in between the Capital and Area column. Now as previously, select the Country column and click A to Z sort option.
The Sort Warning dialog box will pop up and you know which option to select for the context of our task.
Yes, select Expand the selection and click Sort. You will find the alphabetically sorted data.
The countries are in alphabetical order and each country changes the row position along with their respective continent and capital. But! The area and population cause the mismatch. That didn’t change with the respective country.
Excel counts the columns together as long as no empty column is found. Here Excel found an empty column after the Capital column, so it assumes the table up to that column (Capital).
Let’s see how to get rid of this. At first roll back to the original position.
Now select the entire data. And click the A to Z option.
Since we have selected all the values the Sort Warning will not pop up and you will find the sorted result.
The countries are in alphabetic order and carry the respective rows.
2. Advanced Sort
You can also sort the data alphabetically using the Advanced Sort method. Don’t be afraid of hearing the name “Advanced”, you don’t need to be a professional to understand or use it, rather this tool will lead you towards the professional level and provide you different sorting options.
I. Sort Without Empty Column
You will find an option Sort in the Sort & Filter section under the Data tab. Select a column which to sort and click Sort.
Again, the selection of one column leads us to the Sort Warning dialog box (if you don’t want this select the entire data). Select the Expand the selection and click Sort.
You will find a new dialog box (Sort) in front of you. There will be Column, Sort On, and Order. Each option is drop-down in type.
Click the dropdown icon under the Column section and select the column you want to sort by. (Make sure to check My data has headers)
To make things consistent, we are going with the Country column again.
We have selected the column, and the order is A to Z. Now click OK.
The Country column has been sorted alphabetically and the rows kept together.
II. Sort With Empty Column
If you try to select a single column from a dataset that has an empty column within it and click Sort, afraid that’s not a good idea.
That will lead you to the warning and after selecting the Expand the selection option you will be at the Sort dialog box.
Now while selecting the column, this will show you the columns up to the empty column.
Here we have found only three columns.
The Advanced Sort is not that advanced to understand that the table has columns after the empty column. Jokes apart.
We have to select the entire table manually.
Then click Sort and now you will see all the columns from your table.
In addition, the empty column is also in the list. Select the desired column and click OK.
The data is in alphabetic order and carries the rows together while changing positions.
If you are using Excel 365, then you can perform the sort operation by using the SORT or SORTBY function. These functions will sort the data while keeping the rows together.
While using SORT, provide the reference of the entire table within the function.
You will find the alphabetic sort.
For SORTBY you need to provide one extra parameter along with the table range, the base column you want to sort with.
That’s all for today. We have tried to list several ways to sort alphabetically while keeping rows together. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. You can also notify us of any of the approaches we might have missed here.