Effect of Range Naming Conventions on Reliability and Development Time for Simple Spreadsheet Formulas

Practitioners often argue that range names make spreadsheets easier to understand and use, akin to the role of good variable names in traditional programming languages, yet there is no supporting scientific evidence. The authors previously published experiments that disproved this theory in relation to debugging, and now turn their focus to development. This paper presents the results of two iterations of a new experiment, which measure the effect of range names on the correctness of, and the time it takes to develop, simple summation formulas. Our findings, supported by statistically significant results, show that formulas developed by non-experts using range names are more likely to contain errors and take longer to develop. Taking these findings with the findings from previous experiments, we conclude that range names do not improve the quality of spreadsheets developed by novice and intermediate users. This paper is important in that it finds that the choice of naming convention can have a significant impact on novice and intermediate users’ performance in formula development, with less structured naming conventions resulting in poorer performance by users.

💡 Research Summary

The paper investigates whether using named ranges in Excel improves the reliability and speed of formula development for novice and intermediate users. While many practitioners claim that named ranges act like good variable names in traditional programming—making spreadsheets easier to understand and less error‑prone—there has been little empirical evidence to support this belief. The authors previously showed that named ranges hinder debugging performance; the current study extends the inquiry to the creation of simple summation formulas.

Two experiments were conducted with a within‑subject design. Six naming conventions were defined, ranging from highly structured (no two names share a prefix) to completely unstructured (random names). For each convention, participants completed two identical tasks: one using named ranges and one using direct cell references. Each task required summing seven cells, using the “+” operator rather than the SUM function, to keep the cognitive load low. The spreadsheet comprised six worksheets, each representing one naming convention, resulting in a total of 12 tasks per participant.

Participants were drawn from Dundalk Institute of Technology: 15 postgraduate students (Group 1) and 17 second‑year software development undergraduates (Group 2). All were classified as novice or intermediate Excel users based on a pre‑experiment questionnaire. Experiments were run in a controlled computer lab (Windows 7, Excel 2007). Task completion times were recorded automatically with the T‑CAT macro, and correctness was judged by whether the resulting total matched the expected sum.

Statistical analysis (t‑tests and ANOVA) revealed two consistent patterns across both groups and all naming conventions. First, formulas built with named ranges produced more errors than those built with cell references. Error rates increased by roughly 7 %–15 % overall, with the unstructured naming condition (type f) showing the highest error increase (≈22 %). Second, the time required to construct a formula using named ranges was significantly longer, ranging from about 12 % to 25 % more than the cell‑reference condition. Even the most disciplined naming scheme (type a) incurred a 12 % time penalty, while the “same prefix, different trailing number” scheme (type d) produced an 18 % increase. All differences were statistically significant (p < 0.05).

These findings directly contradict the assumption that named ranges automatically confer the benefits of good variable naming. For non‑expert users, the additional cognitive load of recalling, selecting, and typing names outweighs any potential readability gains. Moreover, the structure of the naming convention—whether highly systematic or loosely defined—does not eliminate the performance penalty; poorly structured names exacerbate the problem.

The authors acknowledge several limitations. The tasks involved only simple addition; more complex functions (e.g., VLOOKUP, nested IFs) might interact differently with named ranges. The participant pool consisted solely of students, which may limit generalizability to professional environments. The laboratory setting removed real‑world pressures such as time constraints, multitasking, and collaboration.

Future research directions include: (1) testing named ranges with complex formulas and larger data sets; (2) conducting field studies in corporate settings with experienced spreadsheet users; (3) evaluating the impact of supportive tools such as name auto‑completion, name managers, or integrated documentation; and (4) exploring training interventions that could mitigate the observed drawbacks.

In conclusion, the study provides robust empirical evidence that, for novice and intermediate spreadsheet users, named ranges do not improve formula reliability and actually increase development time. Organizations should therefore be cautious in mandating the use of named ranges without providing adequate training or tooling, and should consider user skill level when formulating spreadsheet best‑practice guidelines.