Utilization of VBA and Excel as a Technological Innovation for Item Analysis

Abstract

Item analysis plays a crucial role in educational evaluation to ensure the validity and reliability of test instruments. Manual calculations of difficulty index (p), discrimination index (D), and reliability coefficients such as KR‑20 are often time‑consuming and prone to human error. This study introduces an innovative approach by utilizing Microsoft Excel combined with Visual Basic for Applications (VBA) to automate item analysis. The developed macro system allows educators and researchers to input answer keys and student responses, then automatically calculates difficulty index, discrimination index, validity categories, and KR‑20 reliability. In addition, the system generates visual outputs in the form of bar charts, pie charts, and reliability graphs, making interpretation more accessible. A simulated dataset of 20 students with 20 multiple‑choice items was used to demonstrate the system’s functionality. The results show that 80% of items fall into the moderate category, 75% are valid, and the KR‑20 reliability coefficient reaches 0.811, which is categorized as high. These findings confirm that the test instrument has good quality and demonstrate that VBA/Excel‑based automation provides accurate, efficient, and reproducible item analysis. This innovation highlights the potential of accessible spreadsheet technology to democratize item analysis, making it practical for educators across diverse contexts.