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.
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
This is great. It’s always interesting to see what the “reality” of some of our factoids are.
-Kyle @ Gullible.info
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
I want to see that worksheet that has 18.2 columns ;-)
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
I don’t think these averages make any sense. What matters more is the number and complexity of unique formulae.
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
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.
“Avergae 1,102 rows and 18.2 columns.”….wonder why MS botherd about increasing the number of Rows and columns
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
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.
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