Note: This is a re-post of the same content posted last December. It’s easy to fall back into bad habits, so I thought it was worth reiterating these three good ones.
Inheriting an existing Excel model can be both a blessing and a curse – the former in terms of not having to build the model yourself, and the latter in terms of not knowing what you’ve just inherited. Sometimes I’ve been initially relieved to not have to start from zero, only to conclude several days later that it would have been easier just to build the whole thing from scratch.
The problem is that every model, whether you build it yourself or not, is unique (usually just unique enough to make you want to pull your hair out or frisbee your laptop at some point), and so you never know which route will end up being the less troublesome one to pursue.
So while I unfortunately can’t give you rules for determining which models you should retrofit and which ones you should just build from scratch, I can give you some best practices for when you do inherit a model and decide to continue using that model.
1. Unhide all hidden sheets, all hidden rows and columns, and ungroup all rows and columns, and inspect everything closely. These hidden elements were built for a reason, and they were hidden or grouped for a reason — maybe because they were no longer important, or maybe because the previous owner was hiding something they didn’t want a particular party (such as the lender) to see when the page was printed, or hiding something they didn’t want anyone to see (such as their manager).
2. Expose any invisible cell contents. While it may be rare, some people will actually hide certain cell contents by formatting the contents to be the same color as the cell’s background color, which renders the contents (whether it be text or numbers) invisible. To see if there is anything of this nature in the spreadsheet, you can temporarily “paint” the spreadsheet a color such as light green, which will make visible all cell contents that are formatted white on a white background, as well as any cell contents formatted the same color as their cell background (except, of course, that formatted in the same exact light green color).
3. Make sure that any formulas that differ from column to column in the same row are understandable. While it is always preferable that formulas applied over multiple periods are identical, sometimes there is a legitimate reason for the formula in the first period (Time Zero) to differ slightly from that in the subsequent periods. Make sure you know what is going on in every column.
Hope these help. What other best practices do you employ when you inherit models?