What a wicked web we weave. Ah yes, through all of our toil, we sometimes end up with a model that is just way too cumbersome to navigate and make inputs, way too slow to calculate, and way too difficult to explain to others. What to do? Let’s take these one at a time.
Let’s say you’ve got a hairy beast of a model, with too many tabs, too many rows, and too many places where the user is supposed to direct their attention to make inputs. If this is the case, there are a few remedies:
- consolidate all inputs on to a single tab, labeled “Inputs”
- insert hyperlinks on all of the other tabs that link you back to the Inputs tab (Alt+N+i2 on PC, Insert > Hyperlink on Mac)
- use grouping to hide certain rows and columns from displaying as a default
Sitting and waiting for your spreadsheet to update calculations is a huge pet peeve and an equally huge productivity killer.
- The first question to ask is: why is your model calculating slowly? Is it because there are other Excel files open at the same time, which are slow to calcluate, and those files are slowing down the calculation of the subject file, which does not have circular references? If so, close those other files when working on the subject file.
- If your file does have circular references, you can do a couple of things to remedy the net amount of lost time due to their presence. Set calculations to Manual, such that they will only occur when you actively hit F9 (Cmd= on Mac) to trigger calculations to run.
- Remove the circular references if possible.
Unfortunately our colleagues can’t read our minds, and so often they misinterpret spreadsheet elements or are lost altogether at what they are viewing.
- Standardize formatting of inputs in bold blue and outputs in regular thickness black/red
- Use Custom format suffixes for non-currency numeric values so that it is clear as a bell what is currency, what is GSF, RSF, FAR, $ per/mo., etc. (Ctrl+1 on PC, Cmd+1 on Mac)
- Annotate key cells by inserting Comments (Alt+I+M on PC, Fn+Shift+F2 on Mac)
- Include an instructions tab.