Spreadsheet Best Practice

Consistency of Formula across rows/ columns

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

When you build your formula, you should try as much as possible to build only one formula that you can copy across or down. Try and avoid building inconsistent formula within a row/ column.

As noted in the picture below, row 12 seems to have 2 different formula where a number of cells use the 5% inflation rate, but one cell (I20) uses the 7%. This is inconsistent and likely to create a misunderstanding or is already an error.

Consistency of Formula across rows columns

Consistency of Formula across rows/ columns

Rows/ Columns should ideally contain only one formula that is copied across or down. This can be achieved by developing formulae that address all possible situations that could occur.

With this in mind avoid having subtotals in the middle of your spreadsheet. So if you have a monthly model and want to have some annual totals, don’t put it in between the months. It is easy to convert a monthly model to annual. It is not so easy to do this when the rows and columns aren’t consistent.

To follow along go to 1.5