For most businesses, Excel will generally have a role to play at some point during contract and vendor lifecycle management. It’s one of the most accessible and widely used pieces of software in the world and well-suited to recording the kinds of data captured in contracts. In this article, we’ll provide you with some practical tips to apply to your contract tracking spreadsheets, as well as highlighting the pitfalls of creating a contract database in Excel. As a starting point, we've also developed some free Excel contract management templates to help you manage your contracts.
If your business currently has no dedicated place to store agreements, your contracts will be found in a variety of places. Whether it's in shared drives, emails, someone's head or a filing cabinet, you will ultimately face a common issues: finding contracts, and the right versions, will become a chore.
71% of businesses can’t locate at least 10% of their contracts - The Journal of Contract Management
A lack of structured contract tracking can lead to other consequences including increased contract risk, unwanted auto-renewals and business disruption. This disruption can take many guises, such as a fine for non-compliance or operational issues because a service has stopped being provided without your knowledge.
For any legal representative, operations executive or vendor manager who recognises these risks, Microsoft Excel offers a better defence than having no contract repository at all.
Effective contract tracking comes down to knowing your dates, so these need to be at the heart of your spreadsheet. This can include end dates, give notice dates and any renewal dates.
The first thing to do is set up a cell that will always have “today’s” date in - ie whenever you open the spreadsheet, this cell will be populated with the current date.
To do this, enter the following formula into your chosen cell: =today()
Now that you have this, you can use it to keep track of how long it is until specific milestones are up for each contract.
For each contract, you may have different key dates, but let’s assume you’re capturing at least “renewal date” and “termination date” in your spreadsheet. In the column next to these you can subtract the cell for “Today’s Date” from these contract dates.
Doing this will give you the number of days from now until that date.
The next step is to add conditional formatting (format/conditional formatting) so that you can tell at a glance whether contracts are near to renewal.
You may want to set your guidelines but you can use the following to start with:
Create a rule for the column that says if the number is greater than 30 but less than 90, to highlight the cell in orange. Add a further rule that says if it’s 30 or lower then highlight in red.
This way, your eye can be drawn directly to the most pressing contracts.
Top tip when entering dates - if you need to insert today’s date, you can simply press ctrl+; to populate it into a cell.
If it’s your responsibility to manage contracts for your business, you'll also want to consider the processes you have in place for version control.
Rather than having different versions, stored in different places, that anyone can change, you should lock down the data sheet as much as possible to ensure changes aren’t made without your permission.
The simplest way to achieve this with Excel is to set a password on the workbook. This can be found under Tools/Protection. Immediately, this will limit the ability for people to change the data in your document.
Next, you may also want to limit what data can be entered into specific fields if you’re going to be permitting other people to access the document. For example, using a free text field for contract type might lead to people entering multiple different versions of the same thing - eg “NDA” and “Non-Disclosure Agreement”. This will make it difficult later on to filter and monitor your data.
The solution is to use Data Validation and a specific list. Go to Data/Data Validation and then select to allow from a list. You can then enter your specific words and phrases, or select a list you’ve already created in the sheet, and then the only data that can be put into the cells will be from the list. This will ensure your data is consistent and can be filtered accurately.
You may also want to leave “Track Changes” enabled so that any time somebody updates fields then the changes are highlighted for you to accept.
"Assuming your actual contract documents are also hosted on a shared drive or similar, you can insert the links to them into your Excel document as well. This will ensure that there’s a clear line of sight to the relevant documents."
However, your system for storing contracts will also need to be robust and monitored closely to ensure that the current versions are always linked to and previous versions are retained for comparison.
Excel can be used as a basic resource for managing contracts. But some obvious limitations mean it's not a fit-for-purpose CLM solution for every business or even for all of the time. Excel is a great way to get started when it comes to contract tracking, but it is at the low end of contract management maturity.
The most crucial issue is that it generally relies on a specific person to build and maintain the sheet and be responsible for its integrity. They might delegate to colleagues to enter some of the data but that will need to be closely monitored to ensure accuracy.
This requires time and resources. It also increases the level of contract administration required, preventing teams or individuals from focusing on strategic contract management issues. If this individual goes on to leave the business, there will also be several knowledge gaps such as how the spreadsheet works, what formula is most important, how often it should be updated - the list goes on.
Even the password protection feature we mentioned earlier can cause issues. If only one person can access the database, the rest of the business can't collaborate on actions required ahead of upcoming dates.
Using Excel for contract management may bring some clarity to the business, but it doesn't completely resolve a lack of contract visibility.
Version control is another major problem with Excel. A password-protected sheet can protect the database itself in some ways - but not the actual agreements themselves. Version control relies on the most up-to-date agreement being linked to the spreadsheet.
If numerous stakeholders can access the database and make that change, how much confidence can you have that the link included is the most up-to-date or accurate?
By its very nature, contract management is a collaborative process with sign-offs and comments to be acknowledged. That's why a solution that can provide a complete and auditable versioning history - without it taking hours to search through - is a much better alternative to Excel spreadsheets. Staying in control of your contract management processes, both pre and post-signature, will put your business in a stronger position throughout each lifecycle stage. However, Excel lends itself to poor version control which can contribute to:
Microsoft Office 365 has addressed some of these collaboration issues and makes it easier to access documents concurrently and remotely. However, you still run the risk of someone overwriting previous work or breaking certain formatting.
Confidentiality is another issue when it comes to using Excel for managing contracts. A lot of agreements will contain sensitive data such as costs or salaries and will only be appropriate for certain people to view.
Linking to documents housed elsewhere, or including the information directly in the spreadsheet, can expose that information to an unnecessarily wide audience. Without different levels of access and the ability to restrict access by role, you risk breaching information security requirements.
Meta was recently fined €265 million over GDPR breaches - a sum that would bankrupt many companies .
Ultimately, managing contracts is a complex process, requiring a clear record of all agreements and then constant monitoring and updating. This will help to support compliance and governance.
If you have no central repository in place for your contracts, Microsoft Excel is a great place to start. I f you're looking to introduce something more robust into your business, watch our video below to see how an automated contract repository could help your business.
