What is the best layout when working with months/quarters/half years and full years? There are a few common structures. I prefer the one that lets you create single formulas that can be quickly copied across and down with as few copies as possible.

Let’s say you have a two year budgeting and forecasting model. The image below has three different column structures.

The first two show just the first year of the two years. The second year in the first two is just a copy of the first year structure.

The third structure has all the months together and all the summarising columns on the far right. The column widths are thin to fit the structures in the image. They would be widened in practice.

Structure One is the hardest to use as you need to create different formulas for the months versus the subtotal columns in the middle of the months. This requires multiple copies to copy the monthly formulas across the structure.

Structure Two is easier to use than Structure One because all the months are together. The summary column formulas on the right can also be simplified by using helper cells to have one formula for all the summary columns – see below example of helper cells for Structure Three.

Structure Three is the easiest of the three to work with as you can have one set for formulas for all the months and then another set of formulas for all the summary columns. Again using helper cells to simplify the summary column formulas.

The helper cells in the third structure could look like the image below.

The formula for cell D5 which has been copied across is

=D2&D3&D4

This creates a code that can then be easily used by the summary cells on the right.

The formula for cell AB7 is

=SUMIF($D$5:$AA$5,AB$5,$D7:$AA7)

That single simple formula (with the helper cells in rows 2 to 5) can be copied across and down to populate each different summary column.

One formula for all the different summaries – thanks to the helper cells.

You can download a copy of the file at the button below.

Example File