MS Excel Basic Data Sorting Operations

We are familiar with Excel basics. In this chapter, let us explore some powerful features of Excel to help us work with the data in a more useful way.

Data Sorting

Sorting I one of the most common requirements of data management. You must have seen lists of students sorted alphabetically or roll number-wise. Arranging the data in either ascending and Descending order is called data sorting. Excel can sort the numbers as well as alphabetical data such as city names, names of the students, etc.

The Benefit of sorting: Sorting helps in understanding the data. For example, sorting the marks of the students in descending (decreasing) order tells us the top performers. Or, sorting the population of cities in ascending (increasing ) order helps us identify the less crowded cities.

Another example is sorting the names of he students of the students in alphabetical order. We can find a name quickly and easily in a list of alphabetically sorted names.

Sorting also helps in grouping the data. For example, if we sort the details of students of class 6 and 7 then, we can easily group class 6 together and class 7 in a separate in a separate group.

Steps to sort the data

To sort the data, first, we need to select the data set.

Sorting the numbers data

To sort the numbers, Excel provides wo options—Sort Smallest to Largest and Sort Largest to Smallest.

Consider the data set of hotel rooms containing room number, room type, room fare and whether the room is occupied or not. Let us sort the data in the descending order of room number.

By default, the first column is considered as basis for sorting the data.

Sorting the text data

Let US see how we sort the text data. Let us consider the same data set of hotel rooms and sort the data is ascending order of room type.

1. Sort the room data set in ascending order of room fare.
2. Sort the room data set in the descending order of whether room is occupied or not.

Custom Sort

For complete control of sorting the data, we need the custom sort option. Using this option sorting can be done on multiply fields. Let us understand this by sorting the room data set on room type and within it on room fare.

Filtering the Data

Filtering refers to separating the desire data from the whole data set. We need to display selective data that meet some criteria. For example, you need to see the details of only those rooms which are not occupied. This can be done by filtering. Let us understand this by filtering out non-occupied rooms from the rooms data set.

Remember

The cell pointer should be on any cell of the data set otherwise Filter option will display an error.

Conditional Formatting

We know that formatting includes the appearance of the cell and its contents in various  styles of fonts, borders. Fill colour, etc. The term condition signifies based on the value in the cell. So, conditional formatting is the appearance of the cell depending on the value in it.

The conditional formatting feature example, allows us to specify the cell formatting which should be applied if cell contains a specific value. For example, if the cell has text “N” then it may be displayed in light red fill colour and red font colour. Let us see how to do this .

Subtotals

Excel allows us to calculate subtotals depending on the set of common values in a column. For Example, in our room data set example, we can calculate the subtotals of fare of Deluxe room, General rooms and suites. We must ensure that data is sorted before applying subtotals. For calculating subtotal, we need to specify following 3 parameters.

1. The field on whose basis subtotal has to be calculated (the field on which sorting is done).
2. The function to apply (for example, for total, we need to select SUM; for counting, it is COUNT, etc.)

• The field on which the subtotal has to be calculated.

Let us see how do we calculate total room fares for each room type. For this, our 3 parameters are:

1. ROOM TYPES
2. SUM function
3. FARE

Practical Application in Computer Lab

Open Excel and create the data set as shown here. Start entry from cell A3. In cell B1, type the heading;

TOP PERFORMERS.

Now, do the following;

1. Sort the data in descending order of years.
2. Within descending order of years, sort the data in ascending order of collection.
3. Filter out the details of the movies release in years 2020, 2018, 2019, 2020, 2021,2022 and 2022.
4. Filter out the movies whose collection is more than Sultan’s collection.
5. Apply conditional formatting on collection so that values more than 285 are shown in dark green fill and white text. Rest of the values must show in simply red font colour.
6. Calculate the average collection in each year. [Hint: In Subtotal dialog box, select function average]