Design in Blocks

  • Tagged with:
  • Blocks,
  • Design,
  • Excel,
  • Spreadsheets,

One of the more important considerations when attempting to create a well designed spreadsheet model is the concept of designing in blocks.

“Blocks” is a colloquial rather than a technical term, and means a “range of contiguous cells that have something in common”. This could be something trivial like number format (currency, percentages, etc.), cell shading, and the like. Or it could be something more meaningful like a logically identical formula.

One of the benefits of designing in blocks is that it becomes easier to see where areas of your model start and stop. It is also then easier to add new rows or columns to the block. Furthermore, it becomes much easier to navigate through your model, especially if you use keyboard shortcuts like Ctrl and arrow keys.

Design_In_Blocks_Example

But possibly the greatest benefit comes when writing formulae that refer to the block. In this case, including the blank row/column at the start and blank row/column at the end of the block will ensure that new rows or columns added to the block later will always be included in the formula.

In this  example, you can see that the total formula in cell D10 refers to the block of items above. Importantly, it includes the blank row at the start of the block (row 3), and the blank row at the end of the block (row 9). This means that when a new row is added at the start or end of the block it will be included in the total formula.

1 Comment so far Join the Discussion

  1. Rickard Warnelid – Corality

    6th July 2009 at about 12pm

    Hi Darren,

    I completely agree that building financial models in ‘blocks’ is that way to go, however I would like to highlight a significant risk using your approach of including the two empty cells D3 and D9 in your SUM formula. The inclusion of these cells do have the advantage that expanding the SUM will be quicker, but the downside is that for a user who is not aware of this unique approach could potentially use cell D3 for personal or temporary notes.

    Alternative ways of achieving a similar outcomes could be to either format cells D3 and D9 as ‘empty cells’ using a unique Style, highlighting that they should not be used or use an input message with the Data Validation tool. Alternatively, and preferred, would be to add a few spare, empty lines, when building the original spreadsheet which limits the need to increase the number of lines included in the SUM.

    Overall, I am very keen on simplification and some more of this can be read in this article:

    http://www.navigatorpf.com/training/tutorials/simply-a-project-finance-model

    Regards,
    Rickard
    http://blog.corality.com

Join the discussion

Popup

Register your Interest

Please register to keep up to date with our developments.

Register your Interest

Please register to keep up to date with our developments.

cialis | shopelix.com