5 Ways To Sort By 2 Columns In Excel

Sorting data in Excel is a fundamental skill that can help you organize and analyze your data more efficiently. While sorting by a single column is straightforward, sorting by two columns can be a bit more complex. In this article, we will explore five ways to sort by two columns in Excel, including using the built-in sort feature, formulas, and VBA macros.

Sorting data by two columns is useful when you need to prioritize data based on multiple criteria. For example, you might want to sort a list of employees by department and then by last name, or sort a list of products by category and then by price. Whatever your use case, we have you covered.

Method 1: Using the Built-in Sort Feature

Excel's built-in sort feature allows you to sort data by up to 64 columns. To sort by two columns, follow these steps:

1. Select the data range you want to sort, including the headers.
2. Go to the "Data" tab in the ribbon.
3. Click on the "Sort" button in the "Data Tools" group.
4. In the "Sort" dialog box, select the first column you want to sort by.
5. Click on the "Add Level" button.
6. Select the second column you want to sort by.
7. Choose the sort order for each column (e.g., ascending or descending).
8. Click "OK" to apply the sort.

Method 2: Using Formulas

If you want to sort data by two columns without using the built-in sort feature, you can use formulas. One way to do this is to create a helper column that combines the values from the two columns you want to sort by.

1. Create a new column next to your data range.
2. In the new column, enter a formula that combines the values from the two columns you want to sort by, such as =A2&B2 (assuming the data is in columns A and B).
3. Copy the formula down to the rest of the cells in the new column.
4. Select the entire data range, including the new column.
5. Go to the "Data" tab in the ribbon.
6. Click on the "Sort" button in the "Data Tools" group.
7. In the "Sort" dialog box, select the new column as the sort column.
8. Choose the sort order (e.g., ascending or descending).
9. Click "OK" to apply the sort.

Method 3: Using the SORT Function**

The SORT function is a new function in Excel that allows you to sort data without using the built-in sort feature. To use the SORT function to sort by two columns, follow these steps:

1. Enter the following formula in a new column: =SORT(A:B, 1, TRUE, 2, FALSE) (assuming the data is in columns A and B).
2. The formula will sort the data in columns A and B by the values in column A (first column) and then by the values in column B (second column).
3. Copy the formula down to the rest of the cells in the new column.

Method 4: Using Power Query

Power Query is a powerful data manipulation tool in Excel that allows you to sort data by multiple columns. To use Power Query to sort by two columns, follow these steps:

1. Select the data range you want to sort.
2. Go to the "Data" tab in the ribbon.
3. Click on the "From Table/Range" button in the "Get & Transform Data" group.
4. In the Power Query Editor, click on the "Sort" button in the "Home" tab.
5. In the "Sort" dialog box, select the first column you want to sort by.
6. Click on the "Add Level" button.
7. Select the second column you want to sort by.
8. Choose the sort order for each column (e.g., ascending or descending).
9. Click "OK" to apply the sort.

Method 5: Using VBA Macros

If you want to automate the sorting process or create a custom sorting solution, you can use VBA macros. To create a VBA macro to sort by two columns, follow these steps:

1. Open the Visual Basic Editor by pressing "Alt + F11" or by navigating to "Developer" > "Visual Basic" in the ribbon.
2. In the Visual Basic Editor, insert a new module by clicking "Insert" > "Module".
3. Enter the following code in the module:

Sub SortByTwoColumns()
    Range("A:B").Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("B1"), Order2:=xlAscending
End Sub

4. Save the module by clicking "File" > "Save".
5. Run the macro by clicking "Developer" > "Macros" in the ribbon and selecting the macro.

Gallery of Sorting by Two Columns in Excel

FAQs

By following these methods, you can easily sort data by two columns in Excel and improve your data analysis skills. Whether you're a beginner or an advanced user, sorting by multiple columns is a useful skill to have in your toolkit.

Gallery of 5 Ways To Sort By 2 Columns In Excel