Using Microsoft Excel for Lesson Transcript Analysis

Abstract

Transcript-Based Lesson Analysis (TBLA) involves transcribing entire lesson recordings into verbatim notes, followed by both quantitative and qualitative analyses to derive meaningful conclusions. This process demands substantial effort and time on the part of the researcher. For example, the researcher must listen to the lesson recording multiple times and transcribe every word spoken by both the teacher and students verbatim, ensuring complete accuracy without omissions or errors. Therefore, automating the TBLA process is essential not only for enhancing the reliability and accuracy of research findings but also for improving researcher productivity. This study introduces an automated digital tool for Transcript-Based Lesson Analysis (TBLA), developed using Microsoft Excel’s Visual Basic for Applications (VBA) and graphical user interface (GUI) functionalities. We applied the tool to analyze an 11th-grade mathematics lesson, demonstrating that the use of this automated digital tool not only facilitates the researcher’s workload but also enhances the validity and reliability of the results. In the future, the application of this digital tool can be expanded to enhance the outcomes of lesson analysis, facilitate lesson sensitivity analysis, and support the development of data-driven approaches in educational research