Spreadsheets
Microsoft Excel spreadsheets can be used for much more than just adding up columns of numbers. In fact, there are three areas of spreadsheet design in which we specialise:
Basic Spreadsheets
Case Study of a Basic Spreadsheet
If your business relies on Excel spreadsheets for day-to-day operation, how confident are you that they are working correctly?
- Do they contain any cells displaying #VALUE!, #DIV/0! or #N/A? If so, these are an indication of errors that will be propagated through to any other cells that use them, with the risk of rendering the resultant values useless.
- Is it obvious which cells contain formulas and which cells require their values to be entered manually? If it isn't, then it's only a matter of time before someone overwrites a formula with an entered value, resulting in all sorts of problems. Better still, you can protect cells that contain formulas against unintentional overwriting by defining Styles which can be applied to each type of cell.
- Do the formulas use error-checking functions, for example ISNUMBER(), ISTEXT(), ISERROR(), to detect bad values and handle them intelligently? If not, then if a function that expects a numeric value (for example the third parameter of VLOOKUP) is presented with a derived value that is textual, the result is going to be unpredictable.
- Is there a document somewhere that describes what the spreadsheet is supposed to do, how to use it and how to modify it? More than likely there isn't. But without even basic instructions, each user will probably operate the spreadsheet differently, and introduce errors that are difficult to spot.
Not everyone realises that a spreadsheet is effectively a software program and, to be reliable, it should be created using formal software design processes, beginning with the list of user requirements that define its intended purpose and ideally ending up with a set of test conditions that can be used to confirm its correct operation.
Unfortunately the reality is that most spreadsheets are created 'at the keyboard' to cater for a fairly circumscribed set of data that prevails at the time of their design. Error conditions are rarely taken into account and mistakes can creep in unnoticed. Inevitably, the spreadsheet's functionality grows beyond its initial brief and it becomes unwieldy, unreliable and unmaintainable. The ultimate problem occurs when the original author moves on and it falls to someone who is unfamiliar with its operation to take over maintenance of the spreadsheet and struggle along as best they can.
But what can you do about it?
One of the most effective tools for checking and correcting the operation of a spreadsheet is a second pair of eyes - someone to examine it without any preconceptions of how it works; to question every formula; to check the values of any constants used and to identify any potential error conditions.
But what if you don't have anyone in your organisation with the appropriate level of expertise to validate or fix your spreadsheets? Then that's where we come in.
In addition to the design of new spreadsheets, we offer three levels of Spreadsheet Services:
- Spreadsheet Health Check - a free report containing an assessment of the condition of any spreadsheet that you are concerned about.
Excerpt from a spreadsheet health check report. - Spreadsheet Auditing - checking the integrity of the spreadsheet, correcting mistakes and implementing error-detection and error-prevention mechanisms.
- Spreadsheet Redesign - equivalent in some ways to designing a new spreadsheet. This has the advantage that the spreadsheet's overall operation has already been established but its implementation has been compromised. This service includes data cleansing and documenting the requirements and operation of the spreadsheet.
Spreadsheets with Form-based User Input
Case Study of a Spreadsheet with Form-based User Input
A significant number of errors in spreadsheets occur when values and text strings are typed in manually using the keyboard. Form-based input with supporting macros is a simple way of eliminating such errors.
A form is a pop-up window that contains input controls (for example: buttons, dropdowns, checkboxes) that promote accurate data entry. For example, instead of typing in a filename, you could use a form containing a control that will let you browse to the file's location using the mouse without typing anything (just like you do when opening a document from, say, Microsoft Word). Instead of typing in a customer name you could use a form that displays a dropdown list of all your customers, from which you select the one that you want, thereby eliminating the possibility that a name is entered incorrectly.
Similarly, values that do have to be typed in, such as postcodes and email
addresses, can be validated to ensure their format is correct. Also you won't
need to type in dates manually, if you have a form containing a pop-up
calendar that ensures that you can select only valid dates.
Example of form-based input.
We have many years experience in setting up such forms and, although they may be complex to construct (that's what we're here for), they are relatively simple for you, the user, to maintain, since all the data needed by the controls on the form is stored on a separate worksheet in labelled columns.
We can design a new form-based spreadsheet to your requirements or take your existing spreadsheet and supplement it with a form-based front-end to make it easier to use.
Spreadsheet-based Applications
Case Study of a Spreadsheet-based Application
These are complex spreadsheets that contain many forms and typically use a separate spreadsheet file as a database.
For example, a simple spreadsheet-based application might allow the user to click on a button that converts a worksheet into a PDF file, select several email addresses from a dropdown list and then click on another button to automatically email the PDF file to the each of the selected email addresses, all from within the same Excel spreadsheet.
At the other end of the scale, we can design a spreadsheet-based application
that can be used to simplify and automate an entire work procedure, i.e.
manage all stages of your business process from order inception through to
order completion. Example of this type of spreadsheet-based application.
But what's the point of all this? On the face of it, adding complex functionality to the humble spreadsheet might appear to be a pointless exercise in technical wizardry, pushing Excel to the limits and making it do things it was never designed to do.
But the truth of the matter is that Microsoft deliberately underpinned Word, Excel, Outlook and PowerPoint with a common programming language (VBA - visual basic for applications) to enable them to work seamlessly together (often referred to as Automation).
If you have complex work processes that are currently accomplished using Word, Excel and Outlook separately, we can integrate them into a single spreadsheet-based application that interfaces with Word and Outlook automatically to emulate your existing workflow and provide a user interface that can be operated by an unskilled administrator.
If you can't view PDF files you can download Adobe Reader free from the Adobe website by clicking on the button below (opens a new window).
