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.
Another nice tell is when most cells in a column have a formula calculating the value, but a few have the value typed in.
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!).
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:
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!!!!
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.
Reblogged this on The eDiscovery Nerd and commented:
Looking forward to seeing all of these posts…
Thank you Joshua!
Pingback: Scared Straight? Reviewing Excel Files in the Wake of Wells Fargo | Excel Esquire
Pingback: Excel Roundup 20150504 – Contextures Blog