The Librarian Concept: A Simple Solution to Excel’s Consolidation Nightmare

For many of the advanced concepts we’ll cover, it’s essential to first grasp some fundamental ideas. These foundational principles will serve as building blocks for understanding more complex topics. One such key concept is the librarian, which plays a crucial role in enterprise Excel architecture solutions. Understanding this concept is necessary for discussing more advanced ideas, such as cloud computing and data management.

What is the Librarian Concept?

To illustrate this idea, imagine a scenario where multiple people are working on a project, but the necessary books are scattered across different homes. No one knows where to find the books when they need them, making it impossible to progress efficiently. This chaotic setup mirrors the issue of unstructured data in spreadsheets.

The solution? A library with a librarian who stores all the books in an organized manner. The librarian ensures that books are easy to find, updates are systematically stored, and nothing is misplaced. This structured approach is precisely what we need for data management in Excel: a central, structured location where data is stored, accessed, and maintained systematically.

The Problem: Spreadsheet Consolidation

One of the most common issues Excel faces—often cited as a weakness—is consolidation. Imagine an organization with 400 stores across 90 cities, 50 countries, and four regions, all rolling up into one group. Each store submits a budget spreadsheet in a standardized format. Someone in the finance team must then aggregate all these budgets into meaningful summaries by store, city, country, and region.

Historically, people have attempted to do this using external links between spreadsheets, a method that quickly turns into a nightmare. A model with 400 linked spreadsheets is fragile; one broken link can cause the entire structure to collapse. Debugging such an issue under pressure is nearly impossible, which is why spreadsheet consolidation is often criticized as being inefficient.

However, this chaotic scenario is just like our earlier example of scattered books. The problem isn’t Excel itself—the problem is a lack of a structured, central data store. Enter the librarian.

Implementing the Librarian in Excel

Now, let’s simplify our example and assume we have just 10 stores within a single department. Instead of managing separate spreadsheets, we create a central database on a shared drive. Here’s how it works:

1. Create a Central Database
   • On a shared departmental drive, right-click and create a new Access database. (If you have Excel, you likely have Access installed.)
   • Name it something like Budget.
2. Structure the Data
   • Inside this database, create a table named Budgets.
   • Define columns such as Shop ID, Account Code, January to December, Actual/Budget (A/B), Username, and Date/Time.
   • This structured table will store all budget data in an organized, predictable manner.
3. Upload Budgets to the Librarian
   • Each budget holder fills out their spreadsheet.
   • When ready, they click a button in their spreadsheet, which uploads their budget (28 rows per store) to the central database.
   • The librarian (database) now stores all the budgets in a structured manner.
4. Retrieving and Consolidating Data
   • Since all budgets are stored centrally, there’s no need to manually link spreadsheets.
   • A Get button in the Excel consolidation sheet queries the database and retrieves all budget data.
   • Instead of pulling numbers from individual spreadsheets, the Get button simply requests the sum of each month’s data across all stores.
   • This eliminates the need for external links, Power Query, dynamic arrays, or copy-pasting.

Why This Works

This system completely avoids the pitfalls of traditional spreadsheet consolidation:

• No external links that break unpredictably.
• No manual copy-pasting, which is error-prone and inefficient.
• No need for complex Power Query transformations.
• Data remains centrally stored, structured, and easily retrievable.
• It scales effortlessly—from 10 stores to 10,000 stores, without additional complexity.

The Bigger Picture: Excel’s Hidden Power

This approach contradicts a common myth spread by IT departments, ERP vendors, and cloud-based FP&A tools—that Excel cannot consolidate data. The reality is that Excel has always had the capability to store, retrieve, and aggregate data centrally using databases like Access or SQL Server.

The misconception stems from the fact that most people think of Excel as a standalone tool, when in fact, it was designed to work seamlessly with databases. Excel and Access are old friends—they speak the same language and can work together smoothly.

Beyond Access: Scaling to SQL Server and the Cloud

In this example, we used Access as the librarian because it’s readily available in most organizations. However, for larger implementations, we can store the database on a company-wide shared drive or even scale it to a global level using Microsoft SQL Server.

• If the database is on a corporate server, all departments within the company can access it.
• If the database is on the internet, spreadsheets worldwide can interact with it in real time.
• This enables a true enterprise-grade solution without the need for costly ERP systems.

Real-World Application: The Global Airbus Challenge

An example of this principle in action is the Excel Global Airbus Challenge, a fictional airline seat booking system that operates on a global scale. It demonstrates how Excel, combined with a centrally stored database, can be used for real-time collaboration across different locations.

Final Thoughts: The Librarian as the Key to Excel’s Future

The librarian concept is critical for understanding modern Excel data management. It highlights a bidirectional relationship between spreadsheets and databases:

• Excel uploads structured data to a central store.
• Excel retrieves structured data when needed.

This concept challenges the outdated view that Excel is only for retrieving data from databases. Instead, it demonstrates that Excel is a full-fledged data management tool when paired with a database.

Furthermore, once data is centrally stored, it can be:

• Secured with access rights (e.g., regional managers can only access their region’s data).
• Joined with other datasets to create a data model.
• Queried dynamically for different reporting structures (e.g., city, country, region).

Looking Ahead

In future discussions, we’ll explore how to expand on this concept:

• How data models work in enterprise scenarios.
• How to implement role-based access control to restrict data visibility.
• How to scale this architecture beyond departmental databases to cloud-based solutions.

For now, remember: the librarian solves the Excel consolidation problem effortlessly. Excel is not the problem—the problem is a lack of structured data management. And now, you know how to fix it.

This is a podcast by Hiran de Silva. Narrated by Charlie.