The benefits of keeping your data in a separate workbook from your reports and processes.
Separation of Concerns
In a previous post we spoke about the benefits of keeping your dashboard, calculations and data in sperate tabs. If you missed it, you can read "3-Tier Architecture in Excel" here.
A sound design principle in Excel is keeping your dashboard, calculations and data in sperate tabs.
This “Separation of Concerns” allows you to create spreadsheets that are easier to maintain and update.
For example, making changes in your dashboard – such as insert or deleting rows & columns – won’t inadvertently break your formulas in the calculation tab.
Here data “flows” from the data tab to the calculation tab and from there to the dashboard (presentation) tab.
However, when there are several people working on the spreadsheet, particularly editing the data, this can cause some challenges.
One approach is to keep your data in a dedicated database, such as SQL Server or a cloud-based database. This isn’t always possible or practical, so one alternative is to keep the data in a separate workbook rather than a separate worksheet tab.
This provides several advantages including:
Excel as a Database
This was the approach I took for a recent project with a client. They had data, shared and managed by a small team across Europe.
In this design, a central workbook acts as the dashboard. It presents dashboards reports and automates the process of generating a series of Excel and Word business documents.
The data, however, is kept in a separate, dedicated workbook. It only contains data.
This workbook can be saved anywhere on their network.
Viewing the Data in the Dashboard
Maintaining your data in a separate workbook does not prevent you presenting it in your dashboard workbook.
There’s several ways to achieve this in Excel.
In this example, we treat the data workbook as a “database” (although it’s not technically one) and extract the data programmatically.
Clicking the Refresh button queries the data workbook and imports the data into the dashboard.
Refreshing the Data
Behind the refresh button is a macro which queries the data workbook as though it was a database.
This method, which has been around since the late 1990s, and has several benefits:
Conclusion
It’s not the panacea to data management in Excel as it also has several drawback such as:
However, while this method has its use cases, it isn’t the only game in town when you need to manage your data in a separate workbook. One other approach is with Power Query, which we’ll cover in another post.
0 comments