Consistency – no hard coding numbers in excel
One of the most common spreadsheet errors is as a result of hard coding numbers in excel cells.
Hard coding of values means that with a formula like
you also include a value, so the formula becomes
= A1*B1 + 500.
The +500 is a hard coded number which will result in errors no matter how careful you are. As shown below, if we want to add VAT of 14% to a sales number it would be better to create a new cell with 14% and link the formula. Including the 14% as a number in in the cell is dangerous.
Best Practice Rule- Data or Formula- Not both
Cells should contain either data or formula. They should never be mixed. One of the most common causes of errors is a number ‘just quickly’ added to a formula to make it work ‘for now’.
The video clip below shows the issue and why this can be a problem.
To follow along go to 1.1 and 1.2