Saturday, 12 July 2014

20 spreadsheet principles for your business

  1. Determine what role spreadsheets play in your business, and plan your spreadsheet standards and processes accordingly. Spreadsheets are documents like any other.
  2. Adopt a standard recognisable form for your organisation and stick to it. Use the logo and think document quality.  Add a date, prepared by, reviewed by box.  The date will save sending spreadsheets with differant names eg SpreadsheetJanuary and SpreadsheetJuly.  Users can see whether its the latest.
  3. Ensure that everyone involved in the creation or use of spreadsheets has an appropriate level of know¬ledge and competence.  Take the time to present it and after a month re-present the spreadsheet to check understanding.
  4. Work collaboratively, share ownership, peer review.  The use of google drive makes this very easy and assists with document management as your changes are automatically saved so the document is upto date and co-worker contributions are not lost..
  5. Before starting, satisfy yourself that a spreadsheet is the appropriate tool for the job.  Will a word document do the job?
  6. Identify the audience. If a spreadsheet is intended to be understood and used by others, the design should facilitate this.  Not everyone is mathematical.  A few images work wonders.  Include an audience list of co-workers in the welcome sheet.
  7. Include an ‘About’ or ‘Welcome’ sheet to document the spreadsheet.  
  8. Design for longevity.  Have a few spreadsheets and stick to them.
  9. Focus on the required outputs.
  10. Separate and clearly identify inputs, workings and outputs.
  11. Be consistent in structure.
  12. Be consistent in the use of formulae.
  13. Keep formulae short and simple.
  14. Never embed in a formula anything that might change or need to be changed.
  15. Perform a calculation once and then refer back to that calculation.
  16. Avoid using advanced features where simpler features could achieve the same result.
  17. Have a system of backup and version control, which should be applied consistently within an organisation.
  18. Rigorously test the workbook.
  19. Build in checks, controls and alerts from the outset and during the course of spreadsheet design.
  20. Protect parts of the workbook that are not supposed to be changed by users.