Find the big errors in a financial model quickly

By Nick Crawley on June 7 2009

CTRL + SHIFT + [

Using the keyboard in Excel can deliver much more than just time efficiency, there are some pretty cool things hidden away in Excel but finding out what they are can be hard work - little gems like this are covered on our training course so if you didn't know this maybe investigate PFM(A).

Here is a good one that highlights any cells that are precedents to a selected cell. If I am investigating a third party model and I need to demonstrate there are errors in it in just a few minutes - this is one of my power tools!

Finding the precedents of a cell almost instantly.

Here is how it works., below is a simple set of costs being captured in an annual summary, so whats the quick way to find the big errors?

Costs flowing into a total line.

First of select a single cell or a range of cells that you would like to identify the precedents of, my example is shown below

Highlight the cell or cells to be investigated.

Next press CTRL, SHIFTand the [ keys simultaneously. You will see that the cells that do not flow into the total line are not shaded, in the screenshot below you can see that the Lease in year 3 and 4 do not flow into the total properly.

Any cells that do not flow through are not highlighted.

This is clearly a really simple example but imagine if this was the Net Cashflow line at the end of a full bloodied project finance cashflow waterfall and you have a virtually instant way of spotting any exclusions, intentional or not.

As you can imagine, if the cashflows (or set of calculations you are investingating) are not laid out clearly and there are sub calculations and excess coding lines within the structure (which its best if there is not anyway) then the exclusions will be harder to spot. This is just one of a dazzling arrays of tricks for finding simple errors very quickly.

This is not only a really quick way of finding errors but also a rarely known trick that is sure to impress your colleagues, clients and boss! If you didn’t know this then I hope its as useful for you as it has been for me.

Comments

Pros, cons and alternatives

Submitted by Aleem on Thu, 18/02/2010 - 02:02.

The benefit of doing that is that you can use TAB to cycle through all the selected cells. Of course it won't select cells that are referenced from other sheets or files and you lose the selection of the cell you were checking out.

Clearing the "allow editing directly in cells" checkbox in Excel options lets double-clicking a cell do the same thing as CTRL+SHIFT+[

You could also hit F2. That not only highlights the referenced cells in different colours, but highlights those references in the formula in the same colours. Of course this doesn't highlight off-sheet references either.

In Formula view mode (CTRL+`) selecting a cell shows the same as what you would see if you pressed F2, but without editing the cell's contents.