There’s an old Excel aphorism that says, “Excel’s greatest strength is its flexibility and its greatest weakness is its flexibility.”
One result of this flexibility is that far too many spreadsheets are created without any thought to structure or layout.
How to Structure Your Excel Workbooks
Perhaps the most common of these design flaws is to build spreadsheets in which the data, business rules and presentation are all contain in a single worksheet.
In software development, there’s a design concept called Three-Tier Architecture in which the software is divided into three separate layers or tiers:
Perhaps the most common of these design flaws is to build spreadsheets in which the data, business rules and presentation are all contain in a single worksheet.
In software development, there’s a design concept called Three-Tier Architecture in which the software is divided into three separate layers or tiers:
- 1Presentation Layer: This is part the user sees and interacts with. IN Excel this is commonly a dashboard of sorts.
- 2Business Logic Layer: This is the part where the business rules or logic are defined. In Excel this is done with formulas.
- 3Data Layer: This is where all the data (which the formulas reference) resides.
In many spreadsheets, the data, the business logic and the data are all contained within a one layer, namely a single worksheet.
Mixing Tiers
This is how you don’t do it - the worst of all scenarios. This one worksheet incorporates:
This example was taken from a live, mission critical workbook at a large financial services firm. It's a typical example of everything not to do.
Combining these three elements into a single tier (worksheet) makes the spreadsheet rigid and inflexible. It’s difficult to change one without affecting the others.
A better approach is to split each tier so it resides in its own tab.
The Presentation Tier
One or more tabs (tiers) would only contain the visual part (or “interface”) you interact with, such as a dashboard. The interface can be enhanced with ribbons, buttons, dropdowns, charts and PivotTables.
There are no business rules (or logic) in the formulas other than to retrieve calculated values from . . .
The Business Logic Tier
This tier contains the logic that manipulates and analyses your data and can be completely hidden from the spreadsheet’s audience.
This helps when calculations are messy or complex, allowing you to hide that complexity from the audience.
There is no data in this tier as the data is stored in . . .
The Data Tier
All the data resides in one or more tabs. It is well structured and stored in Excel Tables.
Also called “Separation of Concerns”, using three tiers (across multiple tabs) will allow you to create spreadsheets that are easier to maintain and update.
Conclusion
If there is one recurring problem I see with spreadsheet design in the financial services world; this is it.
Typically due to the constraints all businesses face - time and money - people take short-cuts in structuring their Excel workbooks so they are efficient, effective and easier to maintain and update.
Over time these simple workbooks grow organically to become beasts that are rigid and fragile. Taking a little time upfront to plan and structure your workbooks - especially those critical ones your business depends on - will pay dividends in the long run.
Do you need assistance in designing your critical spreadsheets? We're here to help. Contact us in the form below.
0 comments