Some Principles of Spreadsheet/Workbook Development

 

Introduction.  Now that we have reviewed at least one basic workbook it should be easier to describe some overall guiding principles for spreadsheet/workbook development.  Obviously, people are going to develop things in different ways.  But there are still some underlying principles and patterns that can be useful to use for guidance when developing spreadsheets.

 

  1. Make the spreadsheet do as much work as possible.  You don't want to be doing a lot of the work to prepare the entries.  This should also make a spreadsheet more self contained.
  1. Whenever possible, a cell formula should be used rather than entering specific numbers.  Among other things, this vastly improves the automatic recalculation as well as providing a resource for determining how the calculations were done or how the entries were derived.
  1. Format the spreadsheet so that it is easier to understand.  Well chosen borders, fonts, legends and whatever definitely improve the readability and sense of overall organization.
  1. Name each spreadsheet in a workbook appropriately.  It is almost always important to group spreadsheets within a workbook if they have some functional interaction.  It is also worthwhile to name the sheets and workbook so that the names provide some sort of mnemonic related to their purpose.
  1. Name ranges and cells as much as is reasonable.  It is much easier to make use of formulas that have names in them rather than just cell locations.  You also need to make sure that the locations of these named cells and ranges are pretty obvious on the spreadsheet.