Spreadsheet Best Practice

Consistency – no hard coding numbers in excel

This is a preview lesson
Purchase this course, or sign in if you’re already enrolled, to take this lesson.

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

= A1*B1

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.

hard coding numbers in excel

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