Principles of Data Design in Spreadsheets

Alexander Prutzkow

Abstract


Spreadsheets remain a relevant data processing tool for end users, despite the proliferation of databases and information systems. There are principles for writing easily-modifiable programs. However, there are no such principles for spreadsheets. We have formulated three principles for data design in spreadsheets. The data elementarity principle states that any component of a spreadsheet (cell, row or column, table, sheet) must contain indivisible (for this component and problem) data. This principle determines the arrangement of data in cells, tables, sheets, and spreadsheets. The data consistency principle states that data must not have contradictory values. This principle defines the relationship of data among themselves, the relationship of source and derived data. The principle, together with the previous principle, is related to the organization of data. The data certainty principle states that any component of a spreadsheet must have purpose. This principle determines the presentation of data in a workbook. The data must have names and a single designation. Each principle has rules that govern the details of data design in spreadsheets. Compliance with these principles and rules will make the spreadsheets readable and easily-modifiable. The formulated principles are used by us in spreadsheets for organizing the educational process and maintaining electronic journals, as well as in teaching how to work in spreadsheets.


Full Text:

PDF

References


A. Prutzkow, Napravlenija sovershenstvovanija dejatelnosti Gorodskoj shkoly programmistov RGRTU [Directions for Improving the Activities of the School of Programmers in RSREU]. In Aktualnye problemy sovremennoj nauki i proizvodstva, 2021:216-219.

A. Prutzkow, Criterion and Principles of Easily-modified Program. In Workshop on Materials and Engineer. in Aeronautics, IOP Conf. Series: Materials Science and Engineer., 2021, 1027, 012025, DOI: 10.1088/1757-899X/1027/1/012025.

A. Prutzkow, Tonkosti Programmirovanija v Primerah [Programming Subtleties in Examples]. Kurs, 2022.

Guidelines for organizing and formatting data on a worksheet. URL: https://support.microsoft.com/en-us/office/guidelines-for-organizing-and-formatting-data-on-a-worksheet-90895cad-6c85-4e02-90d3-8798660166e3. Last accessed 2022/11/07.

Top ten ways to clean your data - Microsoft Support. URL: https://support.microsoft.com/en-us/office/top-ten-ways-to-clean-your-data-2844b620-677c-47a7-ac3e-c2e157d1db19. Accessed 2023/01/03.

K. Broman, K. Woo, Data Organization in Spreadsheets. In the American Statistician, 2018, 72(1):2–10, DOI: 10.1080/00031305.2017.1375989.

A. Barbieri, Productivity Through Data Simplicity: Your Guide to Data Organization and Standardization in Excel. Amazon Digital Services LLC - KDP Print US, 2019.

P. Bewig, How Do You Know Your Spreadsheet is Right? Principles, Techniques and Practice of Spreadsheet Style, 2005.

J. Raffensperger, New Guidelines for Spreadsheets. In EuSpRIG, 2001.

D. Clough, Excel Tips for Chemical Engineers | AIChE, 2017. URL:https://www.aiche.org/chenected/series/excel-tips-chemical-engineers. Accessed 2023/01/03.

D. Limoges, Data Management. Excel Tips and Tricks to Summarize Data, Presentation, 2021. URL: https://www.ichpnet.org/events/annual_meeting/2021/ce/082_-_Limoges_Daniel_-_Data_Management_Excel_Tips_-_1up.pdf. Accessed 2023/01/03.

M. Izza, Twenty Principles for Good Spreadsheet Practice, 3rd ed. ICAEW, 2018.

Principles of good Excel use // Excel Advanced Training // PerfectXL. URL: https://www.perfectxl.com/online-excel-training/principles/. Accessed 2023/01/14.

M. Erwig, Software Engineering for Spreadsheets. In IEEE Soft., 26:25–30, DOI: 10.1109/MS.2009.140.

R. Teixeira, V. Amaral, On the Emergence of Patterns for Spreadsheets Data Arrangements. In Federation of Int. Conf. on Soft. Tech.: Applications and Foundations, Springer, 2016:333-345.

E. Dobell, S. Herold, J. Buckley, Spreadsheet Error Types and Their Prevalence in a Healthcare Context. In J. of Organizat. and End User Comput., 2018, 30:20-42. DOI: 10.4018/JOEUC.2018040102.

S. Powell, K. Baker, B. Lawson, Errors in Operational Spreadsheets. In J. of Organizat. and End User Comput., 2009, 21(3):24-36.

K. Rajalingham, D. Chadwick, B. Knight, Classification of Spreadsheet Errors. In Proc. of the EuSpRIG Annual Conf., 2000:23–34.

J. Strand, Error Tight: Exercises To Prevent Mistakes. In Psycholog. Methods, 2022, DOI: 10.1037/met0000547.

D. Isbell, Open Science, Data Analysis, and Data Sharing. In L. Plonsky (ed.) Open Science in Applied Linguistics, 2021.

H. Wickham, Tidy Data. In J. Statist. Soft., 2014, 59(1):1–23, DOI: 10.18637/jss.v059.i10.

P. Soranno, Six Simple Steps to Share Your Data When Publishing Research Articles. In Limnology and Oceanography Bulletin, 28, DOI: 10.1002/lob.10303.

K. Horstmann, R. Arslan, S. Greiff, Editorial Generating Codebooks to Ensure the Independent Use of Research Data: Some Guidelines. In Eur. J. of Psycholog. Assessment, 2020, 36(5):721–729, DOI: 10.1027/1015-5759/a000620.

S. Ellis, J. Leek, How to Share Data for Collaboration. 2017, DOI: 10.7287/peerj.preprints.3139v5.

F. Tort, Teaching Spreadsheets: Curriculum Design Principles. 2010.

C. Chambers, C. Scaffidi, Struggling to Excel: A Field Study of Challenges Faced by Spreadsheet Users. In 2010 IEEE Symp. Visual Lang. and Human-Centric Comput.:187–194, 2010, DOI: 10.1109/VLHCC.2010.33

L. Bartram, M. Correll, M. Tory, Untidy Data: The Unreasonable Effectiveness of Tables. In IEEE Transactions on Visualization and Computer Graphics, 2021, DOI: 10.1109/TVCG.2021.3114830.

G. Chalhoub, A. Sarkar, “It’s Freedom to Put Things Where My Mind Wants”: Understanding and Improving the User Experience of Structuring Data in Spreadsheets. In CHI Conf. on Human Factors in Comput. Systems (CHI’22), ACM, 2022, DOI: 10.1145/3491102.3501833.

N. Marz, J. Warren, Big Data. Principles and Best Practices of Scalable Real-Time Data Systems. Manning, 2015.

W. Galitz, The Essential Guide to User Interface Design: An Introduction to GUI Design Principles and Techniques, 3rd ed. Wiley, 2007.


Refbacks

  • There are currently no refbacks.


Abava  Кибербезопасность IT Congress 2024

ISSN: 2307-8162