“Excel Essentials for Busy Lawyers” Approved for New York CLE Credit!

Excel Esquire’s signature course has been re-approved for New York CLE credit for 2019!  Excel Essentials for Busy Lawyers is a 2-hour workshop for beginner and intermediate Excel users, and covers the basics of filtering, sorting, and analyzing data, as well as performing basic calculations and writing formulas.  It’s designed to be useful for all lawyers and legal professionals.  The course is a hands-on, interactive experience that uses realistic data in a fun, collaborative atmosphere.  Students have called it an “excellent presentation” that is “very accessible” and “hugely useful and easy to follow.”

Here are just some of the things you’ll learn in Excel Essentials:

Using Pivot Tables to quickly summarize large amounts of information

Exploiting Excel’s powerful Sort and Filter tools

Amazing keyboard shortcuts

Useful math functions

Demystifying error messages

Time-saving tricks

Your instructor is Ben J. Kusmin, the founder of Excel Esquire.  He is a seasoned commercial litigator and former engineer who has worked at several large New York City law firms. He is a veteran of epic privilege log battles, mammoth document reviews, dueling accounting experts, and complex discovery motion practice.  Along the way he has developed Excel strategies specifically tailored to solve common problems encountered by lawyers and their support staff.  He created Excel Esquire to give other attorneys the skill and confidence they need to successfully navigate in Excel.

The workshop is CLE-approved (NY) for both experienced and newly admitted attorneys. Financial aid is available.  Contact Excel Esquire for more details or to apply for financial aid.

Presentations of Excel Essentials and other courses can be arranged in-house at your law firm, company, bar association, government agency or public interest organization.   Contact Excel Esquire to learn more.

Generate Properly-formatted Bates Numbers in a Snap

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

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:

which generates the following result:

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:

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

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)

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

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.

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.

Find Inconsistent Document Tags with Excel

Scenario:  You’re doing QC on a batch of reviewed documents that needs to be released for production.  One thing you need to check for is whether family members (i.e., emails and their attachments) have been coded consistently.  If an email is tagged Responsive, then its attachments should be too, and vice-versa.  In the screen shot below there are two document families that are tagged correctly, and one that is tagged incorrectly:

The first document family was consistently tagged Non-Responsive, and the second family was consistently tagged Responsive, but the third family is tagged inconsistently: the parent email is tagged Responsive, while the attachments are tagged Non-Responsive.  This family will have to be reviewed, and the discrepancy resolved, before this batch of documents can be released.

In this post I describe an easy technique to identify every family in a metadata set that has inconsistently tagged members.  This makes it easy to isolate and re-review all of them at once.  The same technique can be applied for privilege searches, redaction searches, or any other situation when you want to identify rows of data that match on one criteria but not another.

Download the Excel file here so you can follow along.

When performing this routine on your own data, make sure the metadata you download from your document review platform includes the FamilyID or GroupID field–this is the field that tells you which documents belong to the same document family.

Step 1: Generate a Pivot Table

With your cursor anywhere in the table of metadata, go to the Insert Ribbon and select Pivot Table, and Pivot Table again:

Accept the default settings, and click OK.

Excel will send you to another worksheet with the following display:

DO NOT PANIC. This is easy. Drag the Family  ID field into the Row Labels area and into the Values area, like so:

This generates a tally of the number of documents in each family.  This tells us there are 342 document families comprising 795 documents.  So far, so good.

In order to see how many documents in each family are responsive vs. non-responsive, simply drag the “Resp” field into the Column Labels box.  This is where the magic happens:

Now the pivot table tells you, for each document family, how many members are tagged responsive and how many are tagged non-responsive. Most of the families (those that are tagged consistently) will have a number in only one of these columns.   The ones that are mismatched, however, will have a number in both columns.  The first culprit here is the family ENRON-000001750:

Here’s a nifty trick: Double-clicking on the number 3 in the Grand Total column will prompt Excel to extract the three rows in question and place them on a new worksheet for you to review:

Step 2: Use a Formula to Identify the Rows with Values in Both Columns

Now the trick is to identify every row that has numbers in each column, without simply scrolling down and looking at the numbers.  A bit of arithmetic comes in handy here; if you multiply the two numbers together, you can easily spot when both tag types occur on the same FamilyID.  The reason is that if either column is blank–signifying that all the documents in that family have the same tag–the product will be zero. Otherwise you’ll get some other number! For example, if you have 2 of each tag type, you will get a product of 4 (because 2 X 2 = 4).  If you have 4 of one type and none of the other, you will get a product of 0 (because 4 X 0 = 0).

Generating the formulas to evaluate the product in each row can be a little tricky.  Clicking the cells inside a pivot table to build a function will not work well under the default settings, because Excel wants to generate a function called GetPivotData instead of just grabbing the value you see.  The function you create also will not copy properly.  There are several ways around this:

1. Type the formula from the keyboard rather than clicking cells in the pivot table to generate the references.  Then you will get a working formula that can be copied down. (My method of choice.)
2. Disable the GetPivotData feature by going to the Option menu of the Pivot Table Tab, selecting the dropdown on the far left for “Options” and deselecting the option for Generate GetPivotData.
3. You can also copy the whole pivot table and paste it into another worksheet, where it will no longer behave as a pivot table.

I opt to simply type in the formula; I add the header “check” in Column E and put my formulas there.  Since the first row of the pivot table is Row 5, and two numbers I want to multiply are in cells B5 and C5, I just type =B5*C5 in E5:

The formula returns zero, because there is a zero in C5, reflecting the zero responsive documents in this family.  Copy this formula down to the other rows by locating the cross-hairs on the lower right-hand corner of cell E5 and dragging it down to the bottom (or double-clicking on it!):

The formula copied properly, and is working as intended–rows with two numbers are generating non-zero products, and the other ones are coming up zero:

Step 3: Filter the Results to Target the Mismatched Families

Of course, you don’t want to scroll down hundreds (or thousands) of rows looking for those non-zero values either.  Instead, use the filter to display all those values.  (Yes–you can filter pivot table results!)

Turn on the filter tool by navigating to the Data ribbon and hitting the Filter button. Make sure your cursor is in the pivot table somewhere.

Now click the dropdown on the Check field and select the values you want to see.  In this example we have deselected 0 and the large number at the bottom, which represents the grand total of all the check products.  All the other options are non-zero products that indicate a family mismatch. Hit OK.

The result, in Column A, is the list of 12 document families with inconsistent tags.  As expected, each one has results in both the Responsive and the Non-responsive columns.    Voilà!

Copy and paste this list of Family ID numbers back into a Relativity search, select the ‘include family members’ option, and you will be able to re-review and re-tag this group of documents in one fell swoop.

As with any new skill, explaining this process takes much longer than actually doing it.  Once you get the hang of setting up a pivot table, this entire routine can be done in a couple minutes.  Performing this simple check can spare you the dreaded 6 PM email from the vendor, telling you that your production has “issues” and cannot be burned yet.  And you’ll save your client a few hundred dollars in “tech time” while you’re at it.

Learn more about pivot tables, formula writing, and much more in my 2-hour workshop Excel Essentials for Busy Lawyers.  Students have called it an “excellent presentation” that is “hugely useful and easy to follow.” New York CLE accreditation is pending.  Contact Excel Esquire to learn about hosting an in-house presentation of Excel Essentials at your law firm, company, bar association, or public interest organization.

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.

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:

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:

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

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.

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

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.

Voilà:

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:

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.

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:

Choose Months and Years and hit OK

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

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.

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.

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

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:

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

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):

With this result:

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:

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.

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.

Tracking Duplicates with MATCH, INDEX, and COUNTIF

Today’s post is about a powerful Excel formula for tracking duplicates in a large set of email metadata.  This routine is not for the faint of heart, but if you are managing a document production, supervising a redaction project, or creating a privilege log, this method will save you many hours of time and reduce the likelihood of mistakes.  Roll up your sleeves and read on!

When the document ID numbers are in Column A, and the hash values (or other deduping criterion) are in Column B, type the word “original” in D2.  The first formula, in cell D3, is (drum roll please):

=IF(COUNTIF($B$2:B3,B3)>1,”Duplicate of “&INDEX($A$2:A3,MATCH(B3,$B$2:B3,0)),”original”)

As explained in more detail below, the formula identifies duplicates based on the hash value.  When it hits a duplicate value, it reports the Document ID number of the first document with the same hash value.

Here is a screen shot of the formula in action, in cell D7, where the first duplicate occurs:

Notice how the formula correctly identifies that the email in row 7, with Document ID ENRON-000004452, is a duplicate of the email in Row 6, with Document ID ENRON-000004451.

Here’s how it works:

The COUNTIF formula looks at all the hash values so far and counts up how many times the current value has occurred.  If Excel is seeing the current value for the first time, the formula returns 1 – that means it is an “original” document.  If it is seeing the hash value for, say, the 2nd time, it will return a 2.  Column C in the screenshot contains just the COUNTIF function.  If this is all you did, you would have a nice way of segregating the originals from the duplicates.  All the 1’s are originals, and all the others are duplicates.  The rest of the formula tells you what each duplicate is a duplicate of.

The MATCH formula finds the first occurrence of a value in a stack of values, and returns the relative row number where the value is found.  Here, MATCH(B7,$B$2:B7,0) asks “In the stack of hash values from B2 (the first value) to B7 (the current value), where does the current hash value first appear?”  The answer is 5: the 41DCD hash value first occurs in B6, which is the 5th row of the range.  The 0 in the 3rd position of the MATCH function means that we want an exact match.

The INDEX formula pulls a value from a specified row of a specified stack of cells.  For example, INDEX(A1:A100,8) would return the 8th value in the range A1:A100, i.e., the value in A8.  The row number can be (and usually is) specified by another cell reference or a formula.  For example, INDEX(A1:A100,C55) would return the value in A1:A100 corresponding to whatever value is in cell C55.  Here, the MATCH function provides the reference.  We want INDEX(A2:A7,n), where A2:A7 is the stack of Document ID numbers so far, and n is the row where the current hash value first appears.  Hence, INDEX($A$2:A7,MATCH(B7,$B$2:B7,0).

Putting these three pieces together pulls the Document ID number of the first occurrence of any given hash value–that is the original document corresponding to any given duplicate!  Of course, we only want to do this if the hash value in question is, in fact, a duplicate.  That’s why this routine is embedded in an IF function that makes sure COUNTIF is greater than 1.  Otherwise, the formula returns the message “original.”

A couple more notes about this routine:

• The references to A2 and B2 in the range arguments are anchored with $ signs because we don’t want those references to change when we copy the formula down.  You can insert those $ signs by hitting the F4 key after typing or selecting the reference in the formula.
• As with most complicated formulas, you could break this formula down into several smaller pieces.
• The combination of INDEX and MATCH is a popular strategy for overcoming the limitations of the VLOOKUP function.
• The routine described here can also be used to link email attachments with their parent emails, based on the GroupID or FamilyID field.

Using this routine has helped me streamline privilege logs and document reviews, and avoid producing duplicate documents with inconsistent redactions.  I hope you find it helpful, too.

This is quite a bit more complicated than what is covered in my off-the-shelf Excel Esquire courses.  If you would like to learn about setting up a customized training that covers this type of eDiscovery analytics–whether for attorneys, paralegals, or litigation support staff–please get in touch.

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:

 

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:

 

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

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

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

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

  

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

 

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

 

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:

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.

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.

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:

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:

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

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:

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:

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.

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:

 

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.

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.

Choose “Blanks” and hit OK.

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

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”

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

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:

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.

 

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.

     

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:

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:

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:

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.

April CLE Courses Now Enrolling in NYC!

Tools of the Trade: Excel Skills Every Litigator Should Know carries 3.0 New York CLE credits, and April classes are now enrolling in New York City.

The first CLE course of its kind, Tools of the Trade takes you step by step through some of the most important Excel features as you wrangle metadata with filters and VLOOKUP, reconcile mismatched lists with COUNTIF, clean up a privilege log, and find and manage duplicates.  And more!  Workshops are small (maximum 15 participants), interactive, and hands-on.  Follow along on your laptop to get the full experience, or just sit back and soak it all in.  Then take home a comprehensive set of materials that reproduces everything taught in the class, with screen shots  and step-by-step instructions.  Tools of the Trade will make you the “guru” of your company, law firm, or public interest organization!

Your instructor is Ben J. Kusmin, the founder of Excel Esquire.  He is a former engineer and a seasoned commercial litigator who has worked at several large New York City law firms. He is a veteran of epic privilege log battles, mammoth document reviews, complex redaction projects, dueling accounting experts, and all manner of discovery motion practice.  He developed Excel Esquire to give other attorneys the skill and confidence they need to successfully navigate in Excel.

Course Details:

Location:    116 W. 23rd St. Suite 5  NY, NY

---

Price: $295*  (Financial aid is available.  Contact Excel Esquire for more details or to apply for financial aid.)

Tools of the Trade is approved for both newly admitted and experienced attorneys, and carries 3.0 Law Practice Management credits.

To register, follow the LawPay link below (page will open in a new tab):

Presentations of Tools of the Trade and other CLE programs can also be arranged in-house at your law firm, company, bar association, or public interest organization.  Custom courses can also be designed for other legal audiences.   Contact Excel Esquire to learn more.

Not ready to become a guru just yet?  Follow the Excel Esquire blog to get great tricks and tips, and get notified about future courses.

 

Sort Email Metadata Without Losing the Attachments

This routine is a big favorite in my New York Excel CLE course Tools of the Trade: Excel Skills Every Litigator Should Know. Click here for more information about the course.

Scenario: You have a set of email metadata corresponding to a privilege log, exhibit list, or other set of documents, and you want to sort it in chronological order. When you try to sort the entries, you discover that all the attachments get pushed to the bottom of the list, because the date and time fields for the attachments are empty! How do you get the attachments to “stick” to their parent emails?

Strategy: We’ll combine some little-known Excel tricks to “propagate” the Date Sent and Time Sent for every email to each of its attachments.  Then they will sort properly. First, create a new column next to the DateSent column and call it SortDate.

Copy and paste the DateSent values into the new SortDate Column

This is where the magic happens:

Select the entire SortDate Column by clicking on the letter C, and then hit the F5 button to launch the Go To dialog box.  (You can also launch this Dialog Box by selecting Find & Select from the Home Tab.) From there, choose ‘Special’ in the lower left-hand corner.

Select the option “Blanks” and hit OK.

Notice that all of the blank cells (except the first one) in Column C are now highlighted in blue, indicating that they are ‘selected.’ The first blank cell, C3, has a box around it, indicating that C3 is the “Active Cell.”

With all the blank cells selected, we can fill each blank cell with a formula that references the cell above it, which will contain the date of the most recent email in the list. Without clicking anywhere, type an equals sign (“=”) and then click C2, the date above that Active Cell. (Don’t hit enter yet!) It should look like this:

Now hold down the Ctrl key and hit Enter. This fills in every selected cell with what you have just typed–namely, a formula that points to the cell directly above it.

Every cell that was blank before now has the value of the most recent populated cell above it. This even filled in the dates where there are multiple attachments:

With those cells still selected, click the Right Justify button and the Italics button, so that our new values will be easy to distinguish from the existing values.

Now you can hit Enter to complete this step. Finally, let’s replace all of those formulas with the dates they generate to prevent mischief later on (and reduce the file size). Copy the entire Column and then use Paste Special – Values.

The dates for the attachments are now hard-wired into the spreadsheet. (Tip: After a copy & paste operation you’ll see “dancing ants” around the block of cells you pasted. You can get rid of that by hitting Escape.) Now repeat the same process for the TimeSent column and arrive at this:

Now you can sort the whole table by SortDate, then SortTime. Click Ctrl+Home to select cell A1, then navigate to the Sort button on the Data tab.

Click the Add Level button once so that you’ll have two criteria, and sort by SortDate, then SortTime, accept the default Order settings, and the entries will be sorted by oldest to newest, and then in the original order when multiple entries (such as attachments) have the same date and time.

Your entries are now sorted in chronological order, attachments and all.

Voila!

One final tip: before a big sort like this, make sure you know how to get back to the original order if necessary. If there isn’t already an obvious order to the rows (e.g., by Document ID number or privilege log entry) create a new Column A and number the rows all the way down. You can enter the first few and then double click the cross-hairs in the lower-right corner of the last cell you completed, and Excel will autofill the rest.

There are many other uses for this elegant routine.  For example, you could identify all of the emails and attachments from a given sender, by “propagating” the From field.

Thanks for reading. Feel free to comment, share, and re-post.