Is data stored in Excel undermining your ability to provide legal advice? Know when it’s time to move on to a database solution.

As an in-house lawyer overseeing contract compliance for your company, you know the pain of producing a report from your data stored in Excel. You fire up the 15MB spreadsheet and wait 60 seconds for it to load. Your heart sinks as you realize that your legal support staff has accidentally overwritten the version on the shared drive with an old version, deleting all of the contracts from last month.

Which file has the data I want?

In order to replace that data, you can reconstruct it from another department’s Excel sheet, but the person in that department who has access is is away on vacation for the next week. You put in a ticket to IT to request permissions to their shared folder, and they report a 1 hour turnaround.

Your boss casually strolls by and says the deadline for the report has moved up to tomorrow morning in order to support negotiations to close a $10 million licensing deal. It would be more cost effective to have your support staff manually piece this information together instead of paying a lawyer to do it, but you’re the only one who understands the data.

You know that you could give much stronger legal guidance if you had clean, reliable data. Instead you’re looking at a long night of copying and pasting to hack together a shaky foundation of suspect data on which you’re supposed to build your legal opinion. You could start your opinion with a caveat on the quality of the data, but you’re a lawyer — you want to provide solutions, not excuses.

Does this sound familiar?

When you managed 100 contracts, you felt like a genius for getting the paper contracts out of the filing cabinet and into the computer. But as your company has grown, you haven’t been able to scale your ability to handle the volume. Excel used to be enough, but some time ago it started becoming unwieldy and you don’t know what to do about it.

Your needs have grown and it’s time for you to move your data into a database.

Storing your information in a database will give you the confidence that the data you are working with is the sole source of truth. It will free you from having to be the sole conduit of access to the data. And it will provide the informational foundation enabling you to provide legal service at scale.

How a database fixes 3 common Excel problems

Stop manually resolving duplication conflicts

You’re storing all of your contractor data in a spreadsheet and business is good. So good that duplicate entries have started to appear. You thought it was funny when you had two contractors named Pierre Tremblay, but now you have the problem of how to distinguish the two. You could use email addresses, but you know they aren’t really unique and you know they change over time. A database addresses this by automatically assigning a unique identifier to manage each Pierre Tremblay.

Each entry is now referenced by a unique id.

Get clean, actionable output from your queries

Your spreadsheet started out with one email address per contractor, but as your contractors grew they added support staff and you needed to track their email addresses too. You created a second column to store the second email addresses, but every time you export the contact email list you need to manually tweak all of the entries with two email addresses. This was fine when you had 20 contractors, but you have 1000 now and you can’t keep up.

A database solves this by moving all of the email addresses to a second table of emails.

Contractors can be referenced by their unique ids …

… and Emails can be referenced by their contractor id.

Each email has a unique primary key, but in addition, it has a foreign key equal to the contractor’s primary key. That’s how each unique email address is linked to a specific contractor. When it comes time to generate an email list, your database query returns nice, clean data that your email delivery software is able to use without requiring your manual intervention.

So a query can produce a clean mapping of emails to contractor names.

Have confidence in the validity of your data

You’ve realized that you need to have better visibility into your contractors’ employees in order to monitor their contractual compliance. You created a second spreadsheet to store all of the employees and have referenced them to the contractor by name. But every time you try to sort and filter the contractors, you discover that the contractor names all have various spellings and you spend hours making them all the same each time you create a report. You’re on the right track by tracking these employees in a second spreadsheet and creating a reference to their parent contractor. However, you’re failing because you are using the name of the contractor as the key to manually manage the relationship between the two spreadsheets. A database does this for you automatically, without typos.

It looks like there are 3 different roofing companies in your table, but you know these are all supposed to be the same entity.

But what exactly is a database?

You probably have some idea of a database being a place where you store information. But as the above story with Excel illustrates, storing the information is not enough. You need be able to:

1. Query your data at scale and not have wait 60 seconds for your data to load because you have too many rows.
2. Share simultaneous access with your legal team so you can both access and edit the data at the same time. Nuff said.
3. Validate the accuracy of your data being entered so that missing fields are not permitted and entered data is of the correct type. Databases automatically check this for you so you can trust the accuracy of your data.
4. Secure your data against unauthorized access. As legal counsel, you’re entrusted at the highest level of confidentiality and if a shared Excel file gets out, you are responsible.
5. De-duplicate your data so that the information is consistent and trustworthy. Resolving data conflicts caused by duplication doesn’t provide business value, it just takes away time that you could be spending providing legal service.
6. Understand the importance of your data. Your data is your competitive advantage, if you know how to use it. Collect it, sort it, and protect it.

A database does this by providing structure to the way your data stored and defining relationships between related data.

The two signs that it’s time to break up with Excel

If you can’t stand your queries being slow or having to manually validate your data, then it’s obvious that you should move to a database now.

But what if you’re not sure if now is the time? Or maybe you’re starting a new set of data and you’re not sure whether to start with Excel or go directly for a database?

As a lawyer, you’re an expert at identifying legal issues, even if you don’t know the answer. But how will you identify the technical issue of whether the right tool is Excel or a database?

Just remember these two principles:

1. When you want to put two pieces of data into one field (like two email addresses), you need another table.
2. When you want to create a second field to hold a second piece of the same type of data (like email address 1 and email address 2), you need to create another table.

When you need more than one table, it’s time to dump Excel for a database. That’s it!

Ultimately, the quality of your legal advice give rests on the quality of the information you have before you. The stronger your confidence is in the information, the stronger your legal opinion is going to be. As an in-house lawyer, you are always seeking competitive advantage. And if you aren’t fully using all the tools at your disposal, you’re leaving that advantage at the table.

The only question now is, what insights can your legal department provide once freed from manually tweaking your Excel data and you have all the tools of data science available to you?

Over the last 10 years, Andrew has held roles in law, marketing, and technology. Currently he’s building projects in Node.js, Express and React as a technical foundation for his next role. You can follow his progress at http://github.com/sockbot and http://linkedin.com/in/ajchan.