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.