Shakil Ahmed
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.
Download Practice Workbook
4 Smart Ways to Sort Alphabetically and Keep Rows Together in Excel
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.
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 have 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 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).
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.
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.
- 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.
- 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.
Read More: How to Sort Columns in Excel without Mixing Data (3 Ways)
Similar Readings
- How to Auto Sort Multiple Columns in Excel (3 Ways)
- How to Sort Multiple Columns in Excel Independently of Each Other
- Excel Sort By Date And Time [4 Smart Ways]
- Excel Sort Dates in Chronological Order (6 Effective Ways)
- Difference Between Sort and Filter in Excel
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.
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.
Finally, we will find the countries sorted alphabetically and the rows along with that.
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 details, read the following section.
Steps:
- Select the entire dataset.
- Then, click the Sort option from the Data tab.
- 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.
Read More: How to Use Advanced Sorting Options 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)
- Press the ENTER button to have the sorted result.
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)
- To have the desired output, hit ENTER.
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
- How to Sort Rows by Column in Excel (4 Methods)
- How to Sort Rows in Excel (2 Simple Methods)
- Sort Column by Value in Excel (5 Methods)
- Auto Sort When Data is Entered in Excel (3 Methods)
- How to Remove Sort by Color in Excel (With Easy Steps)
- Excel Not Sorting Numbers Correctly (4 Reasons with Solutions)
