Following a discussion on spreadsheets at the Hereford Referrals Brunch last Friday, I thought that some tips are worth sharing.
Many spreadsheets are built, used and then forgotten. Others are well used and get developed further to provide more information to the business. Some “one-off” spreadsheets become business critical. It pays to use a sound structure to start with.
Here are some tips to get you going:
Separate out numbers which may change
Although it is easy to use “=B13*1.2” to add VAT, or “=F22*0.45” to calculate travel costs, if either of these rates change you will have to go through the entire spreadsheet and make updates. As well as the time this will take, there is also the possibility that you might miss one and render the calculation inaccurate.
It is better to create a worksheet called “Constants” and put the rates on this page. The calculations then become “=Sheet1!B13*Constants!B2” and “=F22*Constants!B4”. You set up the formulae in the same way as usual: just click through to the Constants sheet to select the correct rate.
With this method, you can change one number and immediately see the effect on your bottom line.
Identify your formulae
One of the biggest dangers in using a spreadsheet is that you overwrite a formula, making the bottom line totals inaccurate. My tip here is to use colour to show the difference between cells which are entered and those which are calculated.
You can work this in two ways: either colour cells containing formulae as you create them, or colour cells where you want data to be supplied. Your business may have “house rules” in place for you to follow. If not, now is the time to create them! Make all your spreadsheets follow the same standard and everyone will know what to do.
Protect your Spreadsheet
Once your spreadsheet is stable and is working correctly, you can protect it from changes. This is a two stage process: you have to define which cells you want to lock and then protect the workbook. The Excel help on the subject takes you through the process. Click “Protect Sheet” on the Review tab and click “?”. For once, this takes you straight to the right place!
Protecting your spreadsheet can be a fiddly business, so you need to make sure it will not need repeating too often. Make sure you test your calculations thoroughly before doing it.
One final tip: if you do use a password, make sure you don’t forget it!