Class 4: Data
Sorting and Filtering in Excel
Objective:
In this class,
students will learn how to sort and filter data effectively in Excel. This
skill is crucial for organizing and analyzing data in spreadsheets, especially
when dealing with large datasets.
Topics Covered:
1. Sorting Data
Sorting allows you to
arrange your data in a specific order based on the values in one or more
columns.
a. Ascending Sort
- Definition: Sorting data in ascending order means
arranging it from smallest to largest, or alphabetically from A to Z.
- How to Sort in Ascending Order:
- Select the column or range of data you
want to sort.
- Go to the "Data" tab on the
Excel ribbon.
- Click the "Sort Ascending"
button (A to Z icon).
b. Descending Sort
- Definition: Sorting data in descending order means
arranging it from largest to smallest, or alphabetically from Z to A.
- How to Sort in Descending Order:
- Select the column or range of data.
- Go to the "Data" tab.
- Click the "Sort Descending"
button (Z to A icon).
c. Custom Sort
- Definition: A custom sort allows you to arrange data
based on multiple criteria or specific rules.
- How to Perform Custom Sort:
- Select the data you want to sort.
- Click on the "Sort" option in
the "Data" tab.
- In the "Sort" dialog box,
choose the column you want to sort by and specify whether it should be
sorted in ascending or descending order.
- You can add additional levels of sorting
by clicking "Add Level" (e.g., first by name, then by date).
- Click "OK" to apply the sort.
2. Filtering Data
Filtering helps you
narrow down data in a worksheet, allowing you to display only the information
that meets certain criteria.
a. Using AutoFilter
- Definition: AutoFilter allows you to quickly filter
data based on predefined criteria or specific values.
- Steps to Apply AutoFilter:
- Select the range of data or the entire
table you want to filter.
- Go to the "Data" tab.
- Click on "Filter."
- A drop-down arrow will appear in each
header cell.
- Click the drop-down arrow and select the
criteria you want to filter by (e.g., specific values, dates, etc.).
b. Custom Filters
- Definition: Custom filters enable you to set more
complex criteria, such as filtering data that is greater than, less than,
or between specific values.
- How to Apply Custom Filters:
- After enabling AutoFilter, click the
drop-down arrow in the column header.
- Select "Custom Filter."
- In the dialog box, set your conditions
(e.g., "greater than 100" or "contains ‘sales’").
- Click "OK" to apply the filter.
3. Advanced
Filtering
Advanced filtering
offers more flexibility when filtering data by allowing you to set criteria in
a separate range of cells.
a. Using Criteria
for Advanced Filtering
- Definition: Advanced filtering lets you filter data
based on multiple conditions using a criteria range.
- Steps for Advanced Filtering:
- Create a criteria range on your worksheet
(a separate section where you specify conditions).
- Go to the "Data" tab, and click
"Advanced" under the "Sort & Filter" group.
- In the "Advanced Filter" dialog
box:
- Choose whether to filter in place or
copy the results to another location.
- Specify the "Criteria Range"
(where you've set the filter conditions).
- Click "OK" to apply the
advanced filter.
Exercise:
1. Sort a Dataset
by Different Columns
- Open a dataset (e.g., sales data, employee
records, etc.).
- Sort the data first by "Name" in
ascending order.
- Then, apply a custom sort to organize the
data by "Department" and within each department, sort by
"Hire Date."
2. Apply Filters to
Display Specific Data
- Use AutoFilter to show only employees from
the "Sales" department.
- Apply a custom filter to display only
records where the "Salary" is greater than $50,000.
Summary:
In this class, we have
learned how to sort and filter data in Excel. Sorting allows us to organize
data by columns in ascending, descending, or custom order. Filtering helps in
narrowing down the data to show only the required information, while advanced
filtering offers more flexible criteria for filtering complex datasets.
