Upcoming CLE Event for Chicago Bar Association – Advanced Excel for Litigators

The CBA is hosting an exciting new Excel Esquire program called Advanced Excel for Litigators at the historic Chicago Bar Association Building from 12-3 pm on April 20th.  This will be a small, interactive program with plenty of opportunity for feedback.  Participants are encouraged to follow along on their own (or loaner) laptop.

The agenda is chock full of useful skills for litigators and eDiscovery practitioners.  This is a brief overview of the program:

  • Mastering the Filter Tool to Analyze and Edit Data
  • Conditional Formulas, Missing Information, and Complex Sorting
  • Mastering VLOOKUP
  • Special Tools to Reconcile Lists
  • Competent Review of Native Excel Files

Follow this link for more information and registration instructions.  Advance registration is only $55 for CBA members, CLE Advantage members, students, and government employees, and only $85 for everyone else.  Hurry – enrollment is limited to 18!


To learn about organizing an Excel workshop for your bar association, law firm, corporate legal department, or public interest organization, please get in touch with Ben Kusmin of Excel Esquire.  Thanks for reading!  Feel free to comment, link, or share.

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

Pseudo-Scientific Notation, or The Mysterious Case of the Middle E

Typing a part number like 32E50 into an Excel spreadsheet can give you fits.  Excel thinks you are using shorthand for scientific notation, and converts this string into the number 3.20E+51 (or 32 with 50 zeroes) – Ugh!  The way to overcome this feature is to enter the string with a leading single quotation mark, like so: ’32E50

You would do the same thing if you wanted to type a formula, or anything else that begins with an equals sign, without Excel trying to convert it to a formula.


Thanks for reading – hope it helps!

Posted in Everybody, formulas, Lit Support | Tagged , , , , , | 1 Comment

Finding and Reviewing Comment Boxes in Excel Files

Following the Wells Fargo inadvertent disclosure episode, I wrote a blog post describing several ways to find hidden content in Excel files.  In this post I discuss another feature of Excel that all lawyers, but especially legal document reviewers, should know about: the comment box.

Excel allows you to attach comment boxes to particular cells (just click Shift + F2), and also provides several visibility options.  Depending on the options set by the original user, the comments may be fairly easy to identify and read, or quite challenging.  When reviewing Excel files in discovery, it is important to find and review all the comments – they might contain information important to the case, or reflect privileged information.  Here’s an example of a spreadsheet with comments from the Enron corpus (download the file if you like):


In this (default) view, each cell with a comment attached has a tiny red flag in the corner.  When you hover the mouse over such a cell, the comment box pops up.


You can change the appearance of comments by going to the Review ribbon and clicking on Show All Comments, but that is no guarantee that some of the comment boxes won’t escape your attention. You would have to visually scan the entire used area of each worksheet to find them.

What about Relativity, you ask?  Unfortunately, it is almost impossible to review comment boxes thoroughly in Relativity. Here is a view of the same Enron file shown above (in Excel), as viewed in the Viewer mode in Relativity:


Notice that the red flags used in Excel to indicate a comment are not visible in Relativity.  Hovering over the cells does not bring them up, either.

The underlying comments do make it into the OCR text, albeit not very helpfully.  For some reason the text of the comments is extracted and dumped at the end of the OCR text for the affected tab.  In the view below you can see that the comment “Stephanie K McGinnis: When EPC contract is officially turned on” can be found by searching the extracted text.  But this comment text is sandwiched between the last OCR text on this tab, and the OCR text at the beginning of the next tab.  There is no telling which cell this comment was attached to.


Finding all of the comment boxes in an Excel file and reviewing them in context requires reviewing the native file in Excel.  The good news is, there’s a straightforward technique to find and review every comment.  Launch the Find dialog box (e.g., with Ctrl+F), and follow these steps:

  1. Click the Options button to open the additional tools
  2. Under ‘Find What’ type a single asterisk (*) – this is Excel’s wild card
  3. Under ‘Within’ select “Workbook”
  4. Under ‘Look in’ select “Comments”
  5. Click “Find All”

Excel will then launch a results window that helps you navigate to every cell with a comment box attached.


Clicking on a reference in this results list will navigate to the appropriate tab and cell, while (helpfully, I think) keeping this window on top.

Note that the material in the Value column reflects the visible content of the cell where the comment is attached–you will have to navigate to the cell and hover over it to see what the comment actually is. For example, in the Find results shown above, the sixth result is cell H16 on the tab called NTP or Sold, which contains the text “Fr 6B 50hz power barges”; click somewhere on that line to jump to that cell.  When you hover over it, you’ll then see the comment attached to the cell, viz., Stephanie McGinnis’s comment “left outside and water got into gauges.”


Of course, if an Excel file does not have any comments, you can confidently skip this routine.  How do you know if a given spreadsheet has any comment boxes lurking to begin with? Simply click on the File ribbon – the area called Prepare for Sharing will list issues with the file, including the existence of comment boxes.


Under Prepare for Sharing, the first issue listed is Comments – now you know how to find and review them.  To get a more thorough analysis of the file, including a count of hidden worksheets, columns, and rows, click on Check for Issues and follow the prompts.

A disclaimer: If there are comments attached to cells in hidden worksheets, using Find All as described above will not identify them, unless and until you unhide the hidden worksheets.  (But the Prepare for Sharing box will alert you to the presence of both the hidden worksheets and the comments.)  During any kind of legal document review, you’ll want to unhide any hidden worksheets in an Excel file whether there are comments or not.

Thank you for reading.  Subscribe to the Excel Esquire blog to get more tips about using Excel in the legal environment.  And feel free to like, comment, and share!




Posted in CLE, eDiscovery, Lit Support, Native File Review | Tagged , , , , , , | 2 Comments

Avoiding Inadvertent Productions and Other Excel Blunders: New CLE via Wolters Kluwer/myLawCLE

Wolters Kluwer/myLawCLE will webcast my exciting new CLE presentation from 2-4 pm on October 16th.  I will use real Excel files to explain tricky features of Excel that attorneys must be aware of, such as hidden content, filters, comment boxes, and overflow text.   I will also discuss several high-profile episodes where failure to understand Excel led to an embarrassing lawyer moment, including the famous Lehman Brothers bankruptcy incident.  This is a unique learning opportunity for practicing lawyers–many CLE programs talk about how to cope with an inadvertent production after it’s happened, but this one will help you avoid one in the first place.


The program will be broadcast live from Wolters Kluwer’s studio in the Google Building in NYC, and I will take emailed questions from the audience.  It should be a lively and informative event!  Follow this link to learn more and register for the webcast.


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

Digital Detectives Podcast Interview with Excel Esquire’s Ben Kusmin – Spreadsheets as Evidence

Sharon Nelson and John Simek from the Legal Talk Network’s Digital Detectives podcast recently interviewed Excel Esquire’s Ben Kusmin about the pitfalls (and opportunities) faced by lawyers when handling their clients’ Excel spreadsheets. Among other things, we discussed native review of spreadsheets, turning them into deposition or motion exhibits, the challenges of redacting spreadsheets, and the importance of getting Excel files produced in native format. I also gave my take on the Wells Fargo inadvertent disclosure incident, and explained how Excel competence can be part of the lawyer’s duty of technology competence.  You can listen to this lively discussion by following this link to the Digital Detectives podcast. Enjoy!


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

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