Microsoft Office Tutorials and References

In Depth Information

**Formula-bar formatting**

=SUM(Sheet2:Sheet13!B5) adds all the values in cell B5 on all the worksheets between and

including Sheet2 and Sheet13.

You can also use 3-D names in formulas. For more information, see “Creating

threedimensional names” earlier in this chapter.

To construct this three-dimensional formula, follow these steps:

In cell B5 of Sheet1, type
=SUM(
.

1.

Click the Sheet2 tab, and select cell B5.

2.

3.
Click the tab-scrolling button for scrolling right (located to the left of the worksheet

tabs) until the Sheet13 tab is visible.

Hold down the Shift key, and click the Sheet13 tab. All the tabs from Sheet2 through

Sheet13 turn white, indicating they are selected for inclusion in the reference you are

constructing.

4.

Type a closing parenthesis, and then press Enter.

5.

For more information about group editing, see “Editing multiple worksheets” in Chapter 8.

You can use the following functions with 3-D references: SUM, AVERAGE, AVERAGEA,

COUNT, COUNTA, MIN, MINA, MAX, MAXA, PRODUCT, STDEV, STDEVA, STDEVP, STDEVPA,

VAR, VARA, VARP, and VARPA.

Formula-bar formatting

You can enter spaces and line breaks in a formula to make it easier to read in the formula

bar without affecting the calculation of the formula. To enter a line break, press Alt+Enter.

Figure 12-21 shows a formula that contains line breaks. To see the complete formula in the

formula bar, click the Expand Formula Bar button (the one with the chevron) at the right

end of the formula bar.

Figure 12-21
You can enter line breaks in a formula to make it more readable.