Ranges on Multiple Worksheets

Most everyone knows that if you want to refer to a range of cells, you simply specify the beginning and ending point of the range and then separate those points by a colon. For instance, the following formula would return the sum of all cells in the range A1 through C4:

=SUM(A1:C4)

You may not know, however, how you can refer to the same cell or range of cells on a range of multiple worksheets in your workbook. For instance, you may want a cell to return the sum of each cell A1 on the first three worksheets in your workbook. If the worksheets are named Sheet1, Sheet2, and Sheet3, then the formula would appear as follows:

=SUM(Sheet1:Sheet3!A1)

Similarly, if you wanted the sum of all cells in the range A1 through C4 on each of the same worksheets, you would use the following formula:

=SUM(Sheet1:Sheet3!A1:C4)

At times this notation can be a bit difficult to remember. You can easily use the mouse to build such a range by following these steps:

1. Select the cell in which you want to enter your formula.
2. Enter the equal sign and the first part of the function, followed by the opening parenthesis. In the examples given above, you would enter =SUM(.
3. Click on the sheet tab of the first sheet in the range.
4. Hold down the Shift key as you click on the sheet tab of the last sheet in the range.
5. Use the mouse to select all the cells in the range on the visible worksheet.
6. Press Enter.

Your formula should now be complete, with the desired range in place.