Function Problems

How to Arrange Courses Under Separate Teacher Columns in Excel

Bushra Parveen Sep 30, 2026 868 views

Question details

The user wants to reorganize a dataset containing teachers, courses, and times so that each teacher has their own column with their respective courses listed underneath.

How to Arrange Courses Under Separate Teacher Columns in Excel

Product: Excel
Device & OS: not provided
Scenario: Transforming a flat class or schedule dataset into a customized layout organized by teacher.
Observed behavior: Standard PivotTables create one column for every course instead of listing the courses under dedicated teacher columns.

Before you start

Ensure you are using a version of Excel or WPS Spreadsheet that supports dynamic array functions, such as Microsoft 365, Office 2021, or the latest version of WPS Office.

Solution 1Recommended

Use TRANSPOSE, UNIQUE, and FILTER Functions

Extract unique teacher names across the top row and dynamically filter the courses below each name.

Name your source columns

Select the column containing your teachers, right-click, and define the name as 'Teacher'. Do the same for the courses column and name it 'Course'.

Extract unique teacher names as column headers

Click on the first cell of your intended output area (e.g., E1) and enter the formula `=TRANSPOSE(SORT(UNIQUE(Teacher)))`. Press Enter to spill the unique teacher names across the top row.

Filter courses for the first teacher

In the cell directly below the first teacher name (e.g., E2), enter the formula `=SORT(FILTER(Course,Teacher=E1))` and press Enter.

Copy the filter formula across

Select cell E2, grab the fill handle in the bottom-right corner, and drag it to the right to fill the formula underneath all the other teacher columns.

Use TRANSPOSE, UNIQUE, and FILTER Functions

Dynamic Updates: By using Named Ranges, your formulas are easier to read and will automatically update if you add or change data in the original source columns.

Solution 2

Use an Advanced Single Formula with REDUCE and HSTACK

Use a fully dynamic, single-cell solution that automatically spills the entire result matrix without needing to drag formulas.

Create Dynamic Course Schedules in WPS Office

Seamlessly Arrange and Filter Data with WPS Spreadsheet

WPS Spreadsheet offers comprehensive support for modern dynamic array functions like UNIQUE, SORT, and FILTER. You can effortlessly transform scheduling datasets without relying on complex macros or cumbersome manual sorting.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing your schedule.
2. Define your named ranges: Highlight your data columns and use the Name Box to define 'Teacher' and 'Course' ranges.
3. Apply dynamic arrays: Use `=TRANSPOSE(SORT(UNIQUE(Teacher)))` to create headers, and `=SORT(FILTER(Course,Teacher=Target_Cell))` to list the courses underneath.

Fully compatible with Microsoft Excel formulas and .xlsx filesSupports modern dynamic array functions out of the boxLightweight application with an intuitive, tabbed interfaceFree to use for everyday data organization and analysis

Frequently Asked Questions

Why do I get a #NAME? error when using these formulas?

A #NAME? error typically means your spreadsheet software version does not support dynamic array functions like FILTER or UNIQUE, or there is a typo in the function name. Upgrading to a newer version of Excel or WPS Office will resolve this.

Why doesn't a standard PivotTable work for this specific layout?

PivotTables are designed to aggregate numerical data or group items in rows. If you place text like course names into the Values area, it only counts them. If you place courses in the Columns area, it creates a new column for every distinct course instead of listing them as rows beneath the teacher.

How do I remove the #N/A or #CALC! errors if a teacher has fewer courses?

You can wrap your FILTER formula in the IFNA or IFERROR function to hide errors. For example, using `=IFNA(SORT(FILTER(Course,Teacher=E1)), "")` will display a blank cell instead of an error message when there are no more courses to list.

Can I include the class time next to the course name under each teacher?

Yes. If your course and time columns are adjacent in your source data, you can expand the range in your FILTER function to include both columns. Alternatively, you can use the CHOOSECOLS function within the FILTER array to retrieve specific columns.

Excel & Data Problems