Contract Tracker: In Depth Guide for 2024

Excel as a contract tracker tool appears to be here to stay. Incredibly, 60 percent of federal contractors say that they use Excel to track their agreements (according to Baker Tilly) – which means that if you’re keeping track of contracts in Excel, you’re not alone.

We don’t recommend it for everybody, though. In the article below, we’ll explain why – but if you’re dead-set, we’ll also give you some tips and help for making it work.

We’ve designed this article to cover everything you’ll need to know (even though we don’t always recommend it!) – here’s what we’ll cover:

• When should a company use Excel for contract management?
• How to get started
• The shortfalls of Excel as a contract management tool
• How to set up an spreadsheet for managing contracts
• Free contract management Excel templates
  • Commercial templates to consider

Why contract tracking in Excel is a good solution for contract lifecycle management?

We recommend using a robust contract management tool when you can – but we accept that in some cases, Excel might just do the job.

We’ve seen an Excel contracting template work for:

• Tiny businesses, where the team size is smaller than ten and there isn’t a lot of job overlap
• Non-specialist employees working as contract managers who just want a solution in place quickly and can deal with additional overhead
• Businesses with low numbers of contracts, who just need a way to track under ten documents 

How to get started managing your contracts using Excel

When you’re setting up your spreadsheet system to manage your contracts, you’ll want to follow a process that looks something like this.

1) Identify key fields

Go through your existing contracts and tag key fields that you’ll want to track (e.g. the name of your client, the date the contract was signed, the date the contract expires, the type of contract, etc.).

Party names, terms of sale and any other clauses that are subject to more intense negotiation should be tagged, along with the manually input items.

TIME-SAVING TIP: For each key field, add them to a list in Excel. Then, when you reach step 5 below, copy your vertical list, right click in field A1, and click ‘Paste Transpose’. Voila! Your list will now be column headers – no rewriting!

2) Identify what you can auto-calculate in your spreadsheet

Where you can, for each tag you’ve identified, create a formula that will automatically calculate the relationships between different fields. For example, you can use a formula to calculate the date of expiration as the number of days after the signing date (e.g. if the contract is signed on January 1, 2015, the date of expiration would be 365 days later on January 1, 2016).

3) Identify what you’ll need to manually add

Where you can’t create a formula that automatically calculates your key fields, identify what you’ll need to manually add to a spreadsheet.

For example, if you’re tracking a contract value, you’ll need to manually add the value for each contract you enter into your spreadsheet.

4) Identify what you’ll need to manually update

Where you’re manually adding information into your spreadsheet, identify what you’ll need to manually update.

For example, if you’re manually tracking the contract value, you’ll need to know if it changes regularly and that you’ll have to go back and keep your spreadsheet current.

5) Set up your spreadsheet

Create a spreadsheet that includes your key fields per column.

Then, add the formulas for your auto-calculating fields. (For example, if you’re tracking the contract value, create a ‘Total Contract Value’ column that includes a formula to calculate the contract value as the number of months multiplied by the contract amount.)

For each field where you’re manually adding information,we recommend create a column color identifying ‘Manual Entry’. You should do the same thing for auto-calculated columns.

Key fields for your contract tracking spreadsheet

Common contract management fields you might want to consider including are:

• Client
• Contract Type
• Contract term
• Contract Value
• Date Signed
• Expiration Date
• Project Manager
• Project Name
• Contract Manager
• Date of First and Final Payment
• Total Number of Deliverables/Obligations
• Number of Deliverables Completed
• Number of Deliverables Pending
• Last Payment Date
• Number of Months Remaining
• Total Cost to Date
• Total Amount Paid to Date
• Total Amount Remaining
• Number of Days Remaining
• Status
• Internal Team Name or Contact
• External Team Name or Contact
• External Consultant
• Contract Expenses
• Project Expenses

6) Create your standard operating procedure for updating the contract spreadsheet

Once you’ve created your spreadsheet, create a standard operating procedure for ensuring that it’s up-to-date. This SOP should include information on how often you’ll review contracts and when you’ll make updates to the spreadsheet.

For example, you might decide that you’ll review all of your contracts on the first of every month and that you’ll make updates to your spreadsheet on the same day.

The SOP should also include information on how you’ll ensure that your spreadsheet is up-to-date. For example, you might require that every time a contract is signed, the contract manager will immediately add the contract into your spreadsheet.

You’re also going to want to specify how you’ll handle any changes to your contracts, such as if the contract value changes, if you add a new deliverable, or if a contract is cancelled.

Your SOP needs to be comprehensive – it’s a bit grim, but you should work using the ‘what if I’m hit by a bus?’ rule and ensure anybody in your organization can pick up your contract spreadsheet and use it!

Shortfalls of Excel Templates and Spreadsheets for Contract Management

Let’s not kid ourselves – spreadsheets aren’t perfect. More often, you’ll be better off with a high-quality contract tracker, even if it’s as simple as Contract Hound, which is really made for contract tracking. You might also consider a more fully-featured solution (check out our list here).

The fact is that spreadsheets contain errors. And a lot of them – up to 90% of spreadsheets contain errors, according to one study. Whether it’s costing financial institutions billions (as in this case) or delaying critical health reporting (as experienced by the NHS), spreadsheets are so powerful that they’re almost bound to go wrong at some point or another.

Which means, if contracts are critical for your organization, you might want to give Excel a miss.

Apart from being error-prone, there are other reasons to be careful when tracking contracts in Excel:

1) It’s difficult
Want to link to another document? Track expiry dates? Handle new revisions? All of these tasks don’t lend themselves to a simple spreadsheet – so before you know it, your simple Excel contract tracking spreadsheet has ballooned into macro hell. 

2) It’s not centralized
People can’t access each others’ work, and if they can, they’ll often end up overwriting it! Furthermore, it delegates responsibility for the spreadsheet to one core team member, incurring significant risk if that person can’t fulfil their responsibilities.

3) It’s not integrated
Want to send a contract out for signature via Docusign? Prepare for a whole new workflow, using different tools you’ll be trying to keep in sync with that trusty spreadsheet.

4) It’s a security risk
XLS files are just so… Portable. They’ll fit nicely in a thumb drive (easily left on a train) or as an email attachment, forwarded by an outgoing employee. Is it really worth the risk?

How to set up your Excel contract management template

Check out the section above for commonly used fields you might include.

This article is talking about Excel, but you could just as easily use a more modern cloud-based solution such as Google Sheets or Airtable. Both of these tools have the advantage of keeping in sync when multiple users are accessing the sheets.

When creating your contract management template, create a few sheets as part of your solution:

1. List contracts in one sheet
2. List organization contacts (company name, representative name, basic contact details etc etc) in another
3. List any internal picklists (for instance, team name or contract manager) in another

It should end up looking something like this:

You can hyperlink from contracts through to the organizational contacts sheet so you’ll always know who the go-to company is for a given contact.

TIP: If you want to get more complex with this, you can use Excel’s DATA functionality to turn the organization contacts sheet into a mini-database with more formal linking. Find out more on this page: https://exceltable.com/en/database-xml/creating-database-customers

Advanced Techniques for managing your contract with a spreadsheet

If you’ve got above-average Excel skills, you could try doing the following:

1) Auto-calculating expiry dates from start dates using date additions

Add another column which adds the term of the contract in days to the start date. Voila, there’s your expiry date!

2) Conditional Formatting for contract urgency

Using hidden fields and a variation of the trick above, you can calculate how many days there are left on the contract. =TODAY() will give you today’s date, which can then be subtracted from the expiry date to work out how many days are remaining on the contract.

Then, use conditional formatting to turn that cell (or the whole row) a certain colour if the contract is nearing its end term. For instance, you might create a rule which highlights the row red if the remaining days column is less than 30. And then another one which highlights it orange if it’s between 30 and 60.

This way, you’ll be able to see your most urgent contracts easily.

3) Read input data from other sheets

You can create another sheet with details of staff members, teams, or vendor lists, and then use validation rules to create dropdowns in your main contract spreadsheet. We’ve done this in our second downloadable example below.

4) Calculate total amounts by supplier or team using pivot tables

It’s quite common that contract values need to be summed. You can create a pivot table in Excel if you want to sum total contract values for one supplier, for instance.

5) Increase contract management security in Excel

Contracts are confidential, and although Excel isn’t the most secure way to store contract details, there are ways to make it better. For instance, you can lock down the sheets so that they’re unreadable by anyone without the password.

To do this, set a password on the file itself by clicking the ‘File’ menu and selecting ‘Passwords’.

For a more flexible way of protecting your contracts in Excel, you could lock down certain sheets or cells. You can access this option in ‘Tools’ and then ‘Protection’:

Here, you can stop users from adding new rows or columns, deleting contents or filtering in ways you don’t want. 

You can also designate only certain cells to be editable. This would be a great solution if you wanted to stop teams adding new vendors but still wanted them to add contracts for existing vendors, for instance. You’d just lock the whole sheet and then select certain cells as unlocked but make sure that those cells are reading from a Data Validation list.

Free Excel Contract Management Templates

We’ve created some templates which you can use.

All of these spreadsheet templates allow you to track contracts, expiry dates, due dates, period of time (contract term), status and some contact and obligation information. We’ve made them progressively more complex – the final option also includes some approval fields.

Simple Contract Management Template (download via Excel / Google Sheets):

Simple Contract Management Template with Organization Contacts Tracked (download via Excel / Google Sheets)

Simple Contract Management Template with Approvers Listed (download via Excel / Google Sheets):

We’ve also got some links to other options from around the web:

• A Central Contract Register

Commercial Excel Templates to Consider

If you’re not much of an Excel whiz, it’s possible to buy off-the-shelf spreadsheets which have embedded contract tracking functionality. Consider the following options:

Adnia Contract Management Template ($59)

• All your Contract Management data is contained in a single sheet.
• Nice graphics and charts to provide a data overview
• Dashboards which are time-based

This is one of our favorites – check out the overview video for more:

Download it from Adnia Solutions.

Luz Contract Management Template ($4.90/m)

This option is priced as a monthly subscription, which we don’t think is suitable for many companies (since it’s still an Excel template). However, it’s a neat-looking sheet, with basic dashboard functionality built-in.

Download it from Luz Templates.

Advanced Excel Contracts Manager ($44)

If you value your time, this might be a good option. It’s nothing too fancy, but it’s extremely affordable and includes some nifty macros which you probably wouldn’t want to put together yourself.

Download it from PME4U.

Why you should use a contract management solution like Contract Hound

Of course, if you’ve got a budget for contract management, we’d really recommend you try Contract Hound.

It’s really simple – more so than a spreadsheet. It can support multiple users easily (unlike Excel), and it offers significantly better version control, reminder and contract approval functionality than a spreadsheet.

We hope that this guide to contract tracking using a spreadsheet has been helpful, but honestly – do your organization a favor and try Contract Hound free today. You won’t regret it!