Generate Properly-formatted Bates Numbers in a Snap

Let’s say you get the following panicked email from an attorney on your team:

Bates-email2

You understand that these are the Bates numbers for documents on the Big Bank case, but you need to convert them to the proper format in order to pull them out of Relativity all at once.  Viz.,

62 refers to BigBank_000062

7550 refers to BigBank_007550

and so on.  The tricky part of this is adding the “BigBank_” prefix while also getting the right number of padding zeroes into each number.  You certainly don’t want to type them out one at a time–right?  Excel’s TEXT function makes this easy.

First, cut and paste those numbers into Excel and put the following formula in cell B2:

Bates-formula

which generates the following result:

Bates-firstresult

Here’s how the formula works:  “BigBank_” is the prefix for each Bates number, and the TEXT formula with the “000000” argument tells Excel to convert the number in Column A to text and add the appropriate number of padding zeroes to get a total of 6 digits.  The ampersand (&) combines both pieces into a single string of text.

Next, copy the formula by finding the cross-hairs in the lower right-hand corner of cell B2 and double-clicking, or dragging them down:

Bates-copyformula

The copied formulas turn all of the raw numbers into properly-formatted Bates numbers:

Bates-allresults

That’s it!  As a housekeeping matter, it’s a good idea to get rid of the underlying formulas by copying the cells and pasting the values, like so:

First select the cells with the formulas/results, and then right click and choose Copy (or hit Ctrl+C)

Bates-copy

Then select the same range of cells and paste the values right on top of them:

Bates-psv

The cells will appear the same, but now the content of each cell is the resulting Bates number, rather than that funny formula–as reflected in the formula bar.

Bates-nomoreformula

Now these Bates numbers can be pasted into Relativity or other document review platform.  Of course, this trick will work if you have 8 numbers or 800.

Thanks for reading.  If you have a thorny Excel issue, please share it in the comments below.  To learn about hosting a CLE-approved Excel workshop at your law firm, company, public interest organization, or bar association, click here or email Ben Kusmin from Excel Esquire.

Posted in CLE, eDiscovery, formulas, Lit Support | Tagged , , , | 3 Comments

Analyze Matter Data with Pivot Tables

In this post I’ll explain how to use one of the most powerful data analysis tools in Excel – the Pivot Table.  My example involves a list of new pro bono matters, but pivot tables are also an effective way to analyze email metadata or any other large data set.

Suppose your firm signed up 140 new pro bono clients across nine different programs in the last quarter of 2014. Each matter is represented in a row of the spreadsheet shown below. For each new matter, there are fields for the program, responsible attorney, date opened, and several other data points.

PT1

Now suppose you want to know how many new matters were generated in each program, or which program had the highest average cost in this quarter, or see a breakdown of new matters by borough? By month opened? By advocate? You can answer all these questions with a Pivot Table.  Keep reading to learn how.

You can download the Excel file by clicking here.

First, for Pivot Tables to work properly, you should make sure your data is the form of a table. That means no blank rows or blank columns in the data you want to sort.

To insert a pivot table, start by clicking anywhere in your table. Then navigate to the Insert ribbon and select PivotTable on the far left:

PT2

By default, Excel selects all the contiguous data for inclusion in the Pivot Table, and places the table on a new worksheet. You will usually want to accept these default settings:

PT3

Excel takes you to the new worksheet where the Pivot Table has been created. All you see at first is this intimidating diagram:

PT4

The first thing we’ll do is tally the number of clients in each program. Drag the “Program” field from the list into the Row Labels field and into the Values field.

PT5

Like magic, Excel tallies the number of clients in each program, as well as the grand total:

PT6

Now you can get a breakdown of the programs by any given category. Let’s see how many of the clients from each program are from any given borough. The trick is to visualize how you want the data to look and anticipate what the Row and Column headers will be.

Since we want a separate column for each Borough, we drag the Borough field into the Column Labels area.

PT7

Voilà:

PT8

NB: You can change the appearance of the headers by choosing Design –> Report Layout –> Show in Tabular Form

You can even drill down on a certain data point to see which records it includes.

At the intersection of Queens and Bankruptcy Clinic there is a 5, indicating that there are 5 clients in the Bankruptcy Clinic from Queens. Double-click on the number 5 and something neat happens:

PT9

Excel copies those 5 records and pastes them into a new worksheet! It’s a good idea to rename the worksheet to remind yourself what this data is. Double-click on the tab name, type the new name, and hit Enter.

Group by Date

Now let’s generate a breakdown of all the new matters by month.

The first step is to pull the dates into the Column area and see a breakdown by date.

PT10

This is not very useful yet, because it creates a breakdown for every single day. To collapse the breakdown to months, click on one of the dates and then select Group Field from the Options ribbon:

PT11

Choose Months and Years and hit OK

PT12

Now the Pivot Table will break down the count by Month:

PT13

Tally the expense subtotals by program

Remove the Date field from the Column area and drag the Expense field to the Values area:

After formatting these values as USD, you will see the subtotal of expenses for each program, along with the number of clients.

PT14

NB: You can format all the sum and average cells as US Currency by selecting them and then hitting Ctrl+Shift+$

Calculate the Average Cost per Client in Each Program

Now let’s look at the average cost per client for each program. The first, counterintuitive, step is to drag the Expenses field into the Values field again.

PT15

Now you’ll have two columns in the pivot table representing the sum of the expenses for each program:

PT16

Double-click on the Header, the cell that reads Sum of Expenses2, and you will see the option to change the value to an average:

PT17

Now you’ve got the Total in one column and the Average in the other:

PT18

You can also sort these Pivot Table results. If you wanted to view the above table by most expensive to least expensive program, you would click in the Sum of Expenses column and click the Z–>A button on the Options ribbon (you should be there by default):

PT19

With this result:

PT20

To make the Pivot Table more presentable, you can get rid of the Row Labels title by hitting the Field Headers button, and edit the other column headers by adding your own titles:

PT21

One last thing to remember about Pivot Tables: they do not update automatically if you add, delete, or edit the underlying data. If you want the Pivot Table to update, Go to the Options tab and hit the Refresh button.

PT22

These techniques are covered in-depth in my new CLE course† Excel Essentials for Busy Lawyers. To learn more about hosting a session of Excel Essentials at your law firm, company, or public interest organization, please email Ben Kusmin at Excel Esquire.

†The required fine print: Approved in New York for 2.0 CLE credits; approved for both new and experienced attorneys; financial aid available.
Posted in Uncategorized | 2 Comments

Reverse Names Easily with Find & Replace

A student in one of my CLE programs recently asked me the following question:  What do you do when you have a long list of names of the form “Cook, Mary” and you want to put them in the form “Mary Cook”?  This issue comes up so often I decided to write a post about it.

Suppose you have all those pesky names in a single column:

reversenames1 

Although there are text functions that will extract the various parts of the name field, a much more straightforward way is to use wildcards within Excel’s Find & Replace feature to quickly isolate the first names and last names, and then recombine them.  Here’s how it works:

Step 1 is to copy the entire column of names into 2 helper columns:

 reversenames2

Next, select Column B and hit Ctrl+H to launch the Find & Replace dialog box.  Tell Excel to replace ‘*, ’ with nothing:

reversenames3

This deletes everything up to and including the comma and the space after it, leaving just the first name:

reversenames4b

Next, select Column C and hit Ctrl+H again.  This time replace  ‘, *’ (that’s comma space asterisk) with nothing.

reversenames5

This will delete everything from the comma onwards, isolating the last names:

 reversenames6 

Now you can combine the first and last names with this simple formula:

 reversenames7

Copy the formula all the way down to recombine the rest of the names:

 reversenames8

There you have it!  You’ll probably want to replace all these formulas with the resulting text values.  Do this by Copying Column D and then pasting back on top of it with the Paste-Special-Values button:

PSV

Thanks for reading.  If you have a thorny Excel formatting issue, please share it in the comments below.  To learn about hosting a CLE-approved Excel workshop at your law firm, company, public interest organization, or bar association, click here or email Ben Kusmin from Excel Esquire.

Posted in CLE, eDiscovery, Everybody, Lit Support, Practice Management | Tagged , , | 5 Comments

Reviewing Native Excel Files, Part 1: Detecting Inconsistent Formulas

This is the first in a series of posts about reviewing native Excel files produced by parties in litigation.  We’ve finally reached a tipping point in litigation where the production of native Excel files (rather than inscrutable thousand-page printouts) is the rule rather than the exception.  Discovery stipulations now routinely contain a provision that calls for Excel files to be produced natively (does yours?), and Magistrate Judge Facciola famously observed that tiffing out electronic documents such as spreadsheets is “madness” (Covad Commc’ns. Co. v. Revonet, Inc., 2009 WL 2595257 (D.D.C. Aug. 25, 2009)).  The question for practicing lawyers today is how to review those files, and how to exploit the wealth of information they often contain.

Today we look at Excel’s built-in feature that flags inconsistent formulas, and see how that feature can call attention to potentially critical information lurking beneath the surface.

Suppose your client is a plaintiff in an employment discrimination lawsuit.  The defendant produces a spreadsheet that reflects sales commissions for a certain set of employees.

InconsistentFormula-table

Clicking on (or “selecting”) one of the cells containing the commission figure reveals that it is generated by a formula, which appears in the Formula Bar:

InconsistentFormula-formula

The formula =C2*0.08 means that the commission is 8% of the salesperson’s sales.  Clicking down it looks like each commission is calculated the same way:

InconsistentFormula-formula3

Now observe that a few of the commission cells have small green triangles in the upper left-hand corner:

InconsistentFormula-triangles

This is Excel’s way of telling you that the formula underneath this cell is not like the others–i.e., it is not 8% of the sales figure in the same row.  Click on one:

InconsistentFormula-formula2

Lo and behold, this lucky individual is getting a commission equal to 15% of his sales.  Some deposition witness will have some explaining to do!

Note that you can get Excel to display all the formulas in a worksheet (rather than the values they generate) by hitting Ctrl+` (that’s next to the 1 key)(when you’re done hit Ctrl+` again to toggle back to the normal display) .  Now you can quickly survey all the formulas for aberrations, and perhaps reveal some formulas that you didn’t know were in the spreadsheet.  Here we see that all three of the cells with the green triangles are using the 15% commission rate:

InconsistentFormula-DisplayFormulas

Given the way formulas are created in Excel, it is unlikely that these were typos or innocent mistakes–much more likely that someone deliberately changed these three formulas from 8% to 15%.  What if you were reviewing a tiff image of this spreadsheet, rather than the native file?  You would never even know how the commissions were calculated, much less that certain employees were getting special treatment.  Now you know those little green triangles may be a big red flag!

Thanks for reading.  If you have tales of Excel sleuthing, please share them in the comments below.  To learn about hosting a CLE-approved Excel workshop at your law firm, company, public interest organization, or bar association, click here or email Ben Kusmin from Excel Esquire.

Posted in eDiscovery, formulas, Lit Support, Native File Review | Tagged , , , , | 6 Comments

Print a Rough Deposition Transcript in Half the Pages

The court reporter just emailed you the rough transcript of today’s deposition, and you’ve absolutely, positively, got to read it on the train tonight.  The idea of reading it off a tablet makes your head hurt, but printing out that .txt file is a terrible waste of paper, and no fun to look at either:

Dummyroughtxtprint_p1 Dummyroughtxtprint_p2

Look at all that wasted space — Yuck!  This example prints out to 141 pages.  Spend a few minutes scrunching this down in Excel and you can save a whole bunch of trees!

This technique basically consists of copying and pasting the entire transcript into Excel, and then deleting all the blank space.  You can reduce the print size even further by deleting certain useless lines and adjusting the print formatting.  Here goes:

Step 1.  Copy and paste the whole transcript into Excel

From the open txt file, hit Ctrl+A to select everything, then Ctrl+C to copy it all to the clipboard.

In a new Excel file, select cell A1 and Paste (Ctrl+V).  Make the column wide enough to accommodate all the text.

roughtranscript-blankspace

You’ll notice that all of the blank space now consists of discrete rows, which can be deleted en masse.

Step 2. Delete the blank rows

Here is where the magic happens. Click on the ‘A’ above the text to select all of Column A.  Then hit the F5 key to launch a dialog box called ‘Go To’  and choose the “Special” option.

roughtranscript-GoTo

Choose “Blanks” and hit OK.

roughtranscript-GoToSpecialBlanks

Something pretty special happens–Excel finds and selects every blank cell in Column A:

roughtranscript-blanksselected

Now you can delete all the rows where Column A is blank by clicking the dropdown on the Delete tab on the Home ribbon, and clicking on “Delete Sheet Rows”

roughtranscript-deletesheetrows

All that blank space in the entire transcript is now gone:

roughtranscript-noblanks

If you stop here and hit print, you’ve done right by Mother Earth.  But hang in there for a couple more steps and you’ll save still more trees.

Step 3. Delete other useless rows

This step is a little more advanced, but well worth the effort.  Notice how these two rows repeat at the end of every page of the transcript:

roughtranscript-morejunkrows

One of them is a non-printing character that may show up as a question mark inside a box, or some other nonsense character.  The other is the line that says “UNEDITED, UNPROOFREAD, UNCORRECTED, etc. etc.” We can use the Filter feature to delete all of these at once.

With any cell selected, click on Filter on the Data ribbon.

A drop-down arrow appears in the first cell (Excel thinks this is a header, but it doesn’t really matter).  Click the drop-down arrow and you are presented with options for filtering the cells displayed by Excel.  This is a list of every different value in Column A. When you click certain values Excel will display only those.

Unclick the Select All option and click the box next to the two offending rows in this particular transcript; click OK.

filter-nonprinting-character  filter-useless-row

This should brings up hundreds of results–two for each page in the rough transcript.

Select the filter results, and use the Delete Sheet Rows trick again.

deselect-row-1      delete-sheet-rows-1

Now turn off the filter by hitting the Filter button again.

As shown below, the only rows that remain now for each page are the Header Row with the page number, and the substantive rows 1-25:

roughtranscript-onepage

Step 5. Adjust the Print Formatting

The last step is a simple one–set the print formatting to make the most of the printed page. Navigate to the Page Layout ribbon and choose Print Titles to get to the print formatting options.  In the Margins tab, reduce the top and bottom margins to half an inch:

print-titles

reduce-margins

Now, hit Print Preview and you’ll see a much svelter printout–without all the blank space and other junk it prints to only 84 pages!

Here’s how it will look now:

Roughie_After_page1-v2Roughie_After_page2-v2

Despite the long-winded explanation, this entire process can be completed in about 3 minutes once you get the hang of it.

Please share this tip with anyone who might be tempted to print out a rough transcript the old-fashioned way!

*For this post, I created a “dummy” rough deposition transcript using Lorem Ipsum, an automated Latin text generator.  If you would like a copy of the transcript, just leave a comment, or email Excel Esquire.

Posted in Everybody, Lit Support, Practice Management | Tagged , , , , | 2 Comments