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.

Advertisements

About excelesquire

NYC attorney and Excel enthusiast.
This entry was posted in eDiscovery, formulas, Lit Support, Native File Review and tagged , , , , . Bookmark the permalink.

6 Responses to Reviewing Native Excel Files, Part 1: Detecting Inconsistent Formulas

  1. dqframey says:

    Another nice tell is when most cells in a column have a formula calculating the value, but a few have the value typed in.

    Like

    • excelesquire says:

      dqframey, you are absolutely right–hardwiring values is another way to get around a pesky formula. Those hard-wired values will not trigger a green warning triangle, either–all the more reason to hit Ctrl+` to review all the formulas (or lack thereof!).

      Like

  2. Great article!! It is encouraging to see that in court case people are auditing the spreadsheets. The example you present in the article illustrates an inconsistency in formula inputs, but even more than that, the example illustrates a violation of Excel’s Golden Rule:

    1. If a formula input can change, put it into a cell and refer to it in the formula with a cell reference.
    2. If it will not change, you can type it into a formula.
    3. ALWAYS LABEL YOUR FORMULA INPUTS!

    Not only does the example in the article show that someone slipped a 0.15 into the formula, but the face of the spreadsheet does not present the full details, like “Commission Rate” (label) and 0.08 (Actual Formula Input).

    Hard coding numbers into formulas is the #1 cause of faulty spreadsheets and violate the original epiphany from Bricklin and Frankston that led to the first spreadsheet, VisiCalc.

    Here is a scary article that presents research about how the violation of Excel’s Golden Rule is the #1 cause of faulty spreadsheets:

    http://www.strategy-at-risk.com/2009/03/03/the-risk-of-spreadsheet-errors/

    I have many videos about Excel’s Golden Rule. Here is one of them:

    Thank you for the awesome and thought provoking article. If only we had more thoughtful Excel Auditors in the world! Your article prompts us in that direction, a very positive direction!!!!

    Like

    • excelesquire says:

      Mike, Thank you for your thoughtful feedback and your interesting link. As for the Golden Rule, in my experience, when folks are fudging trades, manipulating inputs, or otherwise cooking the books, all kinds of rules get broken! That’s why attorneys have to be vigilant and thorough.

      Like

  3. Joshua says:

    Reblogged this on The eDiscovery Nerd and commented:
    Looking forward to seeing all of these posts…

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s