A Pictorial Walkthrough On How To Use Excel For Law Firm Billing

May your 2015 be full of many months where you need to add more rows to your time entries on your bills.

I previously showed you how to use Excel to do a lot of neat tricks in your office. Here, I want to show some stuff that is a little more advanced. By way of background, I used to work at an insurance defense firm that split up. All of the attorneys went out and started their own firms. Pretty soon after the dust settled, everyone started asking my advice on what kind of billing software they should use. Some of them only had a couple of hourly clients and didn’t want to buy software to handle those few clients. Some of them had a lot of hourly clients, but did not know how to use billing software like Timeslips and did not have an Accounts Receivable person anymore. Regardless of their situation, they all wanted a way to create bills that looked just like the ones they had at the firm. So, I created a simple way for them to track their time each month and prepare bills that didn’t look like they were practicing law out of the back of a van. Because it’s the holiday season, I thought I’d share it with everyone.

Here is the download link for the file (or try this if that doesn’t work). Here’s how to use it.

Case Info Tab

There are several tabs along the bottom. If you click on the Case Info tab, you’ll see an area where you can input… case info. You put all of the header information for your bill here.

Time Entries

This is where you keep track of your time. If you have an insurance company client or any other client that makes you record your time with the ABA “A-codes” and “L-Codes,” you can enter those here too from the drop down list.

Expenses

Sponsored

Enter the date, description, and amount of your expense.

Bill

You don’t enter anything on this tab. All of the cells here reference the information on the other tabs. This is your finished product. You might have to tweak it a little to get the format right for your cases and clients. You’ll notice that the information from the Case Info tab is in the header of your bill and your time entry information is at the bottom. If you click in the cells, you’ll notice that instead of showing data, it shows where that data is found in the spreadsheet. Here, I have selected cell B28 and the contents of that cell show me that cell B28 gets that A101 data from the Time Entries tab cell C2.

If there are zeros showing up, that means that the cell is looking for information in a cell that is blank. I put a bunch of extra rows in there because it’s easier to delete rows there to add them.

Analysis by Day

Sponsored

The tab lets you see how many hours you have billed in a day. If you have 3 entries for December 5, 2014, for 4 hours each, it will show 12 hours for that day. This information is not self-updating. You have to click in the Pivot Table Analyze tab and click refresh.

Fine Tuning

Take a look at that screenshot of the Bill page. Go underneath where it says “Central Division” and delete those zeros. If you don’t want the extra space, select the whole rows and delete the rows. If you accidentally delete something that you wanted, it’s easy to restore. Say I accidentally deleted the date from my first time entry (12/1/2014) on the Bill page. I press the equals sign and then go to the tab I want and click on the cell I want to be referenced and press enter. That creates a reference to another cell in a different tab.

Say you need add more rows to your Bill tab. Go to the bottom of the part where your time entries are listed and select Insert Row. You should have a bunch of blank rows now. Copy the whole row, from Date to Amount, where the references are intact and paste that info into the empty row you just created. It should populate with info from the Time Entries tab.

One of the biggest problems with this method is that if you have text that takes up more than one line of text, you have to manually set the row size. Here, I’ve got text in row 32 and you can see that the last sentence is cut off.

To fix this, just go to the Bill tab and put your cursor over the row number that you want to enlarge. Your cursor changes to a double-sided arrow. Drag the row height to make sure the info fits like so:

You’ll also notice that I have a typo here and there are no red squiggly lines under the typo like there would be in Microsoft Word. You have to manually check for typos with the spell checker. Here, run spell checker in the Time entries tab to correct this typo.

Say you want to have different information in that A-code or L-code section. If you scroll down on the Time Entries tab, you’ll notice that the row numbers go from 102 and then jump to 152. That’s because those rows are hidden.To unhide, I just select the rows around the hidden rows, right click and go to Unhide.

To create the drop down, use the Data Validation tool. Clicking on that brings up a window. You can see here that I’ve chosen List and told it where to get the contents of that drop down list. Here, I told Excel to build the list contents from A116-A149. Once I have defined that list, I can copy and paste that drop down list into other cells.

So that’s it. Shoot me an e-mail at the address below and let me know if you have any problems with any of this. Happy New Year. May your 2015 be full of many months where you need to add more rows to your time entries on your bills.


Jeff Bennion is a solo practitioner from San Diego. When not handling his own cases, he’s consulting lawyers on how to use technology to not be boring in trial or managing e-discovery projects in mass torts/complex litigation cases. If you want to be disappointed in a lack of posts, you can follow him on Twitter or on Facebook. If you have any ideas of things you want him to cover, email Jeff at jeff@trial.technology.

CRM Banner