Data Sorting and Filtering in Excel | How to become an Expert of MS Excel - Class 6 PDF Download

Introduction

Excel is a powerful tool that can handle large amounts of data effortlessly. One of the essential skills in Excel is sorting and filtering data. Whether you want to organize your data or extract specific information, mastering data sorting and filtering techniques can significantly enhance your productivity. In this article, we will explore various methods of sorting and filtering data in Excel, along with simple examples and code explanations.

Sorting Data in Excel

Sorting data allows you to arrange information in a particular order based on one or more columns. Excel provides several options for sorting data. Let's take a look at a few common methods:
1. Sorting a Single Column: To sort a single column in ascending or descending order:

• Select the column by clicking on its header.
• Go to the "Data" tab in the Excel ribbon and click on the "Sort A to Z" or "Sort Z to A" button, respectively.

Example:
Let's say we have a list of countries in column A and their corresponding populations in column B. To sort the data in descending order of population:

Country       Population

------------------------

USA           331,002,651

China         1,439,323,776

India         1,366,417,754

Brazil        212,559,417

After sorting, the data will be:

Country       Population

------------------------

China         1,439,323,776

India         1,366,417,754

USA           331,002,651

Brazil        212,559,417

2. Sorting Multiple Columns: Sorting by multiple columns allows you to prioritize sorting based on one column and then another.

• Select the entire range of data.
• Go to the "Data" tab, click on the "Sort" button, and define the sorting criteria by selecting the columns and the desired order.

Example:
Let's consider the previous example with an additional column for the area of each country. To sort the data by population (descending) and then by area (ascending):

Country       Population      Area (km²)

-----------------------------------------

USA           331,002,651     9,631,418

China         1,439,323,776   9,596,961

India         1,366,417,754   3,287,263

Brazil        212,559,417     8,515,767

After sorting, the data will be:

Country       Population      Area (km²)

-----------------------------------------

China         1,439,323,776   9,596,961

India         1,366,417,754   3,287,263

USA           331,002,651     9,631,418

Brazil        212,559,417     8,515,767

Filtering Data in Excel

Filtering data allows you to display specific information that meets certain criteria while temporarily hiding the rest. Excel provides a powerful filtering feature to perform this task. Let's explore the various options:

1. AutoFilter: The AutoFilter feature is a quick and simple way to filter data based on column values.

• Select the range of data.
• Go to the "Data" tab and click on the "Filter" button.
• Click on the drop-down arrow in the column header and select the desired criteria.

Example:
Consider a table of sales data with columns for "Product," "Region," and "Sales Amount." To filter the data and display only the sales in the "East" region:

Product     Region     Sales Amount

-----------------------------------

Product A   East       $1,000

Product B   West       $500

Product C   East       $2,000

Product D   South      $1,500

After filtering, the data will be:

Product     Region     Sales Amount

-----------------------------------

Product A   East       $1,000

Product C   East       $2,000

2. Advanced Filtering: Advanced filtering provides more flexibility in defining complex criteria using logical operators.

• Define a criteria range outside the data range, specifying the conditions.
• Go to the "Data" tab, click on the "Advanced" button, and define the range and criteria.

Example:
Using the previous sales data example, let's filter and display only the products with sales greater than $1,000 and in the "East" region:
Criteria Range:

Product     Region     Sales Amount

-----------------------------------

             East       >$1,000

After filtering, the data will be:

Product     Region     Sales Amount

-----------------------------------

Product C   East       $2,000

Sample Problems

Problem 1: Sort the following list of names in alphabetical order.

Amy

John

Sarah

David

The sorted list of names will be:

Amy

David

John

Sarah

Problem 2: Filter the given table to display only the products with sales amount greater than $500:

Product     Sales Amount

------------------------

A           $200

B           $750

C           $400

D           $900

The filtered table will be:

Product     Sales Amount

------------------------

B           $750

D           $900

Conclusion

Sorting and filtering data in Excel are powerful techniques that allow you to organize and analyze your data effectively. By utilizing these features, you can easily locate specific information, perform calculations on subsets of data, and present your findings in a meaningful way. With the examples and explanations provided in this article, you are well on your way to mastering data sorting and filtering in Excel. So, start applying these techniques to your own data and unlock the full potential of Excel!