Rows and Columns

gullible.info sez:

An average Microsoft Excel spreadsheet document has 1,102 rows and 18.2 columns.

I say:

That’s 470 rows and 25 columns. I had 408 sheets whose last cell was A1, i.e. blank. I attribute that to two things: I probably have quite a few workbooks that are just code; Most of the workbooks I get from other people have a Sheet2 and a Sheet3 that are unused. The default number of sheets for a new workbook is three.

I also had 15 sheets whose last cell is IV65536, which is clearly wrong. That’s a typical problem with using the SpecialCells(xlCellTypeLastCell) method, but I’ll just exclude those from the average.

If I don’t count the empty sheets and the “full” sheets, I get:

Mean: 103 rows and 28 columns
Median: 58 rows and 11 columns
Mode: 59 rows and 11 columns

That’s over about 2,000 files. It’s not all the Excel files I have, it’s just all of them in the MyDocuments folder. If you’d like to see your average, I’ve posted the code I used below. It takes a few minutes to run and I had to click a few dialog boxes that asked me to edit links or start an external application.

Sub LastCells()
   
    Dim sro As Scripting.FileSystemObject
    Dim srFolder As Scripting.Folder
   
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
   
    Set sro = New Scripting.FileSystemObject
   
    Set srFolder = sro.GetFolder(“C:Documents and SettingsdkMy Documents”)
   
    GetLastCells srFolder
   
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
   
End Sub
 
Sub GetLastCells(srFolder As Scripting.Folder)
   
    Dim srFile As Scripting.File
    Dim srSubFolder As Scripting.Folder
    Dim wb As Workbook, sh As Worksheet, rLast As Range
   
    For Each srFile In srFolder.Files
        If srFile.Type = “Microsoft Excel Worksheet” Then
            Set wb = Workbooks.Open(srFile.Path)
            For Each sh In wb.Worksheets
                If Not sh.ProtectContents Then
                    Set rLast = sh.Cells.SpecialCells(xlCellTypeLastCell)
                    With ThisWorkbook.Sheets(1).Range(“A65536”).End(xlUp)
                        .Offset(1, 0).Value = wb.FullName
                        .Offset(1, 1).Value = rLast.Address
                        .Offset(1, 2).Value = rLast.Row
                        .Offset(1, 3).Value = rLast.Column
                    End With
                End If
            Next sh
            wb.Close False
        End If
    Next srFile
   
    For Each srSubFolder In srFolder.SubFolders
        GetLastCells srSubFolder
    Next srSubFolder
       
End Sub
Posted in Uncategorized

10 thoughts on “Rows and Columns

  1. I used Excel 2007, so I had to respond to a lot more prompts than you did. I eventually got a file that couldn’t be opened, and the code ended. My results for 230 non-empty sheets:

    Avg 382.6 rows and 10.0 cols
    Median 30 rows and 6 cols
    Mode 8 rows and 2 cols
    Max: 31,681 rows, 256 cols

    My results are not at all typical. Most of these are not “real” workbooks. They are files that I’ve used for book examples or for testing purposes.

    If anyone tries this with Excel 2007, change this statement:

    If srFile.Type = “Microsoft Excel Worksheet” Then

    To this:

    If Left(srFile.Type, 22) = “Microsoft Office Excel” Then

    Also, you’ll need to add a reference to Microsoft Scripting Runtime

  2. How would anyone ever get a wide enough sample to feel confident about the “average” Excel spreadsheet?

    Here is my contribution…
    155 Files (blank sheets removed)
    793 Sheets
    ——–
    RowsColumns
    Average585.210.5
    Max40370256
    Median457
    Min11
    Mode142

  3. I don’t think these averages make any sense. What matters more is the number and complexity of unique formulae.

  4. Here is my results
    (I had to rewrite code as John wrote)

    466 Files (blank sheets removed)

    ——–
    Avg 188.4 rows and 10.1 cols
    Median 22,5 rows and 7 cols
    Mode 13 rows and 3 cols
    Max: 30,720 rows, 231 cols

  5. I ran a scan (essentially a DOS batch file) which listed every spreadsheet in one department of a bank I did work for. There was about 7,500 spreadsheets. And while I didn’t get rows and columns, I did get file size and last modified date.

    The vast majority of the spreadsheets were in the few-hundred kilobytes to a couple of meg range. However there were lots of minnows (100meg. There were a couple of 150 meg moby dicks.

    I’d also agree with dermot – the averages don’t necessarily make sense or are even meangingful. Throw in one or two mody dick’s or a few hundred minnows and the average goes out the window. And what would deem a decent sample size?

    The industry in which the spreadsheet is created also makes a huge difference on what kind of average is deemed reasonable.

  6. “Avergae 1,102 rows and 18.2 columns.”….wonder why MS botherd about increasing the number of Rows and columns

  7. 344 non-blank sheets
    Average 444.2 rows and 10.3 cols
    Mode 17 rows and 10 cols
    Median rows 48 rows and 9 cols
    Max 10141 rows and 256 cols
    Min rows 2 rows and 1 cols

  8. I used this code.
    The method application filesearch is much more convenient, because it automatically selects all the excelfiles (if you want it) and automatically takes all subfolders into account (if you wish). Unfortunately the method doesn’t seem to exist anymore in Excel 2007.

    Sub test()
      With Application
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .DisplayAlerts = False
      End With
     
      For Each fl In CreateObject(“scripting.filesystemobject”).getfolder(“E:OF”).Files
        If Right(fl.Name, 4) = “.xls” Then
          For Each sh In GetObject(fl.Path).Worksheets
            If Not sh.ProtectContents Then
              With sh.Cells.SpecialCells(xlCellTypeLastCell)
                c0 = fl.Path & “!” & sh.Name & “|” & .Address & “|” & .Row & “|” & .Column
              End With
              With Workbooks(2).Sheets(1).Range(“A1:D1”)
                .Offset(.CurrentRegion.Rows.Count) = Split(c0, “|”)
              End With
            End If
          Next
          Workbooks(fld.Name).Close False
        End If
      Next
       
      With Application
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
        .DisplayAlerts = True
      End With
    End Sub


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

Leave a Reply

Your email address will not be published. Required fields are marked *