Overloading Excel with worksheets
How many worksheets can a workbook support in Excel?
According to the ‘Excel specifications and limits’ webpage, the total number of sheets permissible is ‘limited by available memory’.
What does this actually mean in practical terms, though?
I decided to conduct a mini experiment to see how many blank sheets Excel could handle in my setup.
In case you didn’t know, I am a long-standing Mac user with Excel for Microsoft 365 installed on Windows virtually via Parallels Desktop.
Here are the specs:
- 16″ MacBook Pro 2021
- macOS Sonoma
- Apple M1 Max
- CPU: 10-core
- GPU: 32-core
- RAM: 64 GB
- Storage: 2 TB SSD
Parallels Desktop configuration
- Windows 11
- Assigned CPUs: 4
- Assigned RAM: 16 GB
In a new workbook, I ran this VBA code, which uses a loop to insert x number of worksheets:
Sub InsertWorksheets()
' Insert 5451 new worksheets
For i = 1 To 5451
Sheets.Add
Next i
End Sub
You guessed it! 5451 was the maximum reliable threshold, although I did not magically guess this.
In fact, it took me a dozen or so attempts to reach that number, often waiting many minutes after running the code to add a batch of 100–1000 worksheets. I had a few hiccups along the way due to trying too much at once, but I eventually got there.
Indeed, the tipping point was 5452 sheets — that’s when Excel hung by a thread.
Despite initial promise from managing to add an extra sheet or two, attempting to save the file resulted in a near-immediate crash every time.
How much bloating did all these blank sheets cause?
When I saved the .xlsm workbook, the file size was recorded as 4.4 MB. To put that into perspective, a fresh workbook with a single sheet is just 9 KB.
It’s reasonable to suggest that had I filled up a few sheets with lots of data, formulas, and conditional formatting, crashing would have occurred at a lower number. How much lower — I’m not sure.
I don’t expect anyone else to give this a go, but if you do, please report back with your result. 😁
