Minimize Spreadsheet Risk with ClearFactr's Cell Dependency Tools

By Dean Zarras

I recently built out a fairly elaborate household budget with numerous sections and attributes. The model was fairly complex, and got increasingly so as I added additional attributes and summation cells. It's the type of Plan that can easily lead to one aspect of "spreadsheet risk": Acting off of information embedded in a spreadsheet that's actually wrong due to overlooked and hard-to-find computation errors.

To ensure that things were bug free, I made use of two different ClearFactr features, which indeed alerted me to a few errors:

  1. I first turned on the Ancestry Highlighter and set it to one level of ancestry (slider fully at left). By clicking on my grand total, I could verify that each of the section subtotals were parents of the grand total. One of the sections was not included in the grand total, so it was not highlighted.
  2. Moving the ancestry slider to level 2, I verified the each of the attributes within each section total were included. A few items had been left out, so they showed up as holes in the color renderings.
  3. Prior to fixing the un-connected attributes -- as a sanity check – I turned off the ancestry highlighting and turned on the Terminal Cells highlighter (red arrow, above). Sure enough, those same cells that failed to be color-coded above were now highlighted in red, meaning that they weren’t controlling anything. Adding these attributes to their respective section total formulas easily fixed the problem.

All this goes to show that even if you have no need to run fancy simulations or scenario comparisons, ClearFactr’s easy-to-use cell dependency tools can still add a lot of value and save you a lot of grief.

Best of all, the entire experience sparked some ideas for some up-coming features that will help prevent such errors in the first place. Stay tuned!

Spending too much time worrying about spreadsheets...
and not enough about your business?