Spreadsheet Coding Convention

Improvements? Suggestions? email lif.zone.main+dna@gmail.com

Overview

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 sysadmin@holaspark.com.

  • Move the newly created sheet to the correct folder under Spark drive
  • Change the sheet's owner to sysadmin@holaspark.com
    • Right-click on the sheet and choose Share
    • Click on Advanced in the bottom right corner
    • Search for sysadmin@holaspark.com
    • Click on the pencil icon next to Sysadmin and choose Is owner

Once done, the sheet will be moved to Spark's storage.

Number format

Thousands separator

Should always have commas thousands separator.
Click in the menu: FormatNumberCustom number format... → select #,##0 (1,235) format.

77302 77,302

Avoid decimal point

Don't use decimal points unless they are substantial.

77,302.23 77,302 0 0.23

Date format

Use only or DD-Mmm-YYYY (or DD-Mmm-YY).
Click in the menu: FormatNumberMore date and time formats... → select 5-Aug-1930 format.

15/1/16 2016-15-01 15-Jan-16 15-Jan-2016

Cell content & format

Single value

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.

C4 cell: "15-Jan-16 started with the new green button" C4 cell: "15-Jan-16" D4 cell: "started with a new green button"

Never merge cells

Don't merge cells. It makes the sheet manipulation impossible.

Avoid highlighting

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' (ViewFreeze1 row) so that it doesn't scroll with the values.

Sharing

By default build the sheets in the Spark directory so that they are accessible to all without needing to share.

Sanity checks

Incorporate sanity checks that will discover errors. For example by summing up % columns to make sure they total 100%.

Obsolete sheets

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.