Excel Expert strives to make all our spreadsheets “robust”. But what does this mean?
The answer is simple: it means that all the equations within the workbook will continue to work properly, even when large sweeping changes are made to the workbook, unexpected data is encountered, columns are moved, etc. Think of it as “immunity from changes and errors.”
A large part of making workbooks robust involves following Bill’s Rule #1. For example, don’t type the “column index number” argument into VLOOKUP as a hard-coded number; instead, refer to a cell which contains that number, typically a cell in the column that VLOOKUP is using, but above the data in that column. If that cell contains a formula which computes the column index number of the column that VLOOKUP is using, based on the position of the column on the worksheet, then VLOOKUP will continue to lookup its information from the correct column, even if columns are inserted, deleted, or moved around wihin the table. The formula recalculates the column index number when the column moves, and VLOOKUP behaves robustly.
If users will enter data into this workbook, it’s a sure thing that eventually a user will make a mistake; perhaps entering a negative number where none are allowed, typing a malformed date, or mispelling a key name or part number. A well-defined user interface equipped with Data Validation protection on every input cell can prevent this type of error, because Data Validation prevents bad data from being entered.
Additional robustness can be built-in by avoiding equations which require databases or tables to be sorted in a particular order; protecting key equations from being accidentally deleted, modified, or typed over; using ‘dynamic tables’, where the equation’s ranges expand automatically to accommodate the data added to them; using Conditional Formatting’s “automatic coloring” to highlight error conditions (where ‘robustness’ depends on the user’s eye to see the colors), and many other techniques.