Excel PivotTables and the Illusion of Clean Data: How a Hidden Default Undermines Research Integrity

A hidden behaviour inside Excel PivotTables can silently distort research results, reshaping categorical outputs even after the underlying data has been cleaned. This paper reveals how a legacy setting—present in Excel since the 1990s—allows obsolete categories to persist in PivotTable views, creating “ghost categories” that alter frequencies, percentages, and visual summaries without any indication that anything is wrong. Because Excel remains one of the most widely used tools in scientific workflows, this silent behaviour has consequences far beyond spreadsheets. It shows how unexamined software defaults can become invisible methods, influencing interpretation while remaining completely absent from documented analytical procedures. By tracing the historical origins of this behaviour, replicating it empirically, and demonstrating its real impact on a systematic literature review, the study exposes a structural vulnerability that threatens reproducibility across disciplines. The paper argues that maintaining research integrity in a software-saturated world requires not only clean data but also critical awareness of the assumptions embedded in the tools that process it. It concludes with practical recommendations for researchers, institutions, and software developers to prevent such distortions and align spreadsheet practice with contemporary scientific standards. Abstract Excel is one of the most widely used analytical tools in research. Among its features, PivotTables are particularly influential: they generate the first descriptive overviews, distributions, and cross-tabulations that often anchor the interpretive logic of a study. This paper documents and analyses a long-standing, under-examined behaviour in PivotTables that directly threatens the integrity of such analyses. In classic Excel PivotTables, the setting “Retain items deleted from the data source” is enabled by default and set to Automatic. Consequently, PivotTables continue to display category labels that no longer exist in the underlying dataset. These “ghost categories” can alter frequencies, percentages, rankings, and even the perceived structure of a field, without any visible warning to the user. The issue surfaced during a systematic literature review on Modified Plant Structures (MPS), where a country label (“China”) appeared in a geographic PivotTable despite being absent from the cleaned dataset. Replication showed that Excel’s internal PivotTable cache retained obsolete categories even after deletions, recoding, and refreshes. Once the setting was switched to None and PivotTables were rebuilt, category sets, frequencies, and interpretations changed substantially, forcing a reconstruction of the Results, Discussion, and Conclusion sections. Based on a historical and technical review of Excel’s PivotTable implementation and a detailed case study, this paper demonstrates how this default has persisted since the 1990s, why it was originally introduced for business reporting, and how it silently undermines data fidelity and reproducibility in research settings. It distinguishes between existing technical notes (where Excel experts describe the behaviour) and the lack of any systematic academic treatment of its methodological consequences. The paper concludes with concrete recommendations for researchers, software vendors, and universities to mitigate the risk and align spreadsheet practice with contemporary standards of research integrity.