Blog: 6 Considerations for Using Excel as an Experience Database

6 Considerations for NOT Using Excel as a Deal or Experience Database

Author: Jamie Addison

Over 750 million computers have Excel installed. As such, it is often the go-to option for any data-related project. It is pervasive, but if you are using Excel as a cure-all for every business situation that requires data structure, you may be adding to your business intelligence problem rather than addressing it. This is especially the case for law firms, investment banks and advisory firms specializing in high value and sensitive client information.  

Excel is an excellent tool for many specific functions. I love a good Excel formula, and even enjoy knowing I have a trusted friend in Google whenever I hit a bump in getting my formula just right. However, as with anything, it’s vital to understand when it’s appropriate to use a spreadsheet tool, such as Excel, or a database solution.

Understanding and setting clear objectives will help you determine when a spreadsheet, database, or both is the right solution. This will help ensure you achieve optimal results.

Define Your Objectives with These 6 Considerations

1. Independence or Collaboration

Are you working with a set of data points that require minimal contribution from other team members or systems? Are you the primary person responsible for managing the data points – such as gathering, contributing, manipulating, distributing and storing the data?

If you answered yes, a spreadsheet may be an appropriate solution for your project. As the primary point of authority, you are able to manipulate and store data based on your individual preferences. You are also able to disseminate that information to others.

However, if you answered no to one or both questions, a database may serve your long-term needs more appropriately. When data is being contributed by members across one or more workgroups, or even sourced from one or more systems across your business enterprise, using a database to store and manipulate the data provides more value to the entire organization.

Databases generally have the bandwidth to handle numerous data sources simultaneously. It ensures all stakeholders are presented with updates in real time, eliminating the universal concern of version control. This often leads to increased productivity as teams can progress with processes and business decisions without the need to wait for the latest version of a single document.

PRO-TIP: If your workgroup(s) have a “Master” copy that no one trusts is it really the most up-to-date version? You need a database.

2. Static Access or Multi-Level Permissions

Building on the prior point, the next consideration is if the same level of information should be shared with all stakeholders? Or, are there certain data points that should not be viewable and/or editable by certain users or work groups? 

If multi-level user permissions are critical to the integrity of your data, you’ll find a database to be more suitable. Permission-based databases allow you to control the authority level of each user or team that is granted access. This ensures sensitive information is protected from unauthorized users. 

Additionally, databases often allow for system administrators to control what actions can be taken by users, which buffers against data being changed or overwritten in error. The security of sensitive data is critical, so being able to control who can access and edit information, as well as having a real-time audit trail of who made changes and when those changes took place, are benefits often realized through the use of a database.

PRO-TIP: Do you often create a second copy of your Excel file to share with other team members? Then, yes. You need a database where power users can easily create permission settings based on job or departmental functions.

3. Data Consistency is Important

Ever tried filtering a spreadsheet only to find the column contains 10 variations of the same option only spelled and/or phrased differently? Or maybe you need to sort a column containing numerical values only to find a few people decided to write notes instead?

If these types of issues prove burdensome to your workflows, a database would provide increased control and buffer against the dreaded field creep. A customizable database allows you to restrict users to entering only the type of data requested, thus keeping your data consistent, relevant, and more importantly, usable.

4. Formatting and Character Flexibility

Do you find yourself in need of additional formatting options when entering commentary associated with a data point? Do you frequently exceed the allowed character limit and merge multiple cells to compensate for this? If so, these are also good indicators a database solution may be more suitable.

Spreadsheets aren’t intrinsically designed as text-based editors, so the flexibility and options available are limited in scope. However, modern databases often include dynamic text editing tools such as style and spacing accommodations, as well as built-in review tools such as spell check. Additionally, databases offer flexibility related to character restrictions eliminating the need for time-consuming workarounds.

5. Data Automation and Transformation

Is it important to sync and consolidate data across multiple platforms and source systems in order to create a complete analysis? Is transforming data into polished presentations, reports, or graphics a burdensome and inconsistent process? A database capable of integrating across data sources would likely be a more efficient solution.

While some spreadsheets have macro functionalities that allow data to be pulled and pushed to a database, often these require manual triggers that may not be scalable and consistent. Using an integrated database automates data collections across platforms in real-time ensuring data integrity and scalability.

The need to transform consolidated data in a meaningful and usable way is a growing concern. As such, databases are being structured to accommodate dynamic layers, including applications that automatically connect data to end-point solutions such as marketing collateral and client presentations at scale.

During my time on the Business Development team of a law firm, I spent a lot of time comparing data in a master spreadsheet to tombstones being created and maintained in InDesign. There was always a level of anxiety associated with ensuring each data point was just right, all tombstones were sorted correctly, and no deals were duplicated (which is difficult when there are instances where a small detail can set a series of deals apart). Having a database solution that connected the actual data to the designed product (i.e., tombstones) would have done wonders for my team’s productivity.

6. Storage and Access

This again ties into the first line of questioning – are you working independently or as part of a larger team. Shared drives and document management systems are commonplace across many organizations and, in most instances, are 100 percent appropriate solutions. However, controlling the accessibility of a group of documents and a group of data points should not be treated as one in the same.

Maintaining a single source of truth for a document across a team of five could prove challenging, as you’d need to determine who can access the file, who can make changes to the contents of the file, determine if changes can be accepted by all users in real time or if updates can be made by one user at a time.

You also risk exposure to another topic we’ve hit on previously – version control, as users could potentially save files in different locations across the drive. If this is the case with just five users, imagine the impact with hundreds or thousands of users across an organization. Data storage and accessibility issues can be solved with most progressive database solutions. Creating a single source of truth with built-in, multi-level permissions would prove invaluable. Additionally, some databases also allow enterprise level credentialing that grants access to users across your organization in a single-sign-on fashion.

Know Your Objective, Find Your Answer

The key to success with either solution is understanding you and your organization’s objectives. Spreadsheets and databases are not mutually exclusive tools. In fact, they can be used in a complementary fashion, but also independently based on your unique set of needs.