Last updated on April 15th, 2023 at 11:15 am
In this article on sorting data in Excel, you will learn skills that will make visualizing and analyzing data in Excel much easier. On the job and even in our private lives, we often sort data in order to better understand or analyze it.
This article will focus on how to sort data in Excel. This process may not apply to all brands of spreadsheet software. In sorting data in Excel, we will explore the following:
- Sorting in Excel; and
- Filtering in Excel.
If you are new to spreadsheets, be sure to visit the introduction to spreadsheets to learn terms related to spreadsheets. Additionally, before you learn about sorting data, be sure to visit the introduction to spreadsheet formulas and what is a spreadsheet function.
Sorting in Excel – Simple Sort
Sorting data is an organizational tool that makes it easier to understand, visualize and draw conclusions. This process makes it easy to understand and locate the information quickly. You can sort the information on your customers by their village then by their surnames. This makes it easier to understand the information. You can sort text data alphabetically from A to Z or Z to A, while values are sorted from largest to smallest and smallest to largest.
To sort data from A to Z, you need to first select the column you wish to sort then visit the sort option on the Home toolbar, as shown in the image below.
- When you press this image, here is what will happen:
- When you choose either of these options, you will see a prompt asking whether you wish to continue with the selection or expand the selection. By default, expand selection is usually identified. When this is used, the entire table of information will be sorted. This is important to retain data consistency. For instance, if you are sorting a person’s last name and you do not choose to expand the selection, only that column will be sorted. As a result, the person will now have a new last name that reflects the sort criteria. Since this is not ideal, we usually choose to expand the selection. However, if you choose to continue with the current selection, the program will only sort the information in that column.
- Finally, the information will be sorted according to the option chosen in 1 (A to Z or Z to A).
Sorting in Excel – Using multiple sort criteria
You may often need to use multiple sort conditions in your daily life. For instance, in a classroom, a teacher may wish to sort all students by their last names then by their first names. What this means is that the teacher may order all of the students’ names alphabetically by their last names. Additionally, when you add the second condition, then by first name, the program will find all the students whose last names are the same and order the first names alphabetically. So essentially, there will be a primary field (last name) and a secondary field (first name).
To perform this sort:
- Click the sort and filter option, as shown in Figure 1.
- Next, choose custom sort.
- Choose expand selection.
- In the pop-up menu in the sort by field, choose the column name where you would like to perform the primary sort. In the second field, choose cell values. Lastly, set the order of the sort: A to Z or Z to A or if you are sorting values, the options will be largest to smallest or smallest to largest.
- Next, go to the top of the pop-up menu and choose add level. Now you should see another set of fields that allow you to perform the secondary sort. Follow the same steps outlined in 4.
- Choose OK, and the information in the table will be sorted on both conditions.
If you wish to become an Expert at spreadsheets, it is imperative that you learn what absolute and relative references are and when to use them, be sure to visit our article on advanced Excel functions as well.
Filtering data in Excel
In Excel, you are also able to find all of the persons based on a particular criterion. Let’s say you have customers from all around the world, and you have stored their information in Excel, including their country. You can find all of the persons from Jamaica by using a filter. First, select the information you wish to filter.
To filter data in Excel, you need to go to the same sort and filter option shown in figure 1. In the dropdown menu, you will see an option marked filter. Click on this option. Your table should now look like figure 2 below.
Click on the arrow of the column with the information you wish to filter. Click select all to remove all the selections, then choose Jamaica. Now you will see the information for the persons from Jamaica only.
Before you go
In sharing information, we try to be as thorough as possible. However, if you have any questions or comments, be sure to leave them in the comment section below, and we will get back to you.