There are a number of steps you can take to help ensure accuracy and help prevent errors in a spreadsheet:
- Design the spreadsheet to allow for growth and with a clear view of the purpose.
- Always protect cells where possible
- Turn off auto complete or the automatic conversion of data if relevant.
- Use drop downs rather than free text entry where possible.
- Ensure you handle blank data if a user forgets to enter something
- Use Named ranges where possible
- Use tables as data sources – these will expand and contract as needed and you can use structured referencing. Tables also self replicate formulas so all rows will have the same calculations.
- Ensure formulas are simple where possible and refer to data ranges. Don’t hard code values in formulas.
- Use different worksheets or workbooks rather than trying to do everything on one sheet.
- Have a proper naming structure for tabs – it’s much easier to understand names rather than Sheet numbers.
- Backup your spreadsheets with version numbers
- Test, test and test again.
If you regularly use a quotation template in Excel for working out prices are you sure it’s still accurate? We’ve come across a number of examples where spreadsheets have been in use for many years at a company but the person who first set it up is no longer involved with the process. The people using the spreadsheet now know how to enter information and get a price out, but they aren’t always sure about the steps in between.
Excel can be set up so that you can enter some of the information you need such as the customer name, products or parts and the spreadsheet produces a price quotation for you. The information you enter can be free text or from a drop down list. The price is normally worked out by taking information from other cells or workbooks within the same spreadsheet or from linked spreadsheets. These can be maintained independently and in some cases can be shared. The pricing can appear by using Excel lookup functions from a separate linked sheet or workbook. For more complex quotations, you can use the macros in Excel to calculate the price. Excel macros can also be used to send the price as a PDF or email it to the customer.
Spreadsheets can provide excellent templates for price quotation systems, especially if you have complex requirements, such as many different parts involved, multiple lookups or parts that can be customised. New parts can normally be easily added. However there is a potential problem if you start amending the calculations or parts are not added in the correct place. Even though Excel can be tailored to provide validation on entry boxes and macros that will run calculations automatically, it just takes a minor error somewhere and some of those figures could be generated incorrectly. It’s something you may never notice – perhaps the prices are just slightly out, however they are just as likely to be substantially out.
The underlying spreadsheet calculations could also have in built errors if adequate testing had not been done when the initial spreadsheet was set up. In complex cases, for example where product customisation is used, it is difficult to test every combination.
There could be other specific problems such as the way that rounding is handled. This can become evident if you have a lot of small value items.
Another common problem is the automatic conversion of data by Excel. In a study published in August 2016 in Genome Biology, researchers found an error rate of nearly 20% based on a review of 3,697 scientific papers. The most common example was the conversion of Gene symbols like SEPT2 (Septin 2) to “September 2”. Even if you’re not working in the science sector, this can still cause issues if you are not aware of it – you could have a product coding system where some codes change to dates and this could mean those products don’t appear in calculations.
At Bespoke Excel, we understand the benefits but also the drawbacks of Excel and we can help you to validate and check your spreadsheets. With our experience, as well as validating what you currently do, we can make suggestions about how Excel can be used to best advantage. Get in touch today and we’ll give you a free no obligation quote.
