Find numbers in Excel formula

Find numbers in Excel formula

The most common error in spreadsheets, often referred to as hard coding. This is where you create a perfect formula and then add a number to it. Good spreadsheet practice suggests that a cell should contain references (=A1+B1) or numbers (=25) only, and they should never mix (=A1+B1+25).

So if the formula says

=VLOOKUP( D1, F8:G25 , 2, false)

and because it doesn’t balance you make it

=VLOOKUP( D1, F8:G25 , 2, false)  + 521.

you are running a big risk.

That hard coded number is a significant risk because it won’t be clear why it is there. Also, next month, when the spreadsheet is not balancing and you forget this cell, you are likely to put a – 521 in another cell!

Note: if you have stumbled onto this page it would be best if you started at the beginning

Below a video clip explaining what this test in Spreadsheet Professional means


Purchase Spreadsheet Professional


Back to: Auditing an Excel spreadsheet > Prioritise which formula to look in for errors