Show Formulas

It's very rare to find an effective excel sheet without formulas. However you will only see the output of the formulas in the cell, unless you click on that specific cell. You can use the 'Show Formula'  

Here is an example of a sheet I just created: 

This is how the sheet looks when I select 'Show formula':

When you select a cell with a formula, you will notice that it highlights all the cells that the formula depends on:

This function is very useful when you are reviewing an excel worksheet. It can be used to find any errors or manipulations that may exist in the sheet, which would otherwise be very difficult to discover. Take a look at the next screenshot:


In the above, you might not discover the 'minor' errors and adjustments made to the formulas by just looking at the sheet, but it does contain them, and they are not minor. This is what the work sheet looks like when I click 'show formulas.'

You will immediately notice the various errors and manipulations that have been made in the worksheet. Some of values in the price column are not even formulas, but just numbers, there have been minor additions made to the formulas, the wrong VAT rate has been used and formulas referencing wrong cells have been used. It may even be difficult to notice that the SUM function have not be input correctly unless you select the cell and see that the first cell have been left out.

Now just imagine what would happen if you give such an invoice (forget above how an actual invoice should look like, just the calculations) and that customer discovers the errors at some point (though, I highly doubt they would really mind). In fact, in one of the future posts, I will show how to make an invoice that is at least not easily to manipulate.

Sheets with minimum data and simple formulas like the one shown in the example above can be easy to audit even without the use of show formulas, however it can be harder to audit it when larger sheets with more data and complex formulas are involved.

I guess that this post is becoming longer than I planned to because of the screen shots, so that's all for now. Be sure to drop any questions or comments below.