Sort Multiple Columns With Formulas In Excel Easily

Sorting data in Excel is an essential skill for anyone who works with data. While sorting a single column is straightforward, sorting multiple columns can be a bit more challenging. However, with the use of formulas, you can easily sort multiple columns in Excel. In this article, we will explore how to sort multiple columns with formulas in Excel.

Why Sort Multiple Columns?

Sorting multiple columns is essential when working with large datasets. By sorting multiple columns, you can quickly identify patterns and trends in your data, making it easier to analyze and make informed decisions. For example, if you have a list of employees with their names, departments, and salaries, you may want to sort the list by department and then by salary to identify the highest-paid employees in each department.

Using the SORT Function

The SORT function is a powerful tool in Excel that allows you to sort data based on one or more columns. The syntax for the SORT function is:

SORT(array, column, [order], [by_column])

• array: The range of cells that you want to sort.
• column: The column number that you want to sort by.
• [order]: The sort order ( ascending or descending).
• [by_column]: The column number that you want to sort by (optional).

To sort multiple columns using the SORT function, you can use the following formula:

=SORT(A2:D10, 2, TRUE, 3)

This formula sorts the data in the range A2:D10 by the second column (column B) in ascending order, and then by the third column (column C) in ascending order.

Using the INDEX and MATCH Functions

Another way to sort multiple columns is by using the INDEX and MATCH functions. The INDEX function returns a value at a specified position in a range, while the MATCH function returns the relative position of a value within a range. The syntax for the INDEX and MATCH functions is:

INDEX(range, row_num, column_num) MATCH(lookup_value, lookup_array, [match_type])

To sort multiple columns using the INDEX and MATCH functions, you can use the following formula:

=INDEX(A2:D10, MATCH(E2, B:B, 0), MATCH(F2, C:C, 0))

This formula sorts the data in the range A2:D10 by the values in columns E and F.

Using the FILTER Function

The FILTER function is a new function in Excel that allows you to filter data based on one or more conditions. The syntax for the FILTER function is:

FILTER(array, include, [if_empty])

• array: The range of cells that you want to filter.
• include: The condition that you want to apply to the data.
• [if_empty]: The value that you want to return if the filter returns no data (optional).

To sort multiple columns using the FILTER function, you can use the following formula:

=FILTER(A2:D10, (B:B > 10) * (C:C < 20))

This formula filters the data in the range A2:D10 by the values in columns B and C.

Sorting Multiple Columns with Formulas

To sort multiple columns with formulas, you can use the following steps:

1. Select the range of cells that you want to sort.
2. Go to the "Data" tab in the ribbon.
3. Click on the "Sort" button.
4. Select the column that you want to sort by.
5. Click on the "Add Level" button.
6. Select the next column that you want to sort by.
7. Repeat steps 5-6 until you have selected all the columns that you want to sort by.
8. Click on the "OK" button.

Alternatively, you can use the formulas mentioned above to sort multiple columns.

Conclusion

Sorting multiple columns with formulas in Excel is a powerful way to analyze and manipulate data. By using the SORT, INDEX, MATCH, and FILTER functions, you can easily sort multiple columns and identify patterns and trends in your data. Whether you are working with a small dataset or a large dataset, sorting multiple columns with formulas can help you to make informed decisions and gain insights into your data.

Gallery of Sorting Multiple Columns with Formulas in Excel

Sorting Multiple Columns with Formulas in Excel Image Gallery

We hope this article has helped you to learn how to sort multiple columns with formulas in Excel. Whether you are a beginner or an advanced user, sorting multiple columns with formulas can help you to analyze and manipulate data more efficiently. If you have any questions or need further assistance, please don't hesitate to ask.