Function Problems

How to Filter Student Names by Grade in Excel

Maira Mehtab Sep 20, 2026 869 views

Question details

The user needs to filter and display student names alongside their fathers' names, either combined into one string or separated into columns, categorized by specific grades (A, B, and C).

Product: Excel
Device & OS: not provided
Scenario: Organizing class records to dynamically extract and group students based on their academic performance grades.
Observed behavior: Extracting and organizing data using complex criteria to present categorized student lists automatically.

Before you start

Ensure your dataset is organized with clear column headers (e.g., Student Name, Father's Name, Grade) and verify that your version of Excel supports dynamic array functions like FILTER.

Solution 1Recommended

Use the FILTER Function to Extract and Combine Names by Grade

The dynamic FILTER function is the most efficient way to extract specific records and combine text strings based on a condition, such as a student's grade.

By utilizing the FILTER function along with the ampersand (&) operator, you can simultaneously extract records that match a specific grade and concatenate the student's name with their father's name into a single neat column.

Identify your data ranges

Locate the columns containing your data. For example, assume Column A contains Student Names, Column B contains Fathers' Names, Column C has additional details, and Column D contains the Grades. Your data ranges from row 3 to 24.

Select the destination cell

Click on the empty cell where you want the filtered list for students with Grade 'A' to begin.

Enter the FILTER formula

Type the formula =FILTER($A$3:$A$24&" "&$B$3:$B$24&" / "&$C$3:$C$24, $D$3:$D$24="A") into the formula bar and press Enter.

Adjust for other grades

To extract students with a 'B' or 'C' grade, copy the formula to a new column and change the criteria argument from "A" to "B" or "C" accordingly.

Dynamic Array Spilling: Ensure there are enough empty cells below your formula. The FILTER function will automatically 'spill' the results down the column, and any existing text in the way will cause a #SPILL! error.

Filter Data Seamlessly

Manage and Filter Student Data Easily with WPS Office

WPS Spreadsheet fully supports advanced dynamic array functions like FILTER. You can quickly organize student records, combine text columns, and sort by grades with a familiar and highly compatible interface.

1. Open your dataset: Launch WPS Spreadsheet and open the file containing your student data.
2. Select a destination cell: Click on a blank cell where you want to output the filtered grade list.
3. Apply the FILTER function: Input your =FILTER() formula, combining the name columns and referencing the grade criteria column.
4. Press Enter to execute: Hit Enter to instantly populate the dynamically filtered student names based on their academic grade.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Supports advanced dynamic array functions including FILTER, SORT, and UNIQUE.Lightweight, fast, and runs smoothly on Windows, Mac, and Linux systems.Includes free built-in templates tailored for education and classroom management.

microsoft office alternative - wps office

Frequently Asked Questions

How can I output the names into separate columns rather than combining them into one string?

To output multiple columns instead of concatenating them, simply reference the entire column range in the first argument of the FILTER function. For example, use =FILTER($A$3:$B$24, $D$3:$D$24="A") to display both Student and Father names in separate adjacent columns.

What should I do if the FILTER formula returns a #CALC! error?

The #CALC! error typically occurs when no records match your specified criteria (e.g., no students achieved a grade 'A'). You can prevent this by utilizing the optional third argument in the formula to specify an alternative output: =FILTER(range, criteria, "No Records Found").

Can I automatically sort the extracted student names alphabetically?

Yes, you can easily nest the FILTER function inside a SORT function. By using a formula like =SORT(FILTER(range, criteria)), Excel will first extract the names that match the grade and then sort the resulting array in alphabetical order.

Excel & Data Problems