Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

# How to Sort in Excel And Keep Rows Together: 4 Easy Methods

by | 5:33 pm EDT | May 17, 2023 | Excel

Sorting data in Excel is a crucial skill when managing your information. One challenge you may encounter is keeping rows together while sorting, ensuring that related data stays linked when organizing your spreadsheet by a particular column.

The most common method to sort data with two or more columns in Excel while keeping rows together is to choose “expand the selection” in the sort dialog box. There are other methods, including the use of the sort function and custom lists.

You will learn all these methods in this article by walking through multiple scenarios.

## How To Sort While Keeping Rows Together

Before we dive into the techniques, let’s take a look at the problem you want to avoid.

Suppose you have inventory data of clothing apparel where the type of item is in column A and the color is in column B. This is the data:

• T-Shirt, Red

• Jeans, Blue

• Hoodie, Green

• T-Shirt, Purple

• Hoodie, Black

You want to sort the items in alphabetical order while keeping the association between the item and its color. This is what the sorted rows should look like (I’ve color-coded the fonts to make it clearer):

If you only sort the first column, the colors won’t move with their associated item. This will lead to jumbled data which loses all meaning.

Here are the results of an incorrect sorting method:

Now that you’re clear on what you want to achieve, let’s look at how to access Excel’s sort options.

## 3 Ways to Access The Sort Commands

The most common way to sort in Excel is to use the Sort command. There are three ways to access this feature:

• Home tab

• Data tab

• Right-click

### The Home Tab

1. Go to the Home tab.

2. Expand the “Sort & Filter” drop-down menu in the Editing group.

3. Choose the ascending or descending order option.

Excel assumes that you have a header row with this option.

### The Data Tab

1. Go to the Data tab.

2. Click on the Sort button in the Sort & Filter group.

3. Choose your sort options in the sort dialog box.

### Right-Click

1. Select the data you want to sort.

2. Right-click.

3. Choose “Sort” from the drop-down menu (near the bottom).

## How to Sort By Selecting Multiple Columns to Keep Rows Together

This method works when you want to sort by the first column in the data set. If the target column is not column A, then use one of the other methods in this article.

To use this method, you will select all the data involved before using the Sort command. This means that you are not simply selecting one column.

In our sample data above, you would either select the entire worksheet, the specific data range of cells from A1 to B6, or the two columns A and B.

1. Select all the cells that are related (or Ctrl-A for everything).

2. Go to the Home tab.

3. Expand the Sort & Filter drop-down.

4. Choose “Sort A to Z”.

Note that you won’t see extra prompts about how you want to sort the data. Microsoft Excel assumes that you want to keep associated rows together because you selected all the data.

If you have a header row, it will also be recognized.

## How to Sort With Excel Prompt to Keep Rows Together

In the previous section, you ensured that your sort request was unambiguous by selecting all the data you wanted to include.

However, you may wish to sort by a column that is not the first in the worksheet. For example, suppose you want to sort the sample data by the color of the garments.

1. Select the column you want to sort by clicking on its header letter.

2. Go to the Home tab.

3. Expand the Sort & Filter drop-down.

4. Choose “Sort A to Z” (or your preference).

5. In the “Sort Warning” window that appears, choose “Expand the selection”.

Note that Excel detected that there were columns adjacent to the one that you selected. In this scenario, Excel asks you whether you want to include multiple columns are not.

By expanding the selection, Excel sorts the chosen column while keeping the associated data in the same row intact.

## How to Use The Sort Function to Keep Rows Together

The SORT function in Excel automatically keeps rows together when sorting.

This function is particularly useful because you can use it in a formula and it automatically updates when your data changes.

The SORT function is a dynamic array function. If you enter it into a single cell, Excel will automatically fill in the other cells needed to display the full range of sorted data, provided there are enough blank cells available.

After entering this formula in a cell, Excel will display the sorted version of your data starting from that cell. Note that this won’t change the original data; it will just display a sorted version of it.

The syntax of the SORT function is this:

=SORT(array, [sort_index], [sort_order], [column_or_row]

• array: the range of cells to sort by.

• sort_index: the column to sort by.

• sort_order: TRUE for ascending (the default), and FALSE for descending.

• column_or_row: FALSE for columns (default), and TRUE for rows

Let’s say you have data in cells A1 through B6 with a header. You want to sort this data by the values in column A in ascending order. Use this formula:

=SORT(A2:B6)

Be careful not to include the cell with the function in the array. Otherwise, you will have a circular reference.

### Sorting And Filtering Together

The FILTER function in Excel is used to filter a range of data based on certain criteria, rather than to sort data. However, you can combine the FILTER function with the SORT function to both filter and sort data while keeping rows together.

The FILTER function is also a dynamic array function (like SORT). As I mentioned in the previous section, Excel will fill in the other cells needed to display the full range of filtered data.

Suppose that some of the clothing items in the sample data have the same color. You want to arrange the data to show red garments sorted alphabetically. This requires two steps:

1. Filter the rows on column B (color).

2. Sort the filtered rows by column A (name of item).

This is the syntax of the FILTER function:

=FILTER(array, criteria, [if_empty])

• array: the range of cells to sort by.

• criteria: what to filter by.

• if_empty: what to return if there is no match.

The key is to wrap the SORT function around the filtered results. For our example, you’d use this formula:

=SORT(FILTER(A2:B6,B2:B6 = “Green”), 1)

After entering this formula in a cell, Excel will display the filtered and sorted version of your data starting from that cell. Note that this won’t change the original data; it will just display a filtered and sorted version of it.

## How to Use Custom Lists to Sort And Keep Rows Together

Custom lists in Microsoft Excel allow you to define your own sorting rules based on specific items. This is useful when you encounter a dataset where the default alphabetical or numerical sorting is not suitable.

Suppose you want to sort items so that purple is always before red which is always before blue etc. You can set up a list of colors in the precise order that you want.

There are two stages to ordering by a custom list:

1. Create the list.

2. Use the list as an order sequence.

### How to Create The Custom List

1. Go to the File tab.

2. Choose Options.

4. Scroll down to the General section.

5. Click Edit Custom List to open the Custom Lists Dialog Box.

This picture shows a list of colors typed into the interface.

### How to Use The List

Now that you’ve created your list, you can apply it to a set of data.

1. Select the column you want to sort by.

2. Go to the Data tab.

3. Click on the Sort button in the Sort & Filter group.

4. Choose “expand the selection” to view the sort dialog box.

5. Expand the drop-down list under the “Order” title.

6. Choose “Custom List”.

8. Click OK twice.

This infographic shows the sequence:

## 3 Tips For Handling Data Types

When dealing with various data types in Excel, such as text, day, month, and last name, it’s essential to be aware of their distinctions. To achieve accurate sorting results, follow these tips:

1. For text sorting (alphabetical order), select a cell in the column you want to sort, go to the Data tab, and click either Sort A to Z (ascending) or Sort Z to A (descending).

2. When sorting by day or month, ensure your dates are in a consistent format (e.g., MM/DD/YYYY).

3. If sorting by age or numbers, make sure all data in the column is stored as the same type (text or numbers).

## 4 Tips For Efficient Sorting

Here are our best tips for efficient sorting:

1. If there are hidden columns or hidden rows in your data, unhide them before sorting.

2. It’s helpful to use the Freeze Panes feature when sorting lengthy data.

3. You can ensure that the sorting order isn’t changed by locking the cells.

4. Remember that blank rows will be pushed to the end when sorting i.e. eliminated.

Sorting data to eliminate blank rows is useful preparation when you’re looking for distinct values in your data. This video shows the task in action:

## Final Thoughts

Sorting is one of the most common tasks when working with an Excel spreadsheet.

You have learned how to sort alphabetically or numerically in ascending or descending order while keeping rows intact.

This ensures that the meaning of your data is kept intact and further data analysis is accurate.

## Can Excel Open JSON? A Quick How-To Guide

Yes, Excel can open JSON files, with the latest versions of Excel being even more capable. By using...

## Excel Solver: Step-by-Step User Guide With Examples

Have you ever felt stuck in a spreadsheet maze with too many numbers? Well, that's where Excel Solver...

## Is Excel Still Worth Learning in 2023? 5 Reasons Why

With new and emerging software and tech coming out all the time, you might be wondering if it is still...

## Are Google Sheets and Excel the same? Differences Compared

Welcome to the landscape where business meets data. In every corner of the business world, two tools...

## What is Regression Analysis in Excel? User Guide + Examples

Regression analysis in Excel is like a super-smart calculator that helps us understand and predict...

## Why is Excel so Bad? Well, Not All The Time…

Have you ever wondered why Excel, the trusted spreadsheet hero, sometimes leaves us scratching our...

## Is Excel a Database? No. Spreadsheets Vs Databases Explained

Microsoft Excel is known for its powerful capabilities as a spreadsheet software. However, a common...

## How Does Xlookup Work in Excel? Step-by-Step Guide

Are you ready to discover the ultimate, game-changing secret weapon that will revolutionize your...

## What is the Quick Access Toolbar in Excel? Guide to Customize

Are you ready to tap into the power of Excel's Quick Access Toolbar, the efficiency enhancer right at...

## Does Excel Have a Dark Mode? Yes, Let’s Enable it

If you find yourself struggling with a bright computer screen (like us), you might be asking, "Does...

## Where Does Excel Store Temp Files? Location & Recovery

Understanding Excel's filing systems initially seems daunting. A common inquiry that emerges is, "How...

## What Does Clicking The Plus Sign in Excel Do? Let Us Explain

Welcome to the world of Microsoft Excel, the Swiss Army knife of spreadsheet tools used for an endless...