#321: Formula flow

People who design spreadsheets tend to think in straight lines. This makes life easy when undertaking the programming (which is pretty difficult, evidently), but it creates some additional headaches when the data are displayed.

I recently had to decipher the details of a spreadsheet written by someone else. It was a complicated multipage affair with a huge number of complex formulae on board. Usually, making use of an inbuilt feature, such as ‘trace dependents,’ would be enough to see the underlying structure of the data. When data and formulae lie in the same rows or columns, however, all the arrows lie on top of each other and it becomes impenetrable.

Today’s invention is a two-fold improvement.

1. Replace the straight-line arrows with arcs. The radius of the arc linking two cells could be in proportion to the distance between cells. This would greatly reduce the degree to which arrows overlap and thus allow the relationships to be more apparent.

2. Introduce a step-through function in which cells are visited one by one, starting from some chosen location. At each cell, the precedents and dependents are drawn in (using two different colours and the curves described above). The speed with which this ‘movie’ is displayed would be variable, allowing a ‘dependency flow pattern’ to be perceived.