We know that spreadsheets are error-prone. One of the possible causes for this is the user interface of a spreadsheet. In a spreadsheet, you define a model by entering formulas into cells. In these formulas, you can refer to the value of one or more other cells on the same worksheet, a different worksheet or even a different workbook. As soon as you enter the formula, the spreadsheet will hide the formula and present you with the result. The ease with which cells can be linked and the possibility to immediately see the results, make spreadsheet flexible and powerful. However, the exact same characteristics make it also very difficult to understand the relations between cells.
To make this a bit easier, we developed Polaris. It is an add-in for Microsoft Excel that makes the task of understanding and debugging formulas easier. Let’s illustrate this with a small example.
Consider the formula in cell C5 .
=(Students!P18 * Parameters!C3 + Certificates!J15 * Parameters!C4) / (‘Country KPIs’!C4 + ‘Country KPIs’!F3) * ‘Country KPIs’!I4
When we inspect this formula we see that the formula contains three different references to cells on the worksheet ‘Country KPIs’. To fully understand the calculation we want to check the values for these references. Therefore, we navigate to the worksheet ‘Country KPIs’.
Unfortunately, as soon as we activate this sheet, Excel activates the last selected cell on this sheet (which is not the cell we are interested in). And because it activates this cell, it will also show the formula for this cell in the formula bar, meaning that we lose the formula that we were analyzing. Now we have to remember the formula, the three references on the worksheet ‘Country KPIs’ that we wanted to check and we have to locate these cells manually. Not an easy task!
This is where Polaris will help. As soon as you select a cell, it displays its formula at the top of the screen. As soon as you navigate to the sheet ‘Country KPIs’ we still will loose the formula in Excel’s formula bar, but Polaris will keep the formula visible for us at the top of the screen. Furthermore, it will automatically navigate to the first reference in the formula on this worksheet. With either a button or a shortcut key (alt + ]) we can toggle between the three references on this sheet. When we have checked the value we can easily navigate back to original cell C5 on the worksheet ‘Funding’.
Want to know more about Polaris?
Let us know what you think of this tool. Would it work for you?