How To Sort Alphabetically In Excel And Keep Rows Together

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.

Dataset - How To Sort Alphabetically In Excel And Keep Rows Together

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.

Practice Workbook

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.

Sort option - How To Sort Alphabetically In Excel And Keep Rows Together

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.

A to Z sort

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).

Sort Warning

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.

Sorted improperly - How To Sort Alphabetically In Excel And Keep Rows Together

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.

Expand select in warning box - How To Sort Alphabetically In Excel And Keep Rows Together

You will find the countries sorted alphabetically. And the rows are along with that.

Sorter alphabetically and carry rows- How To Sort Alphabetically In Excel And Keep Rows Together

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?

Empty rows in data - How To Sort Alphabetically In Excel And Keep Rows Together

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.

Select column and click sort - How To Sort Alphabetically In Excel And Keep Rows Together

The Sort Warning dialog box will pop up and you know which option to select for the context of our task.

Expand the selection in warning - How To Sort Alphabetically In Excel And Keep Rows Together

Yes, select Expand the selection and click Sort. You will find the alphabetically sorted data.

Parted sort - How To Sort Alphabetically In Excel And Keep Rows Together

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.

Empty rows in data - How To Sort Alphabetically In Excel And Keep Rows Together

Now select the entire data. And click the A to Z option.

Select entire data - How To Sort Alphabetically In Excel And Keep Rows Together

Since we have selected all the values the Sort Warning will not pop up and you will find the sorted result.

Alphabetically sorted and carry rows - How To Sort Alphabetically In Excel And Keep Rows Together

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.

Advanced sort - How To Sort Alphabetically In Excel And Keep Rows Together

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.

Sort Warning

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.

Sort dialog box

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)

Select sort by column - How To Sort Alphabetically In Excel And Keep Rows Together

To make things consistent, we are going with the Country column again.

select sort by column 2 - How To Sort Alphabetically In Excel And Keep Rows Together

We have selected the column, and the order is A to Z. Now click OK.

Sorter alphabetically and carry rows

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.

Select advanced sort

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.

Limited 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.

Select entire data and advanced sort

Then click Sort and now you will see all the columns from your table.

Columns in sort box

In addition, the empty column is also in the list. Select the desired column and click OK.

Select column

The data is in alphabetic order and carries the rows together while changing positions.

Alphabetically sorted and carry rows

Alternatives

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.

To know about the functions, visit the Microsoft support site and read these SORT and SORTBY articles.

While using SORT, provide the reference of the entire table within the function.

SORT 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.

SORTBY function

Conclusion

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.


Further Readings

Shakil Ahmed

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo