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. JPK -JKP d’oh

12. 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.

13. 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.

14. 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.

15. 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.

16. 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.