Using macros in microsoft excel to facilitate cleaning of research data

Area of Science:

• Health Informatics
• Clinical Research Methodology
• Data Management

Background:

• Retrospective chart reviews often face delays due to difficulties in exporting clean clinical data from electronic medical records (EMR).
• Lack of dedicated informatics support in smaller institutions can hinder efficient data processing.

Purpose of the Study:

• To demonstrate the utility of Microsoft Excel macros for streamlining clinical data cleaning.
• To provide a cost-effective solution for researchers in community hospitals and smaller academic centers.

Main Methods:

• Exporting vital signs and lab results from 20 hospitalizations via EMR.
• Developing and applying two Excel macros for data sorting and formatting.
• Comparing the time efficiency of macro-assisted cleaning versus manual transcription.

Main Results:

• Macro-assisted cleaning reduced vital signs data cleaning time by 94% (46.5s vs 12.3 min/record).
• Macro-assisted cleaning reduced lab data cleaning time by 91% (13.7s vs 2.6 min/record).
• Statistical significance was achieved for both vital signs and lab data cleaning time reductions (P < 0.001).

Conclusions:

• Excel macros provide a flexible and efficient method for cleaning large clinical datasets.
• This approach is particularly beneficial for institutions with limited informatics resources or EMR export capabilities.
• Macros enable faster preparation of clinical data for analysis, supporting retrospective research.