Combine and consolidate with a function

This shortcut works best when you are consolidating sheets that have the same structure (the same columns and rows).

1. Select where the formula will go, press =.
2. Type the function name and the opening parenthesis, for example: =SUM(.
3. Select the sheets that contain the data:
   • Click the first sheet's tab
   • SHIFT+click the last sheet's tab, or CTRL+click non-adjacent tabs
4. You have now grouped worksheets.
5. Select the cell or range of cells that you want to include in the formula.
6. Type the closing parenthesis and press ENTER.
7. You can fill that formula down by dragging its fill handle. Then, while the column is still selected, drag the fill handle across.

Note that the first and last sheets used in this method must stay the same. You can insert sheets between the first and last sheet, and you can move or rename sheets between the first and last sheet, but the names of the first and last sheet are pretty much 'hardwired' in the functions.

To work around this limitation, you can try one of the following:

• Create "bookend" sheets as the first and/or last sheet. These act as bookends and then the sheets between the "bookends" can be moved, renamed, added or deleted as necessary. You can even hide the bookend sheets (Format → Sheet ? Hide).
• If you need to add a new sheet before the first sheet or after the last sheet, do a giant find-and-replace, searching all formulas for instances of the original first/last sheet name and replacing with the new sheet's name.