I was on the Excel team when this was designed.
If you remember the version of Excel right before you could have multiple worksheets, that version had a concept, IIRC called WORKSPACES, that let you link multiple worksheets into a workspace which could be opened and closed together.
The idea was that if you had 7 spreadsheets, one macro sheet, and four chart sheets that you always used together, you could link to all of them in a "workspace" which could be opened all at once. The spreadsheets still lived in their own files.
This was complicated and confusing and not used by a lot of people, but a lot of users still had 7 spreadsheets, one macro sheet, and four chart sheets that they wanted to use together.
In the meantime, Lotus introduced "3D worksheets" and was telling everybody that this was the next thing.
So the team was trying to design something that was the superset of workspaces and 3D worksheets, and that's where the design came about for workbooks with multiple tabs.
A workbook was stored in an OLE DOCFILE which is a compound document that allows you to put "a whole file system in a single file". Old, existing Excel code that only knew about worksheets could still work perfectly, and just thought it was loading or saving a worksheet, even though it was actually loading or saving a single piece of a compound document.
The program managers and developers on that feature (mainly Craig Unger) tried various approaches. 1 sheet default was logical but didn't "advertise" the possibility of multiple sheets. Using a large number of sheets (16 or 256) would be similar to how rows and columns work (you always get a lot of blank ones) but was slow and memory-intensive and annoying to people who didn't need all those sheets and then had to delete the ones they weren't using. 3 was a nice compromise: it showed people that the feature existed without being too burdensome. And of course every decision has to have an option in the options dialog, so you can change the default number of new sheets in a workbook.