Spreadsheets are essential in businesses of any size and any type. Great spreadsheet design can prevent errors from occurring, and sometimes you just might need some help with this.

I have previously worked for a number of years in an Accounting role for Ergon Energy, as well as various administrative roles. These included payroll, accounts payable, accounts receivable, stock management, and bank reconciliation and finance.

I can share with you the benefit of my spreadsheet design experience in these roles.

The beauty of spreadsheets

  • You can quickly lay out information.
  • You can copy and paste from various sources.
  • You can link them to databases.
  • You can use them for recording data, reporting, calculations, charting.

The hidden traps

  • As a spreadsheet enables you to create a free-form design, it may be inefficiently laid out for data entry. This can make it difficult to know where everything is that needs to be entered each time you update the data.
  • When you add new data, you often have to remember to change the formulae.
  • It is sooooo easy to end up with formulae errors, even if everything was correct when initially set up.

To prevent these issues

  1. Data entry should be physically separated from the formulae, i.e. have a data entry sheet and put your calculations on another sheet. This may seem like extra work, but it will save a lot of pain later on.
  2. Protect your formulae.  If you don't they will be broken at some point.
  3. Make sure that you are using the right tool for the job.  A database solution might be a more logical fit.
~ Example of Reporting and Charts Using Excel