Excel in the News

When Excel is in the news, it’s never a good thing.

via Above The Law

According to the various affidavits (posted below) the Cleary first-year did not notice that the 179 contracts were marked as “hidden” in Excel, and certainly didn’t notice that those entries became “un-hidden” when he globally reformatted the document.

If you’re buying any troubled assets in the near future, be sure to use the auditing feature in any spreadsheets you get. Show the Formula Auditing toolbar and click the Trace Dependents button. The difference between a reference to a visible and hidden cell is shown below.

Posted in Uncategorized

16 thoughts on “Excel in the News

  1. Hi Dick,

    I’m using a review sub to identify these hidden columns/rows which I’d like to share with you. I had the references to variables in my addin removed and replace (hopefully no errors made there).

    Cheers
    Andreas

    Sub FindHiddenSections()
    ‘ Andreas Emmert, July 2007

    Dim c As Range
    Dim shcounter, i, j, k As Integer
    Dim Sh As Worksheet
    Dim n As Name
    Dim SearchSheet As Boolean
    Dim adresse(10000), arbeitsblatt(10000), zellinhalt(10000), zellformel(10000), wsProtected(100) As String
    Dim maxShNumber As Integer
    Dim BaseFilePath, BaseFileName As String
    Dim SearchSheetArray As Variant

    maxShNumber = ActiveWorkbook.Worksheets.Count

    If ActiveWorkbook.MultiUserEditing = True Then
    MsgBox “Workbook is in MultiUserEditing Mode. Sub will abort!”
    Exit Sub
    End If

    For Each Sh In ActiveWorkbook.Worksheets

    If Sh.ProtectContents = True Then
    MsgBox “Worksheet ” & Sh.Name & ” is protected.” & Chr(13) & _
    “Sub will abort!”
    Exit Sub
    End If

    Next Sh

    For Each Sh In ActiveWorkbook.Worksheets

    Sh.Outline.ShowLevels rowLevels:=8
    Sh.Outline.ShowLevels columnLevels:=8

    With Sh

    LastRow = .Cells.SpecialCells(xlLastCell).Row
    LastColumn = .Cells.SpecialCells(xlLastCell).Column

    For j = 1 To LastRow
    If .Rows(j).RowHeight

  2. some of the code has been cut off (picking up again):

    For j = 1 To LastRow
    If .Rows(j).RowHeight

  3. some of the code has been cut off (picking up again):

    For j = 1 To LastRow
    If .Rows(j).RowHeight ‘enter less sign here’ 5 Or .Rows(j).Hidden = True Then ‘ Adjust Row Height here

    i = i + 1
    adresse(i) = .Rows(j).Address
    arbeitsblatt(i) = .Rows(j).Worksheet.Name

    End If
    Next j

    For j = 1 To LastColumn
    SpalteBuchst = Columns(j).Address(False, False)
    If .Columns(SpalteBuchst).ColumnWidth ‘enter less sign here’ 3 Or .Columns(SpalteBuchst).Hidden = True Then ‘ Adjust Column Width Here

    i = i + 1
    adresse(i) = .Columns(j).Address
    arbeitsblatt(i) = .Columns(j).Worksheet.Name

    End If
    Next j

    End With

    Next Sh

    BaseFilePath = ActiveWorkbook.Path
    BaseFileName = ActiveWorkbook.Name

    Workbooks.Add

    ActiveSheet.Name = “Hidden Areas”
    ActiveSheet.Cells(1, 1) = “Worksheet”
    ActiveSheet.Cells(1, 2) = “Address”
    ActiveSheet.Cells(1, 3) = “Hyperlink”

    For j = 1 To i
    ActiveSheet.Cells(j + 1, 1) = arbeitsblatt(j)
    ActiveSheet.Cells(j + 1, 2) = adresse(j)
    If Not arbeitsblatt(j) = “” Then
    ActiveSheet.Cells(j + 1, 3) = “=HYPERLINK(“”[” & BaseFilePath & “” & BaseFileName & “]'””&RC[-4]&””‘!””&RC[-3],””Klick mich””)”
    End If
    Next

    With ActiveSheet
    .Tab.ColorIndex = 3 ‘ Color Tab
    .Columns(“A:C”).EntireColumn.AutoFit
    With .Range(“A1:C1?)
    .Interior.ColorIndex = 11
    .Interior.Pattern = xlSolid
    .Font.ColorIndex = 2
    End With
    End With

    Exit Sub

    Errorhandler:
    MsgBox “Your Error Information here”
    Exit Sub

    End Sub

  4. Hi Dick,
    I think this is a good example of how poor excel is at auditing, if you didnt know better, what would that picture mean, I mean, it’s not excatly clear what going on is it? In fact I think the biggest clue theres somthing amiss is the A-C column heading?

  5. How about this as a simple check for hidden cells on a sheet:

    Sub HiddenCells()
        Dim allCell, visCell
        visCell = Cells.SpecialCells(xlCellTypeVisible).Count
        allCell = Cells.Count
        MsgBox “Sheet has “ & allCell – visCell & ” hidden cells”
    End Sub
  6. I understand that Excel is designed for the novice user (though the power user and Professional is well taken care of), but Excel alone is not at fault here. I’ve noticed many times in the past few years that proficiency in Excel (and indeed, MS Office as a whole) are taken for granted, and no emphasis on continued learning is ever demanded. Many companies spend millions of dollars per year maintaining their CAD systems, databases, and network and offer no such support for any Office or clerical application. Executives would be horrified to realize just how much sensitive or “production” data is stored in spreadsheets; if they did, I think we’d see a greater emphasis on skills and proficiency.

  7. What makes Matthew believe executives don’t know there’s lots of data stored in spreadsheets? Even if they didn’t, why would that in itself not be negligence? And if executives are already negligent, what reasons are there to believe they’d correct that?

    There’s no training budget for Office applications because the AVERAGE return on investment is insufficient to justify it. Catastrophic spreadsheet errors are very rare, and usually happen when there are no reality checks, as was the case with Barclays. Training won’t help when deadlines are unreasonable.

  8. Fzz is right, it’s the flaw of averages.
    Managers don’t recognise the cost of the rare event, and just take the risk.
    The discussion in Above the Law has colorful arguments for all sides.

    It’s a tough sell for us in Excel training, Eusprig, SpreadsheetSafe, etc.

    My own XLTest addin of course reports and unhides hidden rows, columns, data, etc, but knowing that you might need to take that action takes some awareness.

    P.

  9. fzz: “There’s no training budget for Office applications because the AVERAGE return on investment is insufficient to justify it.”

    But if you look around in the offices and see how many people struggle and do their work highly inefficiently because they never had basic training in how to properly use the product, I can’t help thinking this is a (very) wrong assumption.

  10. JPK – I totally agree.
    But then you come up against a very real cost (training/time out of office) v an unknown unproven hard to measure possible productivity benefit.
    Most people seem to prefer to measure what is easy, not what is important

  11. From an Excel perspective, this strikes me as more of a database issue than formula auditing. However, the total universe of assets should have been counted and compared to source materials.

  12. First, at least half of Office users use Word and Outlook predominantly, PowerPoint sometimes, Excel almost never for anything more than list creation and sorting (and usually only for lists that don’t fit on a single page in Word).

    My own experience having worked for financial services companies is that it’s more effective to have one or two people who know the applications everyone needs to use, and the one or two local experts would provide ad hoc advice and assistance to the others. Training for all is a waste of time and money for all the Office applications. Most people will soon forget most of the tricks they’re shown in training classes, and fewer than 10% will adopt systematic ongoing self-learning.

    The other half of Office users range from using Excel workbooks other people created to those FEW who create spreadsheets themselves either that other people would use and/or on which their companies rely for key financial reporting or decision making. From my own experience, I’d put that last group at fewer than 15% of all Office users.

    In the case at hand, there may be a comment in the article that law school students should be taught Excel, but the reality is that law firms are run by senior partners, and neither they nor the less senior partners use Excel. Why should they pay for training none of the associates who become partners would need? That includes an indirect unwillingness to let law schools use formal instructional time on matters perceived to be only distantly applicable to the practice of law. As for law school students themselves, I doubt they’d be demanding even more classroom and homework hours.

    Even if lawyers specializing in bankruptcy and/or financial filings were required to complete additional formal training in accounting and spreadsheet modeling, extremely tight deadlines render any amount of training purposeless. There’s also the question whether X hours of Excel training would have touched on reformatting which was the problem in this case. You experts could design a 10 hour training course focused only on gotchas, and I’ll give you another 10 common mistakes you didn’t include. At some point only experience and multiple, unrushed levels of review are the ONLY reliable ways to spot and stop most errors. Training will only allow most Excel users to make more errors more quickly. I fail to see the ROI.

  13. I’ve read discussion at Above The Law carefully and the case seems to be more complex. Technically the problem was not only in hidden entries (rows). From declarations of Cleary associates – first-year: “Some of the rows of the original Excel spreadsheet were spaced too close together or too far apart, making it difficult to read when printed or converted to PDF format. … I also removed certain columns that I understood were not needed on the final PDF document.” and second-year: “I opened the spreadsheet and saw only Y, for Yes, in each of the rows in Column Z, labeled Critical. … The hidden rows each contained N, for No, in Column Z, labeled “Critical.” … The spreadsheet also contained several additional rows designated N in Column Z that had not been hidden”. As understood from Barclays Relief Motion all (both “hidden” and “unhidden”) entries designated N were included in the list of designated contracts “but Barclays had in fact expressly declined to designate as such”.
    So tracing possible hidden rows or columns will not prevent mistakes unless we make a tool to find all mistakes that users can make.

    My conclusions (not sorted in any specified order):
    1. The users should be trained what are basic rules of preparing the data (like data of same kind, no empty columns between), no gotchas or tricks. It will prevent mistakes better than one or two expert users per company (they can’t handle all spreadsheets, can they?).
    2. The old rules like: Think what you are doing. Check your work. When no applied even Sarbanes-Oxley Act, EUSPRIG and ISO 17799 will not help.
    3. Time is necessary to do things well. When you need do things in short time you have to use a model/program or experience (needs time consumed before) or an expert. But an expert needs some time, too.
    4. Users think they know how to use Excel. And they are wrong. Especially when they think it’s easy and can handle something with Excel. Do you call an expert when you need something well done? I do. I can paint my room or use some plumber’s tools but when it is CRITICAL I leave the place to people who can make better use of the same tools. Even if I have to pay.
    5. Cleary Gottlieb case – they handled a billion dollar deal. Do they bill so little for services they can’t afford an expert? MBAs of associates and other qualifications were not enough to use Excel or paint brush (see previous point).
    6. It was a sum of mistakes and negligence done at time pressure. But when there is negligence, there is also breach of duty, doing below the level of competence expected. We say about lawyers and accountants completing “deals with far-reaching effects on the global financial markets”. But in Barclays Relief Motion it seems to become a point to be excused rather than to be required of higher level of competence and proper care.

  14. fzz: I didn’t mean to imply everyone should be tought everything, but at the very least teach the regular users how to use the product they need properly.

    This may avoid people spending four days a month building financial reports by clicking, cutting, pasting and re-typing, instead of creating a more robust and far more efficient reporting file in which they just need to refresh the queries and pivots once every month and be done with it.

  15. It’s still a matter of PERCEIVED return on investment – it’s just not there. Besides, using the example of report generation, sound automation requires the ability to think procedurally and express procedures literally so a computer can understand and act on them rather than just the ability to follow procedures. The former ability is rare among office workers, so providing training for the tools to exercise that ability would be wasted on most office workers.

    I think Microsoft realizes this. Hence the ribbon UI, the interface for users certain never to become experts.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.