How to Sort Alphabetically and Keep Rows Together in Excel (4 Smart Ways)

Get FREE Advanced Excel Exercises with Solutions!

Everyone appreciates decorated things. Even in terms of working with Microsoft Excel, we love to decorate our data. We can sort data alphabetically and keep the rows together in Excel. In this article, I am going to explain 4 smart ways to sort data alphabetically and keep the rows together in Excel. I hope this article will be helpful for you if you are looking for a way to do similar sorts of stuff.


How to Sort Alphabetically and Keep Rows Together in Excel (4 Smart Ways)

In this article, I am going to explain only 4 smart ways to sort data alphabetically and keep the rows together in Excel. For more simplification, I am going to use a dataset on some country’s information in the Country, Continent, Capital, Area (km2), and Population (m) columns.

Sort Alphabetically Keep Rows Together


1. Employ Sort Feature

There is a built-in feature in Excel named Sort to sort data alphabetically. It also keeps the related rows together. But there might be two different cases. The dataset might contain an empty column. Nothing to worry about. They are described in the following section.


1.1 Sort Alphabetically Without Empty Column

In order to sort alphabetically and keep rows together when there is no empty column, you just need to follow the following steps.

Steps:

  • First of all, select any of the column cells that you want to sort. Here, we are sorting on the basis of the Country column.
  • Next, go to 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.
  • Afterward, click the option (A to Z).

Sort Alphabetically Without Empty Column

A Sort Warning dialog box will pop up. 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.

  • Now, select the Expand the selection option and click Sort.

We will find the countries sorted alphabetically and the rows along with that.

Sort Alphabetically Keep Rows Together

Read More: How to Sort Alphabetically in Excel with Multiple Columns


1.2 Sort Alphabetically with Empty Column

In the earlier section, we have seen how to sort data 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? Now, we will discuss it in this section.

Steps:

  • To demonstrate, we have inserted a column in between the Continent and Capital columns.

Sort Alphabetically with Empty Column

  • As per the previously mentioned process, 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. So, select Expand the selection and click Sort.

Sort Alphabetically Keep Rows Together

  • The countries are in alphabetical order and each country changes the row position along with their respective continent. But! The capital, 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 Continent column. So, it assumes the table up to that column.

  • Select the entire dataset first.
  • Now, click the A to Z option from the Data tab.

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

Sort Alphabetically Keep Rows Together

Read More: How to Sort Columns in Excel Without Mixing Data


2. Sort with a Specific Criterion

We can also sort alphabetically along with the related rows based on a certain column. We are going to discuss the whole process in the following section.


2.1 Sort Alphabetically Without Empty Column

In this section, we will try to explain sorting with a specified criterion where there is no empty column.

Steps:

  • Select any of the column cells (i.e. Country) that you want to sort.
  • Then, click the Sort option from the Data tab.

Sort Alphabetically Without Empty Column

Instantly, a Sort Warning dialog box will appear.

  • Select Expand the selection and click Sort.

  • Click the dropdown icon under the Column section and select the column you want to sort by.
  • Also, mention the A to Z option from Order.
  • Then, click on OK.

Sort Alphabetically Keep Rows Together

Finally, we will find the countries sorted alphabetically and the rows along with that.

Sort Alphabetically Keep Rows Together


2.2 Sort Alphabetically with Empty Column

Sorting with a specified criterion having no empty column and having empty column(s) does not follow the same procedures. There needs just a little modification. To know in detail, read the following section.

Steps:

  • Select the entire dataset.
  • Then, click the Sort option from the Data tab.

Sort Alphabetically with Empty Column

  • Next, click the dropdown icon under the Column section and select the column you want to sort by (i.e. Column B).
  • Also, mention the A to Z option from Order.
  • Then, click on OK.

Thus, we will have our desired output.

Sort Alphabetically Keep Rows Together

Read More: How to Sort by Row not Column in Excel


3. Use SORT Function

If you are using Excel 365, then you can perform the sort operation by using the SORT function. This function will sort the data while keeping the rows together.

Steps:

  • Pick a cell (i.e. H5) where you want to have the entire sorted table.
  • Next, input the following formula in that cell.
=SORT(B5:F18)

Use SORT Function

  • Press the ENTER button to have the sorted result.

Sort Alphabetically Keep Rows Together

Read More: How to Sort Rows by Column in Excel


4. Apply SORTBY Function

There is another function named SORTBY in Excel 365 that can be used to sort data. This function will sort the data while keeping the rows together too.

Steps:

  • As a first step, choose a cell (i.e. H5) where you want to have the entire sorted table.
  • After that, input the following formula in that cell.
=SORTBY(B5:F18,B5:B18)

Apply SORTBY Function

  • To have the desired output, hit ENTER.

Sort Alphabetically Keep Rows Together


Download Practice Workbook


Conclusion

At the end of this article, I like to add that I have tried to explain 4 smart ways to sort data alphabetically and keep the rows together in Excel. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below. You can visit our site for more articles about using Excel.


Related Articles


<< Go Back to Sort in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo