How to Sort Data in Excel: Easy Step by Step Process

Sorting data in Excel is a fundamental skill that can greatly enhance your data analysis capabilities. Whether you’re dealing with a simple list or a complex dataset, knowing how to sort data in Excel can help you organize and make sense of your information quickly and efficiently. In this article, you will learn the easy, step-by-step process of sorting data in Excel. From basic alphabetical or numerical sorting to more advanced multi-level sorts, you’ll learn how to manage your data like a pro. Know how to organize your data in Excel by using some sorting techniques.

Microsoft Excel offers two widely used features: sorting and filtering. They are frequently used in data analysis to set up, organize, and subset your data according to particular criteria. You will discover how to sort data in Excel in this article. You will also get knowledge of data filtering.

Sorting is the process of arranging the strings or integers so that they can be placed in ascending or descending order. A text column can be sorted alphabetically (A-Z or Z-A). A number column can be sorted from largest to smallest or smallest to largest. A date and time column can alternatively be sorted from oldest to newest or vice-versa. Excel also has the option of sorting using a custom list or formats like cell colour, font colour, or icon set. In this article, we will look into how we can do Sorting in Excel. 

Single Column Sorting

• You can sort data based on the values in a single column.
• For example, sorting a list of names alphabetically or sorting numbers from smallest to largest.

Multiple Column Sorting

• You can sort data based on multiple columns to prioritize sorting order.
• For example, first sorting by department and then by employee names within each department.

Shortcut Key to Sort a Single Column in Excel – Ctrl + Shift + L

Sorting a single column in Excel is a basic operation that allows you to organize data in ascending or descending order based on the values in that column. Consider the Employee dataset depicted below. It has information about the employees, the Job Title, Department, Gender, and so on.

Let’s sort the data based on the Annual Salary of each Employee in descending order.

• You can choose the data and the shortcut key Ctrl + Shift + L to sort just one column.
• Select the List Annual Salary column’s downward pointing arrow. From largest to smallest, choose.

Most often, only one column needs to be sorted. However, there can be times when you need to sort across many columns. Data can be sorted by several columns using advanced sorting methods. Here’s a step-by-step guide on how to do it:

Step 1: Select your data

Click anywhere within the data range you want to sort. Ideally, select the entire table, including any header row.

Step 2: Go to the Data tab

Navigate to the “Data” tab on the Excel ribbon and click on it.

Step 3: Click Sort in the ‘Data tab’

In the “Data Tab” group, find the “Sort” button and click on it.

Step 4: Choose the first sort column

A “Sort” dialog box will appear. In the “Sort by” dropdown menu, select the first column you want to sort by. This is your primary sorting criteria.

Step 5: Add additional sorting levels (optional)

If you want to sort by more than one column, click the “Add Level” button. Then, choose the next column you want to sort by from the new “Then by” dropdown menu and set its order (ascending or descending). You can repeat this step for a third level of sorting.

Tip

Sorting happens sequentially. The data will be sorted by the first column first, then within those sorted groups, by the second column, and so on.

Step 6: Click ‘OK’

Once you’ve defined your sorting criteria, click the “OK” button to execute the sort.

You can create your custom order in Excel by using custom sorting. Data that cannot be sorted alphabetically or ascending may occasionally need to be sorted. To sort data, Excel enables you to make your unique lists. Suppose you want to sort the dataset based on Department in the following order – IT, Sales, Marketing. Follow the steps given below for your reference:

• Select the dataset > Click on the Sort option in the Data tab
• Choose the Department column to sort
• Under Sort On, select Cell Values
• Choose the Custom List, under Order
• In the Custom Lists dialog box, add the List entries separated by commas – IT, Sales, Marketing
• Click on Add > Select OK.

The result of the same is displayed below.

Data sorting using a single column was demonstrated. You were aware of how sorting functions when numerous columns are involved. Additionally, you learned about MS Excel’s capability for custom sorting.

• You can sort up to three levels in the basic sort and more using the custom sort.
• Sorting happens sequentially, so prioritize your most crucial sorting criteria first.
• For clarity, consider using a helper column with formulas to define your sorting logic before using the custom sort option.

A filter in Excel is a tool that allows you to display only the rows that meet certain criteria, hiding the rest. This is particularly useful for analyzing large datasets, as it helps you focus on specific subsets of data without deleting any information. Here are the key points about using filters in Excel:

AutoFilter

The most commonly used filter, which allows you to filter data based on values, specific criteria, or conditions directly from the column headers.

Advanced Filter

Used for more complex filtering scenarios, allowing you to apply criteria ranges and extract filtered data to another location.

Step 1: Select the Data Range

Step 2: Go to the Data Tab and Click on Filter

Click on the Filter button. Small dropdown arrows will appear in the header row of each column.

Step 3: Set Filter Criteria

• Click the dropdown arrow in the header of the column you want to filter.
• Select the criteria you want to filter by (e.g., specific values, date ranges, number ranges, etc.).
• You can use checkboxes to select specific values or use the search box to find values quickly.

Step 1: Set Up Criteria Range

• In a different part of the worksheet, set up a criteria range with the same headers as your data range.
• Enter the criteria you want to use for filtering.

Step 2: Go to the Data Tab

Step 3: Go to the Sort and Filter Option and Open Advanced Filter Dialog

Step 4: Select the Range of Data to Filter

Step 5: Specify the Criteria Range

Step 6: Choose Filter Option and Click OK

• Decide whether to filter the list in place or copy the results to another location.
• Click OK to apply the filter

Sorting data in Excel is a basic yet powerful tool that can greatly improve how you handle and analyze information. This guide shows you the easy steps to sort your data, whether you’re working with names, numbers, or more complex data sets. By mastering these sorting techniques, you can quickly organize your information and make it easier to understand. Excel’s sorting and filtering features help you arrange and focus on specific parts of your data, making your work more efficient. Explore this article to learn how to use these features and take your data organization skills to the next level.

What are the types of sorting in Excel?

There are 3 ways in which you can sort data in Excel.

• Sort A to Z (Sorts in Ascending Order)
• Sort Z to A (Sorts in Descending Order)
• Custom Sort (Sorts by applying different sort criteria)

What are 3 ways you can sort data in Excel?

Here are 3 ways you can sort data in Excel:

• Simple Sort (Single Click)
• Sort by Multiple Columns
• Custom Sort (Advanced Sorting)

How do I sort columns in Excel without mixing data?

There are two main ways to sort a single column in Excel without affecting or mixing the data:

• Sort with the “Expand the selection” option
• Sort with Helper Column (Optional)