Formula viewer in Excel spreadsheets

I wanted to let everyone know about a really useful utility that is now in the current version of ComplyXL.

http://www.lyquidity.com/mambo/images/SWF/Formula%20Viewer.htm

I’m excited about this development because it is the first step on our journey to make the day-to-day use of Excel more intuitive. It can sit on your desktop and will be updated as you move from cell-to-cell or as you edit a formula.

Where this will go next is to add type information to Excel and goes to the point I made about the new version of Excel not really adding much to help create better more reliable workbooks. As developers our tools have strong typing built into them. If we try to add a thing of type apples to a thing of type oranges the tool will point out the error of our ways. By contrast, Excel will allow you to add apples to anything. Add numbers to strings of characters, strings of characters to dates and so on. This makes it easy to use because there’s no cumbersome definition phase to endure in which users tell Excel that cell A1 is of type apples. The corollary of “easy to use” is “easy to make a mistake”. But by letting the user add types to cells they can specify which cell is “Revenue”, which is “Cost of sales” and which is “Gross Profit” so that the types can be checked to make sure formulas make sense. “Gross Profit=Revenue-Cost of Sales” would make sense. “Current Liabilities-Gross Profit” would not.

Anyway, back to the formula viewer. Take the formula:



=IF(SUM(CK10:CK11)

Unless you are a hardened Excel meister even this relatively simple formula is difficult to understand at first glance. Once you have worked out what part is the condition and which parts are the TRUE and FALSE results you have no idea what values are being used and how the result is computed. Yes, you can use the formula evaluator but it's hidden away and does not show you the values your formula will work with as you write the formula.



Formula Viewer (see the image) changes the presentation to make the structure of the formula clearer and shows the values used by each part of the formula so that you can see how the formula will work. For example, you can see that the condition's result in this formula is FALSE because the sum of the cells in the range CK10:CK11 (10) are not greater than the value of cell CK12. OK you still have to know that that “<” means less than but we could make it say “is less than”.

Also you can see how by adding an extra column called “Type” we can show the user whether or not their formulas are logical.

All this really adds benefits to our auditing users.