Recently, I was working on an extra-large spreadsheet for an important client. I showed my spreadsheet to Grace Gridwright.
Grace, this is a really big spreadsheet. It seems like there should be some way to simplify it, but I am not sure how. Also, I have been having trouble auditing it.
"Boss, you're not going to like this."
I'm not going to like what?
"You're using multiple sheets."
Yes, a wonderful feature.
"No."
Beg your pardon?
"Boss, multiple sheets are bad."
Huh?
"Boss, multiple sheets are very bad."
Wow, you better have a good argument on this one. Are you saying that multiple sheets are always bad?
"Actually, I have a long list of arguments why multiple sheets are bad. Sometimes they do make sense, but many spreadsheet writers overuse them, putting bits of information all on different sheets, posterizing all the data. "
All right. Let's hear your arguments.
 |
"The main reason to put your spreadsheet on a single sheet is so that your client has the chance to see everything at a glance. Perhaps your client will set View at 25% to get a bird's-eye view of the structure. At least he has a chance. If you put the spreadsheet on multiple sheets, he is forced to clicky-click to see it all, and then only in pieces." |
|
"Multiple sheets breed spurious cells. You put an important input on Sheet 1. On Sheet 2, you can't see the input, so you just add a dumb reference to it. What a waste of time!" |
| Here is an example of a spurious cell. D8=B24. It just points to another cell. | 
|
| Here is another example. The writer can't see the "Tznlir %" from
the Input sheet, so he copies it here with the useless formula, G8=Input!G15.
Now his reader is going to have more goo to look at.
The spreadsheet is bigger because the writer used multiple sheets! |
|
|
"Multiple sheets force the client to use the mouse. Boss, do you know the keys to shift sheets?"
No, I guess not. I just click on the Sheet tab. "Yeah, you and everyone else. But some of us hate the mouse. The secret keystrokes are Ctrl-PgUp and Ctrl-PgDn." Oh, thanks. Now that I know the keystrokes to change sheets, I can use multiple sheets. "Yeah, funny. What about your client?" |
|
"The search function does not work on multiple sheets by default.
If you click Edit, Search, it won't find anything on a separate sheet."
"Of course, if you know the secret trick (select all the sheets while pressing Ctrl), you can make it work. Again, this is not something your client is likely to know. |
 |
"The auditing toolbar fails with multiple sheets. Look at the example of "Tznlir %" above. You click "show dependents" or "show precedents," but if it's on a different sheet, you just get a dumb it's-off-the-sheet icon. The point of the audit toolbar is to show the dependents or precedents visually. |
 |
"Writers often leave blank sheets in the workbook, like putting blank pages in a business report.
The client is forced to click on the blank page to see if anything is there,
like Sheets 7, 8, and 9 below.
A waste of time. So set the default number of sheets to one (select Tools, Options, General, Sheets in new workbook). If you insist on using multiple sheets, at least delete the empty ones. |
 |
"Formulas get longer. What should be short gets disgustingly long, just because the formula has to reference the name of a different sheet. Again, the whole spreadsheet is big because you are using multiple sheets." |
OK, I'm sold. How do I put this monster on one sheet?
First you have to find the end! Move your cursor to the last cell on the sheet for which memory has been allocated, by selecting Edit, GoTo, Special, Last cell. You can use this to select all the cells on one sheet. Here's how.
- Select all of Sheet2: Move the cursor to cell A1 on Sheet2, then press Shift-End-Home. Now all the content of Sheet2 has been selected.
- Press Ctrl-X to cut it.
- Go back to Sheet1. Press End, then Home. This will move the cursor to the lowest right cell of the active block.
- Press Down, then End Left. This will move the cursor to the A column, but below everything else in the sheet.
- Press Ctrl-V to paste Sheet2 into Sheet1.
- Do the same thing with the other sheets. When you are done, you should have the entire workbook in one sheet. The Auditing Toolbar will do a much better job for you now, and you have a chance to see everything at once!
Then substitute out those spurious cells.