Introduction
Many people use spreadsheets on a regular basis without receiving much instruction in how to do so, leaving them to work out styles, structures, and conventions for themselves. This sometimes leads to choices that make spreadsheets harder to understand and use to their full capabilities. This guide contains provide principles and practices of spreadsheet construction to help you...
- Quickly settle on a good organization for your data
- Collect data more consistently with fewer errors
- Reduce or eliminate extra processing steps before you can analyze data
- Make spreadsheets easier for collaborators to understand and use
- Keep access to documents and data in the long term
- Facilitate machine reading and manipulation
Most of the tips provided here focus on making calculations easier, not necessarily improving the way data are displayed in a table. Depending on your goals for your spreadsheet, you may find yourself making some tradeoffs between easy readability for humans vs machines. However, unless your sole purpose is to display the data in a table, you will likely help yourself in the long run by incorporating more of the tips presented here.
Key Points
Much of the most helpful work you can do happens before you start collecting data
- Think through the structure of your worksheet
- Develop conventions for naming variables and columns -- be consistent
- Set column formats and input validation before you start entering data
Spreadsheets can handle a lot of complexity, but complexity is not necessary for a good spreadsheet. Instead, try to keep it simple:
- Use names that are descriptive, yet concise
- Put only one type of information in each cell
- Be careful how you handle dates
When you think beyond a single spreadsheet, the best things you can do for yourself are to try to enforce transparency and consistency
- Use a separate file in the same folder to document units, notes, processing, and other details needed to understand your data
- Develop a standard way for how you name different files in project folders, and how you keep track of file versions
Before you begin...
Before you start collecting data, think about what you need to be able to do with your spreadsheet. Ask yourself questions like the following:
- What types of data will you collect?
- Will you analyze data in the spreadsheet? What calculations will you use?
- What outputs will be involved?
- Will you make charts or graphs from your data?
- Is it for personal use, or will multiple people use it?
- How long will you need to...
- ...use it?
- ...add data to it?
- ...refer to it?
