Practical 4 – Applying Filters to Data

Course: Advanced Excel with Visualization | Course Code: CAD41VSP201
Practical Number: 4
Topic: Applying Filters to the Marksheet for Data Analysis.
Duration: 2 Hours

1. Objective

The primary objective of this practical is to enable students to effectively use Excel’s filtering capabilities to view and analyze subsets of data within a large dataset, specifically using the marksheet created previously. Students will learn to:

• Apply AutoFilter to a table.
• Filter data based on specific text, numbers, and dates.
• Utilize advanced number and text filters (e.g., ‘Greater Than’, ‘Contains’).
• Filter by multiple criteria.
• Clear and reapply filters.
• Understand the importance of filtering for data analysis and reporting.

2. Introduction

Filtering in Excel is a powerful tool that allows you to display only the rows that meet certain criteria, temporarily hiding the rows you don’t want to see. This feature is invaluable for quickly finding specific information, performing ad-hoc analysis, and presenting targeted data without altering the original dataset. Building upon the marksheet from Practical 3, we will now explore how to extract meaningful insights by filtering student data based on various conditions.

3. Pre-requisites

• Successful completion of Practical 3.
• Basic understanding of Microsoft Excel interface and table structure.
• Ability to open, save, and navigate Excel worksheets.

4. Software Required

• Microsoft Excel

5. Procedure: Practical Steps with Examples

Ensure you have your completed marksheet from Practical 3 open. We will use this table as our dataset for applying filters.

Step 5.1: Opening the Marksheet and Activating AutoFilter

1.     Open the Marksheet: Open the Excel workbook containing your Marksheet from Practical 3.

2.     Select Data Range: Click any single cell within your marksheet table (e.g., cell A3 which contains "Student Name"). Excel is smart enough to usually detect the entire data range if you select a cell within it. Alternatively, select the entire range of your data, including the headers (e.g., A3:J8 from the example in Practical 3).

3.     Activate AutoFilter:

o    Go to the Data tab on the Excel ribbon.

o    In the Sort & Filter group, click the Filter button (it looks like a funnel).

o    Observation: You will see small dropdown arrows appear next to each column header in your marksheet (e.g., Student Name, Subject 1, Total Marks, Percentage, Grade, Result). These arrows indicate that filtering is now active.

Step 5.2: Filtering by Specific Text

Let’s filter to see only students who received a specific grade.

1.     Click the Filter Arrow: Click the dropdown arrow next to the Grade column header (I3).

2.     Deselect All: In the filter dialog box that appears, you’ll see a list of all unique grades present in your data (A, B, C). First, uncheck the “ box at the top.

3.     Select Specific Grade: Check the box next to A.

4.     Apply Filter: Click OK.

o    Observation: Only the rows for students with a ‘Grade A’ are now visible. The row numbers on the left will appear in blue, indicating filtered rows. The filter icon on the Grade column header will change to a funnel, signifying an active filter.

Example Screenshot:

Student Name	Subject 1	…	Grade	Result
Charlie	92	…	A	Pass
Other rows hidden

Step 5.3: Filtering by Numbers

Now, let’s filter students based on their percentage.

1.     Clear Previous Filter: If the Grade filter is active, click the funnel icon on the Grade column header and select Clear Filter from "Grade". All rows will become visible again.

2.     Click the Filter Arrow for Percentage: Click the dropdown arrow next to the Percentage column header (H3).

3.     Use Number Filters: Hover over Number Filters. A sub-menu will appear with options like Equals, Does Not Equal, Greater Than, Less Than, Between, Top 10, Above Average, Below Average, and Custom Filter.

4.     Apply ‘Greater Than’ Filter: Select Greater Than….

5.     Enter Criteria: In the Custom AutoFilter dialog box, ensure the first dropdown is is greater than and type 70% (or 0.7 if entering as a decimal) in the box next to it.

6.     Apply Filter: Click OK.

o    Observation: Only students with a percentage greater than 70% are displayed.

Example Screenshot:

Student Name	…	Percentage	Grade	Result
Alice	…	78.0%	A	Pass
Charlie	…	90.8%	A	Pass
Other rows hidden

Step 5.4: Filtering by Multiple Criteria

You can combine filters from different columns to narrow down your data even further. Let’s find students who ‘Passed’ with a ‘Grade C’.

1.     Clear Previous Filter: Clear the filter from the Percentage column.

2.     Filter Result Column: Click the dropdown arrow next to Result (J3). Uncheck “ and check Pass. Click OK.

o    Observation: Only ‘Pass’ students are shown.

3.     Filter Grade Column (while Result filter is active): Click the dropdown arrow next to Grade (I3). Uncheck “ and check C. Click OK.

o    Observation: Now, only students who both ‘Passed’ AND received a ‘Grade C’ are displayed.

Example Screenshot:

Student Name	…	Percentage	Grade	Result
Eva	…	70.0%	C	Pass
Other rows hidden
(Note: Based on the example data, Eva is 70% which would be a ‘B’ not ‘C’. The formula from Practical 3 was IF(H4>=0.8,"A",IF(H4>=0.6,"B",IF(H4>0.45,"C",""))). Let’s assume for this example, there was a student who passed with a ‘C’ based on the 45% criteria. For Eva with 70%, the grade should be B.)

Correction/Clarification: Let’s adjust the example data or criteria to make this combination possible for a clearer illustration for students. If there isn’t a student who passes with a ‘C’ in the sample data, we’d tell the students that no results were found, which is also a valid outcome of filtering. For the sake of showing an example, let’s assume we have a student like Gopal with marks: 35, 40, 48, 42, 38. His total would be 203, percentage 40.6%, which is C and Pass.

Step 5.5: Filtering by Color (if Conditional Formatting is applied)

If you completed the optional task in Practical 3 to apply conditional formatting to highlight ‘Fail’ students in red, you can filter by this color.

1.     Clear All Filters: Click on any column with an active filter (funnel icon) and choose Clear Filter from "Column Name". Repeat for all filtered columns, or go to Data tab > Sort & Filter group > Clear button.

2.     Apply Conditional Formatting (if not already done): Ensure the Result column has conditional formatting applied (e.g., "Fail" in red fill).

3.     Click the Filter Arrow for Result: Click the dropdown arrow next to the Result column header (J3).

4.     Filter by Color: Hover over Filter by Color.

5.     Select the Red Color: From the sub-menu, select the red color that indicates ‘Fail’.

o    Observation: Only students who ‘Failed’ (and thus have their result cell colored red) will be displayed.

Step 5.6: Removing All Filters

To revert to showing all data, you can clear individual filters or remove the AutoFilter functionality entirely.

1.     Clear All Filters: Click the Clear button in the Sort & Filter group on the Data tab. This clears all active filters in the current sheet.

2.     Remove AutoFilter: Click the Filter button (funnel icon) in the Sort & Filter group on the Data tab again. This will remove all the dropdown arrows from the headers, deactivating the filtering functionality.

6. Exercises / Tasks

Apply the concepts learned to complete the following tasks using your marksheet:

1.     Find Specific Students: Filter the marksheet to display only students whose Student Name starts with the letter ‘A’.

o    (Hint: Use Text Filters > Begins With…)

2.     Identify Low Scorers: Filter the marksheet to show students who scored less than 60 in Subject 3.

3.     Analyze Grades and Results:

o    Filter to see all students who received a Grade B.

o    While this filter is active, further filter to see which of these Grade B students also got Pass.

4.     Identify Borderline Cases: Filter to display students whose Percentage is Between 40% and 50%.

5.     Reapply AutoFilter: If you removed the AutoFilter arrows, reactivate them.

6.     Experiment with ‘Top 10’ or ‘Above Average’: Use the Number Filters on Total Marks to display the top 2 students or students who scored Above Average in total marks.

7. Assessment / Deliverables

• Demonstrate to your instructor how to apply and clear various types of filters (text, number, multiple criteria).
• Submit screenshots of your marksheet after applying specific filters as requested by the instructor (e.g., a screenshot showing only ‘Fail’ students, or students with ‘Grade A’).
• Be prepared to explain the steps taken to achieve specific filtered views.