Did you ever make a formula like the one below?  After reading this post, I hope you will never make a formula in this way again.

It is quite common that we hold many worksheets of identical structure in a workbook; and have a summary worksheet which is basically the total of all worksheets in the workbook.  As you see in the example below, the workbook holds daily sales record.  In total, there are 32 worksheets – one Total and 1 to 31 for each day in a month.

The lengthy formula can be replaced by:

=SUM(‘1:31’!L8)

Is it simple and easy to construct and read?

Literally, =SUM(‘1:31’!L8) tells Excel to SUM the value in cell L8 of all worksheets from ‘1 to ’31 (both inclusive).  You may refer to “SUM vs. +” for the benefits of using SUM instead of “+”.

You may input the formula directly in the formula bar.  If you want to use mouse to point to the reference you need, follow the steps below:

Suppose you want to sum the value in L8 in all the worksheets, as in the example shown before.

1) In cell L8, type =sum(

Now you are ready to input the first number or reference

 2) Use your mouse to select Sheet “1”

Note the change in the formula bar

3) PRESS the SHIFT Key and select Sheet “31” by mouse.  Again note the change in the formula bar, Excel writes part of the formula for you.

4) The last part is to input the cell reference.  In this case, it’s L8. (you may input in formula bar directly, or select cell L8 with your mouse)

After you create the formula in the right place, you may simply copy it down and across to complete the summary report.

Tips: One of the advantages of using SUM is the flexibility of add new record inbetween. It applies to 3D SUM too.

In screenshot below, a new spreadsheet “1 (Special Event)” of identical structure is placed right after worksheet “1”

In the worksheet “Total”, the SUM formula automatically include the new data.

IMPORTANT: All worksheets MUST be IDENTICAL in structure in order to get the desired result.