Scared Straight? Reviewing Excel Files in the Wake of Wells Fargo

The Wells Fargo inadvertent disclosure episode provides a high-profile reminder that attorneys who are responsible for reviewing and producing client documents must thoroughly understand those documents.  There were several process breakdowns that led to the inadvertent disclosure of thousands of confidential records, and not all of them were very well explained.  (The affirmation of the producing attorney is available on the New York e-filing website, Index No. 652025/2017 )(see docket entry 36).  But however it happened, as reported by the New York Times, the inadvertently produced material “included copious spreadsheets with customers’ names and Social Security numbers, paired with financial details like the size of their investment portfolios and the fees the bank charged them.”  This might have been a pure eDiscovery mistake–i.e., an accidental production of documents due to tagging errors or miscommunication with the vendor.  But I suspect some Excel ineptitude was at play as well: the producing lawyer may have affirmatively decided to produce certain spreadsheets without realizing that they contained this confidential data.  Excel features such as filters and hidden columns often mean that there is data “hidden in plain sight” that escapes the attention of document reviewers.

That possibility should give us all pause–might we make the same kind of mistakes?  As the producing party, the consequences of failing to review documents thoroughly go beyond just bad publicity. At one end of the spectrum you may fail to produce relevant information, and face an embarrassingly meritorious (not to mention expensive) motion to compel. At the other end, you may inadvertently produce a “treasure trove” of sensitive information that should never have been produced.  Aside from potentially landing you on the pages of the New York Times, that kind of mistake may jeopardize your case, and the representation itself.  If the information contains Social Security numbers or other PII, the disclosure may run afoul of state and federal privacy laws as well.  Of course, as the receiving party, failing to comprehend the evidence produced by the other side means that you may be overlooking evidence that could win the case for you.

The key to avoiding such mistakes is to thoroughly understand the relevant features of Excel.  This can be a challenge because the users–our clients’ employees–are very skilled users of Excel!  They use it all day to create fancy spreadsheets.  It’s their job.  These files are not dumbed down for us when the eDiscovery vendor collects and hosts them for us to review.  That means we’ve got to roll up our sleeves and get our hands dirty–whether we “like” using Excel is not relevant.

Is your document review team up to the task?   Here is a list of skills that I think every reviewer must have to competently review spreadsheets in the eDiscovery context:

  • Reviewing the entire workbook, not just the current tab
    “In my Father’s house are many mansions . . . ” (John 14:2).  So, too, are there multiple worksheets, a/k/a tabs, in a typical Excel file. Some complex accounting files can have more than 20 worksheets!  Reviewers have to understand that every worksheet must be reviewed for relevant information, not just the worksheet that first pops up when you open the file.  It’s not always obvious how many worksheets there are.  In the example below, it looks like there are only 5 worksheets.Multitab-1

    But looks can be deceiving, and they are here.  You can launch a list of all the worksheets in an Excel file by right-clicking on the group of arrows in the lower left-hand corner–the area shown in green below. Doing that here reveals that there are three more worksheets, called Additional Data, Update, and Corrected Data–those sound important!


    Meanwhile, to search for specific information or keywords throughout a multi-tab workbook, you can tweak Excel’s Find feature to search for content across all the worksheets, rather than only the current worksheet, which is the default. To do this, click on Options in the Find dialog box, and select “Within –> Workbook”; then choose “Find All” instead of “Find Next.”

  • Understanding Filters
    Filters allow a user to selectively display certain rows of a worksheet with certain characteristics.  In the example below, a spreadsheet of wire payments is filtered to display only records where the customer name contains ‘Fujitsu’–there are thousands of other records in the spreadsheet that are not currently displayed, but can be revealed by clearing or turning off the filter (e.g., by the receiving party).FilterRedact-fujitsu

    Business professionals love
    filters, and use them extensively.  As a consequence, many native files open up with filters already applied, making a lot of the information temporarily invisible.  As a document reviewer, you fail to understand filters at your peril.   I discussed this issue in a recent presentation at the Harris County Law Library in Houston; the relevant (8-minute) video clip is viewable here.
  • Identifying hidden rows, columns, and worksheets
    Relativity is not your friend here.  In the default view you probably won’t recognize hidden material, much less be able to unhide it.   You should really be downloading Excel files natively and reviewing within Excel to make sure you’re seeing everything. That said, your eDiscovery vendor may be able to create and populate a metadata field to flag Excel files with hidden content.  Once inside, there are several tricks to find and unhide it. (I discuss one of the tricks in this post.) Remember: there is nothing necessarily nefarious about hiding content in Excel–it is usually a matter of convenience.  And when the last user of the file saves it with hidden content, it will still be hidden when you open the file to review it.
  • Identifying & Deciphering formulas
    Numbers that show up in an Excel worksheet may be just typed in, or they may be generated by a formula, a link to other cells, or both.  The distinction can be extraordinarily important in a litigation.   Sometimes values are hard-wired when they should be generated by a formula, and sometimes formulas and links are fudged to perpetrate a fraud.  Identifying which kind of cell is which is critical. Once you identify the formulas, you’ll want to figure out how they work.   You may need to call in the experts, but a basic knowledge of formula syntax can go a long way.  I discuss some tips for finding and analyzing formulas, including detecting inconsistent formulas, in this post.
  • Understand the Freeze Panes display feature
    Sometimes a worksheet is bizarrely difficult to navigate, and the reason is Freeze Panes–a way of locking the display to keep certain rows and columns visible.  Some users may also have unusual Freeze Panes settings that can obscure large parts of the worksheet.  These settings are easy to turn on and off from the “View” ribbon:unfreeze-panes
  • Finding and Expanding Truncated Text
    Sometimes certain cells contain reams of text that aren’t immediately visible.  Take a look at the spreadsheet screen-capped below–it’s derived from publicly available data on 1,651 Chicago restaurant inspections between 2010 and 2015. (You can download this data from the Chicago Data Portal.)   Column N is a field called Violations; each cell appears to have a number and then a brief description.overflowtext-1When you click on one of those cells, you discover that there is a lot more text in it than what appears in the too-small cell.  The actual contents appear (kind of) in the Formula Bar up above (shown in the green box).


    But wait, there’s more!  In fact, this cell contains several paragraphs of text, and the default Formula Bar only displays the first line.  When you drag the lower edge of the Formula Bar down to make it bigger, you finally see the entire Violation description.


    Would your document reviewers know that they need to stretch out the Formula Bar to see all the text?  Suppose your litigation involves faulty ice machines? Lead paint? Sneeze guards? How do you make sure that you’re identifying all the relevant entries in this workbook, when the relevant text may not appear initially?  There are a number of Excel techniques beyond the scope of this post to plumb the depths of a text-heavy spreadsheet like this.  Suffice it to say you do not want to leave the proper review of this file to chance.

  • Identify the boundaries of a worksheet 
    You may think you are at the bottom or the edge of a set of data, but how can you be sure there isn’t more stuff down there? There are ways.  For example, to jump to the very last used cell on a worksheet, hit the F5 key to launch the Go To dialog; then click Special–>Last cell.GoToLastCell
  • Finding each worksheet’s print formatting
    Reviewing which data a user chose to print, and what kind of titles she added, can provide valuable insight into the case.  For example, an otherwise innocuous-looking list of foreign exchange trades might have a footer that says “Fake Trades to Process”–you’d be surprised how dumb some criminals are.   Fortunately, there are ways to review this information without actually printing each worksheet.  One is to review the print formatting in the “Name Manager” ribbon; another is to use Print Preview.   

Excel Esquire offers an interactive (and potentially CLE-accredited) workshop that illustrates the most important aspects of legal Excel review in depth, with real spreadsheets.  To learn about organizing a workshop for your document review team, please get in touch with Ben Kusmin of Excel Esquire.  Thanks for reading!  Feel free to comment, link, or share.


Posted in CLE, eDiscovery, formulas, Lit Support, Native File Review, Practice Management | 5 Comments

Excel Filters and the Duty of Technology Competence

The Harris County Law Library in Houston, Texas recently hosted my presentation “Excel Essentials for the Practice of Law.”  Among other topics, I explained how filters work in Excel, and how they present pitfalls for the unwary lawyer.  When lawyers responsible for reviewing documents–either incoming or outgoing–are unaware of how filters work, all kinds of things can go wrong.  Learn more by watching this 8-minute video excerpt:

In the ongoing conversation about what constitutes “technology competence” for lawyers, I think more attention should be paid to Microsoft Excel.  Because our clients’ employees use it, and our adversaries’ employees use it, we ignore it at our peril.

I hope you enjoyed this post and video.  If you would like to learn how to host an Excel workshop at your law library, bar association, law firm, company, or public interest organization, please get in touch with Ben Kusmin of Excel Esquire.

Posted in CLE, eDiscovery, Lit Support, Native File Review, Practice Management | Leave a comment

Upcoming CLE Event in Houston, TX (with livestream)

On July 13th, I will give a presentation called “Excel Essentials for the Practice of Law” at the Harris County Law Library in Houston.  The presentation is designed for lawyers, paralegals, and other legal professionals.  Participants will learn how to manipulate text with Find & Replace, print worksheets with a custom footer, analyze data with Excel’s Filter feature, and create and copy formulas.  If you’re not lucky enough to be in the Lone Star State, you can watch the livestream!

More details below from HCLL’s website:

Date: Thursday, July 13, 2017

Time: 12pm – 1pm

Class Format: Classroom & Livestreaming

Where: Harris County Law Library — Harris County Attorney Office Conference Center, 1019 Congress Street, Houston, Texas 77002

Description: Microsoft Excel is an important tool for the modern legal profession. It can assist in supervising document reviews, calculating damages, and managing client information. Because so many business clients use Excel, spreadsheets often become evidence in their own right, and attorneys need to be able to review and understand Excel documents. In this interactive program, Ben J. Kusmin, a commercial litigator and the founder of Excel Esquire, will demonstrate the most important features of Microsoft Excel using concrete examples and realistic data. Attendees will have the option to download the data used in the presentation to follow along on their own computers.

CLE: 1.0 hour CLE in Texas (pending)

To learn more and register, please follow this link: HCLL

Posted in CLE, eDiscovery, Everybody, formulas, Lit Support, Practice Management | 1 Comment

Spelling Bee Fundraiser for Harlem Hospital CCHP

Hello readers!  Your humble narrator and Excel blogger will compete in a spelling bee fundraiser to benefit the Center for Comprehensive Health Practice, a terrific hospital serving needy residents in Harlem.  They are facing a difficult rent hike and this fundraiser is critically important.  And if you’re in New York, please come to the spelling bee – it is a lot of fun! (The featured photo *might* be me in last year’s spelling bee.)   Please follow the link to learn more about the event or make a donation.


With any luck they will ask me to spell CONCATENATE.

Posted in eDiscovery, Everybody | Tagged , , | 1 Comment

Nassau County Bar Association CLE Event – March 8

The Nassau Academy of Law, the educational arm of the NCBA, will host an Excel Esquire CLE lunch program called “Excel Essentials for the Practice of Law” on Wednesday, March 8.  Lunch is served at 12:30, and the one-hour program will start at 1 pm.  NCBA is the largest suburban bar association in the country, and we’re expecting a great turnout. The program will feature lots of great Excel content for both beginners and experienced users.  See below for more details:


Click here to go to the registration page for this program.  Hope to see you there!




Posted in CLE, eDiscovery, Everybody, formulas | Tagged , , | Leave a comment

Highlight Every Other Row of a Privilege Log (or other spreadsheet!)

Happy New Year, readers! Today’s tip is an old favorite, and one that can save you a lot of tedious work.   It exploits Excel’s conditional formatting feature to quickly highlight every other row of your privilege log, or any other spreadsheet.  Feel free to download my dummy privilege log if you would like to follow along.

Here is the original, unadorned privilege log:


In order to highlight alternating rows, take the following steps:

First, select all the cells containing the privilege log entries, but not the Header Row.  Also, don’t select the entire rows–just the cells to be highlighted. You can do this by clicking on cell A2 and dragging the cursor to the last cell on the bottom right of the log.  (Pro tip: with cell A2 selected, hit Ctrl+Shift+Down Arrow, then Ctrl+Shift+Right Arrow (perhaps a couple times) to highlight all the cells.)


Next, from the Home ribbon, click the drop-down menu under Conditional Formatting and select New Rule:


In the dialog box, choose the option “Use a formula to determine which cells to format”; then, in the formula bar, type the following (don’t forget the “=”):


. . . then hit the Format… button to set up the highlighting.


Choose Fill, pick a nice color, and hit OK:


Now you can hit OK on the Conditional Formatting dialog box:


Voilà!  Alternating rows of the privilege log will be highlighted in the color you chose:



The formula analyzes every cell in the selection and applies the formatting to every cell in an odd-numbered row.  That’s why the entries in rows 3, 5, 7, etc. are highlighted.  You can delete this highlighting, if necessary, by selecting all the cells and choosing Clear Rules from the conditional formatting drop-down menu.

Please share, forward, or like this post, and subscribe to the Excel Esquire blog!

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, formulas, Lit Support, Practice Management, Privilege Logs | Tagged , , , , , , | 1 Comment

Hide Your Name When Printing Outlook Emails

When you print an Outlook email–whether your own or someone else’s–your own name appears by default in large text at the top of the printout.


This is awkward if you are printing emails as a delegate, or are using them as exhibits to a motion.  How do you get rid of it?  For some reason, Outlook won’t let you remove the header entirely, but there is an easy way to make it virtually invisible.  Here’s how:

1. Under the Print menu, click on the small box promising “Print Options”:


2. Then select Page Setup


3. Under the Format tab you can see that Outlook prints your name in the header with a larger font by default.  We can change that by selecting Font:


4.  As with other Microsoft applications, the choices of font size appear limited, but you can always type in the exact size you want.  Here type in 1 to make it really small.


5.  Click OK, confirm the other print settings, and print normally.


Voila!  Your name is so small it may as well not even be there.  This change should stick, so you won’t ever have to think about changing the settings again–until your colleagues ask you how you did that!

You could also have a little fun by choosing a symbolic font to obscure your name, while keeping the font size legible.  I like Wingdings.


Enjoy this post?  Please comment, subscribe, and share!  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. Thank you, and be well.

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

Redact Native Excel Files with the Filter Tool

Sometimes you need to produce only certain rows of a large Excel spreadsheet.  Some lawyers will painstakingly apply redaction stamps to hundreds of pages of tiff images, leaving only the responsive rows visible. This is a terrible waste of time and money, and typically generates an unusable mess like this, which sometimes extends to hundreds or even thousands of pages:


This type of production fairly cries out for a motion to compel.  The next time you need to heavily redact a spreadsheet, use Excel’s built-in Filter tool to quickly create an elegant display of only the responsive rows of data.

This tutorial uses a fictitious data set (which you may download here) that has a tab for each month of 2015.  Each tab contains a list of thousands of incoming wire payments from various customers, in straight chronological order. The goal of this exercise  is to produce only the records that relate to Fujitsu subsidiaries. The January tab looks like this:


In this split screen view, you can see that there are 2,246 rows of data on the January tab.  This will print or tiff to about 53 pages for this tab alone, but there are only 8 rows of data that need to be produced. Ugh!


Rather than trying to redact every row that doesn’t relate to Fujitsu, we’ll use the following simple routine to selectively display only the responsive rows and create a highly readable (not to mention defensible) production of the responsive data.

Step 1: Turn on the Filter tool.


NB: The filter tool works properly when data is arranged as a table, i.e., there is a header row, there is one record in each row, and there are no blank rows or columns.

With the filter tool activated, you can filter the data by any of the fields by clicking the drop-down on the desired column header and choosing the filter criteria:

Here, you can filter for the Fujitsu records by scrolling through the choices on the Customer field and clicking all the Fujitsu entities (there are three in this example), or by typing “Fujitsu” in the filter tool’s built-in search bar:

FilterRedact-clickfujitsuX3  OR  FilterRedact-typefujitsu

Either way, hit OK and you should get this result:


Pretty good trick, huh? Notice how the row numbers now show up in blue to remind you that only certain values are displayed.  Notice, too, that a funnel icon appears where the drop-down icon used to be on Column C to let you know that Column C is where the Filter is applied.

At this point the printout or tiff image would look like this:


This is a vast improvement over 53 heavily redacted pages, but we’re going to do a few more things to make our production bullet-proof.

Step 2: Launch the Page Setup dialog by clicking Print Titles on the Page Layout ribbon:


Perform the following formatting steps:

On the Sheet tab

  1. Click into the “Rows to Repeat at top” field and click on the Row Header for Row 1 to generate the syntax below.  Now if your printout is more than one page long you will see the Header repeated on every page of the printout.
  2. Select the option to Print Gridlines.  This will improve the readability of the printout.
  3. Select the option to Print Row and column headings.  This will make it possible to identify exactly which cells of the spreadsheet are being printed, and will make it easier to refer to specific information later, such as in a deposition or in a motion.


On the Page Tab:

  1. Select either Portrait or Landscape orientation depending on the data in the current worksheet.  In this example we’ll use the default Portrait mode because there are only a few columns.
  2. Select ‘Fit to 1 page wide and [blank] pages tall.’ Excel will figure out how many pages tall the printout needs to be.  NB: If you absolutely must make the printout 2 pages wide because there are too many columns (and you’re already in Landscape mode), then use the “Columns to repeat” option on the Sheet tab.


On the Header/Footer Tab:

  1. Click on Custom Footer.


This will launch the Custom Footer dialog box, where you will create the following:

FilterRedact-customfooterIn the Left section area, type “Filename: ” and click the filename button to insert the Excel code for the filename.  Do the same for the Tab name on the next line.  Then type a line explaining what Filter setting was used–the goal here is transparency.

Before you hit OK, select all the text and hit the Format Text button (the A on the left):


In the formatting dialog box, set the font to bold and increase the font size . . .

FilterRedact-footerboldandbig    to get this:   FilterRedact-footerbignow

These formatting tricks make the data print like so:


And generate the following footer:


And what would have been 53 ugly pages reduces to a single, highly responsive page that looks like this:


While this may seem like a lot of steps, it will only take a couple minutes per tab once you get the hang of it–and certainly much less time than redacting the tiff images individually.

A few more points:

  • Perform these formatting changes for each tab of the Excel file that needs to be filtered
  • Do not produce the native file for this Excel document–if you do your adversary can turn off the filter and see all of the original information.  Instead, print this to pdf and have your vendor tiff the pdf file.
  • Make sure your vendor understands what you are doing, and that they apply the correct confidentiality designation and Bates numbers to the production images.
  • Explain what you have done in your production cover letter.

Producing information this way has many virtues.  The first is clarity: Your adversary, and more importantly, the judge, can immediately understand exactly what has been produced here–namely, the information pertaining to Fujitsu entities on the January tab of the file called ‘Incoming Wire Payment Register 2015.’   Because the row numbers are also displayed (in blue above) it is also evident that about 2,000 non-responsive rows were filtered out–saving piles of your client’s money and countless trees.  Finally, you send a powerful message when you produce the responsive information this way, rather than buried among hundreds of pages of redactions:  You are not afraid to produce responsive information in a legible format, because you have a strong case and nothing to hide.

In a future post I’ll explain an efficient method for redacting the native Excel file when you need to leave the customer names exposed, but redact all of the payment amounts and other data.

Enjoy this post?  Please comment, subscribe, and share!  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. Thank you, and be well.



Posted in CLE, eDiscovery, Lit Support, Native File Review, Practice Management | Tagged , , , , , , , , , , | 1 Comment

Get Excel to Automatically Launch Documents from an Exhibit List, Privilege Log, or Hot Documents List

Today I’ll explain how to create a schedule of documents or other files in Excel that includes a hyperlink for each document.  Anyone reviewing this list will be able to open any document described on it by just clicking the link.  This makes it easy to review a list of deposition exhibits or hot documents, or the internal version of a privilege log, and immediately review any document from the list that looks interesting.

Step 1:  Generate a list of the filenames that will be linked in the schedule.  They should all be in the same folder.  You can learn how to do this here.

NB:  If the filenames follow a predictable naming convention such as JX1, JX2, etc. the names can be generated with a formula such as =“JX”&A2 where column A contains the number of each exhibit.

Step 2:  Paste or create the filenames in the column of your choice.  In my example they are in Column A, and I’ve left cell A1 free:


Step 3:  Navigate to the folder that contains the files in Outlook Explorer.  In this example I want to link to the photographs saved in a subfolder called “late 2014” within the My Pictures directory:


Step 4:  Click in the address bar to make the full path come up.  Copy the path by hitting Ctrl+C


Step 5:  Paste the path in a convenient cell in your schedule file.  In this example I’ve pasted it in cell A1.


Step 6:  Use the HYPERLINK formula to generate a hyperlink to each document.  The function has two arguments: 1) the “link location,” i.e., the path to the file, including the filename; and 2) the “friendly name,” i.e., the text of the clickable message that you want to appear.  Excel prompts you for these two arguments when you type out the name of the function and the open parenthesis:


We’ve set the schedule up in such a way that the link location will follow a predictable pattern—we can simply “splice” the path in cell A1 and the file name in the adjacent cell of Column A to get the complete path to the relevant document.  The first formula—to launch the file in cell A3— looks like this:   =HYPERLINK($A$1&A3,”Open File”)

And this is how it appears onscreen:


See how the friendly name is what appears in the cell, while the underlying function you created is visible in the formula bar.  Note the use of dollar signs for the A1 reference (insertable by hitting F4), and the quotation marks around the friendly name text. 

Step 7: Copy the formula down to the rest of the filenames.  You can accomplish this by double-clicking the cross-hairs in the lower right-hand corner of cell B3.  Excel will generate the appropriate formula for each filename, concatenating the path in cell A1 and the correct filename.  Observe how the formula in cell B4 still refers to the path name in A1 (thanks to the dollar signs) but uses the filename in the same row, viz., A4:


Observe how clicking on the link next to the file IMG_1317.JPG launches the picture in a new window:


That’s all there is to it.  The key to making this work is that you have to have access to the folder containing the documents in the same environment in which the Excel file is open.  For example, if your documents reside in a local shared drive, someone in another office will not be able to open the documents from this Excel file because the links will not work.

If you want to make this file portable, make sure you use the correct path for the media where the files will reside.  For example, you could store all your deposition exhibits on a thumb drive, and put this file on the thumb drive as well.  Just make sure that the path you are using to launch the documents is the path that points to the thumb drive, not your computer back at the office.

Thanks for reading.  If you have an idea about how to use this tip in your law practice, 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, formulas, Lit Support, Privilege Logs | Tagged , , , , , , , | 5 Comments