Formula Viewer

By breaking down formulas, the Formula Viewer display presents complex formulas containing function,references and nested conditions in a way that makes them easier to understand. The display is updated automatically and dynamically as you move from cell to cell and as formulas are updated so you see the structure of any formula. You can also keep the window open all the time so that you see the structure of any formula as you work.

A better view of a formula

As an example, take the formula:

=IF(SUM(CK10:CK11)<CK12,0,SUM(CK10:CK11)-CK12)

Unless you are a hardened Excel user, even this fairly simple formula is hard 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.

The ComplyXL Formula Viewer 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.

Why does CK10 return the value 10?

Image In the Formula Viewer cell references are clickable. If you click on the cell CK12 the Formula Viewer will present the formula in this cell and it's value (see the screenshot below).

In this case cell CK12 contains a reference to cell CK37 on worksheet TOTAL-319.

Click on this reference and you navigate to the cell. The Formula Viewer display is shown in the screen shot below.

This process can be repeated following the chain of calculations.

Tracking down the cause of errors

One of the challenges when creating or maintaining a worksheet is tracking down the cause of an error. Suppose that a formula like this causes an error:

=CK29+CK$25+CK21+(CL61/2)-CK14-CK10-(0.5*CJ10)-(0.5*CK11)- (0.5*CJ11)+(CL56/2)+(CL57/2)+(0.75*CM52)-(CI10*0.5)-(CI11*0.5)-(CF10*0.25)-(CH10*0.5)- (CG10*0.25)

Image If the cell references are nearby it may be possible to see the cause of the error. But if the cell is on another worksheet or is one of the cells used by SUM() function what then?

As you follow the calculation chain using the Formula Viewer, a history of the cells visited is maintained and shown in a list. You can click on any of the references stored in the history list to return to that cell. Alternatively you can use the Back button to return to the cell previously visited.

ComplyXL provides another way to view formulas and their differences by using the graphical display.

Formula differences

The second way to to see Excel formulas is to use ComplyXL. This powerful tool allows different workbook versions as well as different spreadsheets to be compared. ComplyXL presents changed and errored cells and formulas in an easy-to-understand graphical view which highlights cells containing changed values, formulas and type (was numeric is now text). Users are able to choose which filters to use for difference reporting.