Excel for In-House Counsels

9 min readJun 13, 2021

This post was inspired by Yeo Bee Yin’s tweet on Excel and the resulting replies by a few of my friends who are in the legal field:

Out of my 9 published posts here on Medium, 4 of them has touched on Excel.

(Only 9 published?! Feels a whole lot more than that. *checks draft folder* Ahh, I know why.)

I have a very keen interest in Excel that is bordering on passion. I used to read Excel blogs during my LRT commute to work and it never failed to rev up my engines. I can pinpoint two earlier instances during which Excel left an impact on me:

Attending an ‘Intermediate Excel’ course with a bunch of my colleagues (who weren’t from the Legal department) and learning about all the formulas and functions that I could use. I was nowhere near intermediate level, but attending felt so ‘WOW!’.
Attending a night meeting where a senior management person was firing questions about this and that, and the person in the crosshairs was just calmly pulling up numbers using pivot tables to answer.

So for this post, I’d like to share some use cases for Excel for an in-house counsel. I think it would be fun to see how relevant Excel can be in the context of the Legal department’s work.

Why Excel and not Word?

Wait, before that. Why am I recommending Excel and not Word for these use cases?

If you skimmed through the post, you’d have seen that some of these use cases just look like they’re words in tables. Which Word can do.

But what Word can’t do is perform mathematical functions on the information. If you need to count how many contracts in a Word table, you’d have to do it manually. But Excel can count FOR you. There’s a formula for that.

If you need to set a deadline for something, you’d need to manually count the days in your calendar and exclude weekends and public holidays. But Excel can count FOR you. There’s a formula for that.

Excel is excellent for managing legal work.

1) Track and analyse incoming work requests for the Legal department

Apart from using a project management app or software, you can also use Excel to track incoming work requests for the Legal department nice and easy. You can also add other columns like:

Aging: Calculate how many days has lapsed since the request date. (Working days — important distinction!)
Completion Status: Track if the status is Ongoing, KIV, Completed.
Urgency: Label and track requests based on their level of urgency, which may have different SLAs.

2) Track and analyse a specific type of work request

If you want to drill down further into a specific type of work request, you can have a specific Excel sheet or workbook on that work request alone, e.g. contracts in the screencap above.

This is particularly useful if you’re in charge of certain work requests a.k.a. your portfolios. Based on the industry you’re in or what your company sells, you can even divide them according to the following:

Revenue vs. operational: Does the contract relate to the revenue of the company or to the operational parts?
Customer lifecycle stage: At which point of the customer lifecycle is the contract used? For example, for financing, I divided the documentation into Pre-Disbursement and Post-Disbursement.

From the data that you capture, you’ll be able to see which type of contract is in desperate need for further strategizing. For example, if IT keeps coming to you with all sorts of contracts from their vendors, it may be worthwhile to sit down and develop templates specifically for IT, even if their contracts may not relate to revenue.

3) Manage a document rectification project

Let’s say you’ve been assigned to handle a rectification project. Basically, you need to go back and search through all past documentation, and see if it meets a certain criteria. If it does, hooray. If it doesn’t, it’ll be your job to rectify it. I call this the ‘fixer-upper project’.

The first step towards rectification is identifying what is not enough in the first place. So something like the screencap above would help you check each document systematically. There’s nothing worse than getting to no. 100 on your list, and realizing that you didn’t note down a critical piece of information for 50% of the list.

Once you have the data, it will be far easier for you to propose measures to rectify the problem to your boss.

4) Track and analyse incidents/problems/cases

The screencap is an example of tracking instances where the contract was returned after the deadline by the business unit to the Legal department.

This is similar to no. 3 above but is more geared towards collecting data from a specific type of incident/problem/case. If something is recurring enough that you need to do something about it, having an Excel tracker to analyze the situation is really helpful — especially when you need to convince your boss or management to accept your proposed solutions.

You can track:

Internal issues: Issues that come up during interactions with other departments.
External issues: Issues that come up during interactions with external parties, like panel solicitors, vendors or customers.

Going with my example above, it’s best to list down the common reasons for the late return of the contracts. E.g. Reason A, reason B, reason C, other reason. Then use the data validation function to create a drop-down list for the reasons. So whenever you want to enter an incident, you pick from A, B, C or other. This makes the data much easier to sort through compared to writing the reasons down randomly.

5) Basic contract management system

A professional contract management system is ideal but can be hard to realize considering budget concerns, buy-in from management and the time needed to onboard everything and everyone. But you can make do with Excel.

Excel allows you to have an overview of the things that you need to monitor, like renewal dates and deadlines. You can make full use of date- and time-related formulas to make your life much easier — no manual counting please!

And if looking at a bunch of numbers makes your face go (@_@), you can even apply conditional formatting to your Excel so that you can identify critical deadlines much better.

6) Manage list of panel solicitors

Using Excel to create some sort of “Solicitor Management Dashboard” is also a very worthwhile project, especially if you have a considerable panel size and are required to report to management on their performance.

Some ideas that you can implement:

Basic list that has all the contact details of the firm: Excellent if you have to regularly send out bulk letters or emails to your panel, especially when combined with the mail-merge function!
Total numbers of cases assigned to a specific firm: Without complicating this post, the idea is to use the COUNTIF formula to automatically count how many files have been assigned to each firm. This data can be displayed next to each firm on your ‘dashboard’ so that you’ll be able to see at a glance how active a particular firm has been. (You will need a separate Excel tracker on your assigned files to achieve this)
Solicitor performance review: Going off from the example above, you can also have columns with the performance review of each firm. E.g. Case A — Messrs. ABC — score 95/100. Then at the end of the year, you can average out the scores based on the number of files assigned.

7) Create calculators (for other departments)

If you need to help other departments calculate something like stamp duty, a calculator is a great tool to give them. Heck, you can even make a calculator for deadlines!

But first, you will need to build it with the formulas. I remember building one for the business department to use. It took a couple of rounds to make sure the formulas were working perfectly. If you got it wrong, it’s only going to make other departments more confused, so it’s best to test and validate as much as possible.

It’s also good to write out some guidelines for other people to use so that they don’t enter the wrong values into the calculator. Simple tips like “Key in the number of copies you want to make” will go a long way towards making your calculator user-friendly.

Don’t forget to password protect your Excel sheet (except for the relevant cells that people will need to key in values) so that no one can mess with your formulas!

3 Golden Tips

I hope I’ve been able to share with you use cases of Excel that will make your life easier in the long run. Sure, some of them takes time to build and Excel has a learning curve, but the output will allow you to inject strategy into legal work, rather than just act the fireman and extinguish fires all the time.

I have 3 Golden Tips that form the basis of my Excel trackers:

Excel does the math, you do the reasoning. Take it from someone who wasn’t/isn’t good at Maths, what you need to do is apply logic and structure to the data you want to collect. Excel will help you with the calculations part. High school mathematical reasoning will help you here. “If this, then that…” and that sort of thing.
Categorize, categorize, categorize. For something as subjective as law, you need to be able to categorize matters to be able to analyze them. Spend time going through your work and see how you can categorize them. E.g. contracts. From there, categorize further: revenue contracts, non-revenue contracts. Further! Non-revenue contracts: HR, IT, training.
All the data you need belong in their own columns. You can have up to 16,384 columns on Excel. Yes, I just googled that. That means that you need to give each piece of information their own real estate! As much as possible, break down information into small pieces. And ask yourself, “If I need to know how many …., can I filter this information?” — if the answer is no, you need to make more columns.

Because that last Golden Tip is the most important tip in my opinion, I’m gonna leave this screencap here as a parting shot.