Learn What Drives Your Spreadsheet, and What Doesn't, Faster Than Ever

By Dean Zarras

We’ve just released some subtle changes to ClearFactr's automatic cell type highlighting features. They make it even faster to determine what cells:

  • Start the calculations in the rest of the model
  • Look like formulas but are actually constants
  • Are driven by one or more other cells
  • Are driven by other cells but drive nothing else
  • Or, do nothing!

Let’s go through a simple example. Here's a group of cells showing some monthly salaries by employee name, plus a few other monthly expenses:


ClearFactr's cell type and highlighting tools will quick shed light on what's going on. The first thing we'll do is examine the "root" cells -- cells that have no parents, but do have children. They're the starting points of any model:


The monthly salary for Sara, Bill and Bob are all "inputs" to the model. Bill's stands out with a slightly different color. Clicking on to the cell, we see that ClearFactr is drawing attention to what we call a "computed constant":


Notice that Bill's monthly salary has been entered as an annual number ($75,000) but then divided by 12. That may be acceptable in this case, but very often these "computed constants" are buried into a spreadsheet and represent all kinds of hidden knowledge and ad hoc calculations -- often perhaps worthy of a comment at the minimum.

Next we'll highlight the formula cells:


In this case, the current cell -- bordered in purple and labeled to the left of the formula field -- shows Jane's Monthly Salary and we see that it's a function of Bob's (and great for Jane in this case!)

Now we'll turn the Root cells back on, but then also highlight the Terminal Cells. Terminal cells are cells that have parents (i.e., they're computed), but have no children -- the action stops there. At the "edges" of your model, that may make complete sense, in totals, for example.

In this case, however, we see that Fred is labeled as a Terminal Cell. That's a problem:


Sure enough, if we click the gears icon and show the Cell Dependencies for the Total, we'll quickly see that Fred has been skipped! Also notice that the formula says "=sum(Sara through Jane)"


Contrast that with how things look for Jane. We can see that Jane's salary is a function of Bob's, but also controls the Total.


Lastly, take note that in all of these pictures, Rent and Computer Hosting's numbers never highlighted in color. They're not roots because they don't drive anything but they're not formulaic, either. We're just getting started, so they don't do anything in the model yet.

Want your model to do this stuff and more? Sign up for ClearFactr and be using things in about a minute, for free.

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