Rows and Columns
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 Settings\dk\My 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
Kyle:
This is great. It's always interesting to see what the "reality" of some of our factoids are.
-Kyle @ Gullible.info
24 January 2008, 10:36 amJ-Walk:
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
24 January 2008, 11:25 amMichael:
I want to see that worksheet that has 18.2 columns
24 January 2008, 1:59 pmJim Cone:
How would anyone ever get a wide enough sample to feel confident about the "average" Excel spreadsheet?
Here is my contribution...
24 January 2008, 4:57 pm155 Files (blank sheets removed)
793 Sheets
-----------
Rows Columns
Average 585.2 10.5
Max 40370 256
Median 45 7
Min 1 1
Mode 14 2
dermot:
I don't think these averages make any sense. What matters more is the number and complexity of unique formulae.
24 January 2008, 10:05 pmPetLahev:
Here is my results
(I had to rewrite code as John wrote)
466 Files (blank sheets removed)
-----------
25 January 2008, 3:14 amAvg 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
Marcus:
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.
25 January 2008, 10:51 amsam:
"Avergae 1,102 rows and 18.2 columns."....wonder why MS botherd about increasing the number of Rows and columns
27 January 2008, 4:39 amTony:
344 non-blank sheets
28 January 2008, 10:24 amAverage 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