These guidelines are for spreadsheets that will be used by multiple people at Spark. The guidelines are meant to create sheets that are simple to digest, to use, and to expand. We work only with Google sheets.
Consistent and Minimal
Most important rules: Be consistent. Be minimal. Read these sections carefully.
Use Google Sheets
Why we use Google sheets?
We use exclusively Google Docs Sheets, never Microsoft Office Excel.
This allows us to easily share and contribute the document, control
revisions, and provide a common platform.
Due to historic reasons, we may sometimes refer to a spreadsheet in short as an 'XL' - but we still mean Google Sheets, not Microsoft Excel...
How to save a new Google sheet?
Newly added files should be owned by email@example.com.
- Move the newly created sheet to the correct folder under Spark drive
- Change the sheet's owner to firstname.lastname@example.org
- Right-click on the sheet and choose Share
- Click on Advanced in the bottom right corner
- Search for email@example.com
- Click on the pencil icon next to Sysadmin and choose Is owner
Once done, the sheet will be moved to Spark's storage.
Should always have commas thousands separator.
Click in the menu:
Custom number format... → select
Avoid decimal point
Don't use decimal points unless they are substantial.
Use only or DD-Mmm-YYYY (or DD-Mmm-YY).
Click in the menu:
More date and time formats... → select
Cell content & format
Should have only one value, so that the cells are sortable. If you have 2 values in a cell, split it up in to 2 different cells.
Never merge cells
Don't merge cells. It makes the sheet manipulation impossible.
Don't highlight cells to show their status. If you need to highlight its typically a sign that you need another information cell next to it. Why? So that you can sort cells according to this data (highlight is not a sort feature yet) Should have only one value, so that the cells are sortable.
Column and Row Headers
First row should typically have the column headers,
and first column should typically have row headers.
These should be highlighted in a different color, in bold, and row/column of headers should be 'frozen' (
so that it doesn't scroll with the values.
By default build the sheets in the Spark directory so that they are accessible to all without needing to share.
Incorporate sanity checks that will discover errors. For example by summing up % columns to make sure they total 100%.
If the sheet is no longer in use, due to being replaced by a different sheet, erase all the sheets, and leave a text on the only sheet left with the link to the new sheet, so that nobody uses that sheet by accident.