INSERT A FORMULA WITH A 3-D REFERENCE

Each quarterly worksheet calculates the sales totals for a three-month period for each department. You want to insert 3-D references to each worksheet to consolidate the quarterly sales on the Yearly Totals worksheet. Refer to Figure 9.18 as you complete Step 1.

a. Open e09h1Indianapolis_LastFirst if you closed it at the end of Hands-On Exercise 1, and save it as e09h2Indianapolis_LastFirst, changing h1 to h2.

b. Click the Yearly Totals sheet tab. Type Quarterly Total in cell A11. Apply bold, change the font color to Purple, and then click Increase Indent in cell B3.

c. Click cell B3, type = and click the Qtr1 sheet tab, click cell E3 in that worksheet, and then press Ctrl+Enter. Look at the Formula Bar. The formula is ='Qtr1'!E3, where Qtr1 refers to the worksheet, and E3 refers to the cell within that worksheet.

d. Double-click the cell B3 fill handle to copy the formula to the range B4:B11. The formula’s cell reference is relative, so it changes as you copy the formula down the column. The formula in cell B4 is ='Qtr1'!E4.

e. Click cell C3 in the Yearly Totals worksheet, type = and click the Qtr2 sheet tab, click cell E3 in that worksheet, and then press Ctrl+Enter. Double-click the cell C3 fill handle to copy the formula to the range C4:C11. Look at the Formula Bar. The formula is ='Qtr2'!E3, where Qtr2 refers to the worksheet and E3 refers to the cell within that worksheet.

f. Adapt Step e to enter references to the appropriate totals in the Qtr3 and Qtr4 worksheets. Save the workbook.

INSERT A FUNCTION WITH A 3-D REFERENCE

You want to calculate the total annual sales by department. Although you could simply sum the values in the Yearly Totals worksheet, you want to build a function with 3-D references to provide a cross-check that the totals are correct. Refer to Figure 9.19 as you complete Step 2.

a. Click cell F3 in the Yearly Totals worksheet.

b. Type =SUM( You start the 3-D formula with =, the function name, and the opening parenthesis.

c. Click the Qtr1 sheet tab, press and hold Shift, and then click the Qtr4 sheet tab. You grouped the worksheets together so that you can use a common cell reference for the range of cells to sum.

d. Click cell E3, the cell containing the quarterly sales, and press Ctrl+Enter. Look at the Formula Bar. The function is =SUM('Qtr1:Qtr4'!E3). If you select the range B3:E3 in the Yearly Totals worksheet, the status bar shows that the sum is $1,306,949.09, the same unformatted value that displays when you inserted the 3-D formula.

e. Double-click the cell F3 fill handle to copy the formula to the range F4:F11.

f. Apply Accounting Number Format to the ranges B3:F3 and B11:F11. Apply Comma Style to the range B4:F10. Apply the Total cell style to the range B11:F11. With the range B11:F11 still selected, change the column width to 14. Save the workbook.

INSERT A FORMULA WITH A 3-D REFERENCE

Each quarterly worksheet calculates the sales totals for a three-month period for each department. You want to insert 3-D references to each worksheet to consolidate the quarterly sales on the Yearly Totals worksheet. Refer to Figure 9.18 as you complete Step 1.

a. Open e09h1Indianapolis_LastFirst if you closed it at the end of Hands-On Exercise 1, and save it as e09h2Indianapolis_LastFirst, changing h1 to h2.

b. Click the Yearly Totals sheet tab. Type Quarterly Total in cell A11. Apply bold, change the font color to Purple, and then click Increase Indent in cell B3.

c. Click cell B3, type = and click the Qtr1 sheet tab, click cell E3 in that worksheet, and then press Ctrl+Enter. Look at the Formula Bar. The formula is ='Qtr1'!E3, where Qtr1 refers to the worksheet, and E3 refers to the cell within that worksheet.

d. Double-click the cell B3 fill handle to copy the formula to the range B4:B11. The formula’s cell reference is relative, so it changes as you copy the formula down the column. The formula in cell B4 is ='Qtr1'!E4.

e. Click cell C3 in the Yearly Totals worksheet, type = and click the Qtr2 sheet tab, click cell E3 in that worksheet, and then press Ctrl+Enter. Double-click the cell C3 fill handle to copy the formula to the range C4:C11. Look at the Formula Bar. The formula is ='Qtr2'!E3, where Qtr2 refers to the worksheet and E3 refers to the cell within that worksheet.

f. Adapt Step e to enter references to the appropriate totals in the Qtr3 and Qtr4 worksheets. Save the workbook.

INSERT A FUNCTION WITH A 3-D REFERENCE

You want to calculate the total annual sales by department. Although you could simply sum the values in the Yearly Totals worksheet, you want to build a function with 3-D references to provide a cross-check that the totals are correct. Refer to Figure 9.19 as you complete Step 2.

a. Click cell F3 in the Yearly Totals worksheet.

b. Type =SUM( You start the 3-D formula with =, the function name, and the opening parenthesis.

c. Click the Qtr1 sheet tab, press and hold Shift, and then click the Qtr4 sheet tab. You grouped the worksheets together so that you can use a common cell reference for the range of cells to sum.

d. Click cell E3, the cell containing the quarterly sales, and press Ctrl+Enter. Look at the Formula Bar. The function is =SUM('Qtr1:Qtr4'!E3). If you select the range B3:E3 in the Yearly Totals worksheet, the status bar shows that the sum is $1,306,949.09, the same unformatted value that displays when you inserted the 3-D formula.

e. Double-click the cell F3 fill handle to copy the formula to the range F4:F11.

f. Apply Accounting Number Format to the ranges B3:F3 and B11:F11. Apply Comma Style to the range B4:F10. Apply the Total cell style to the range B11:F11. With the range B11:F11 still selected, change the column width to 14. Save the workbook.

INSERT A FORMULA WITH A 3-D REFERENCE

Each quarterly worksheet calculates the sales totals for a three-month period for each department. You want to insert 3-D references to each worksheet to consolidate the quarterly sales on the Yearly Totals worksheet. Refer to Figure 9.18 as you complete Step 1.

a. Open e09h1Indianapolis_LastFirst if you closed it at the end of Hands-On Exercise 1, and save it as e09h2Indianapolis_LastFirst, changing h1 to h2.

b. Click the Yearly Totals sheet tab. Type Quarterly Total in cell A11. Apply bold, change the font color to Purple, and then click Increase Indent in cell B3.

c. Click cell B3, type = and click the Qtr1 sheet tab, click cell E3 in that worksheet, and then press Ctrl+Enter. Look at the Formula Bar. The formula is ='Qtr1'!E3, where Qtr1 refers to the worksheet, and E3 refers to the cell within that worksheet.

d. Double-click the cell B3 fill handle to copy the formula to the range B4:B11. The formula’s cell reference is relative, so it changes as you copy the formula down the column. The formula in cell B4 is ='Qtr1'!E4.

e. Click cell C3 in the Yearly Totals worksheet, type = and click the Qtr2 sheet tab, click cell E3 in that worksheet, and then press Ctrl+Enter. Double-click the cell C3 fill handle to copy the formula to the range C4:C11. Look at the Formula Bar. The formula is ='Qtr2'!E3, where Qtr2 refers to the worksheet and E3 refers to the cell within that worksheet.

f. Adapt Step e to enter references to the appropriate totals in the Qtr3 and Qtr4 worksheets. Save the workbook.

INSERT A FUNCTION WITH A 3-D REFERENCE

You want to calculate the total annual sales by department. Although you could simply sum the values in the Yearly Totals worksheet, you want to build a function with 3-D references to provide a cross-check that the totals are correct. Refer to Figure 9.19 as you complete Step 2.

a. Click cell F3 in the Yearly Totals worksheet.

b. Type =SUM( You start the 3-D formula with =, the function name, and the opening parenthesis.

c. Click the Qtr1 sheet tab, press and hold Shift, and then click the Qtr4 sheet tab. You grouped the worksheets together so that you can use a common cell reference for the range of cells to sum.

d. Click cell E3, the cell containing the quarterly sales, and press Ctrl+Enter. Look at the Formula Bar. The function is =SUM('Qtr1:Qtr4'!E3). If you select the range B3:E3 in the Yearly Totals worksheet, the status bar shows that the sum is $1,306,949.09, the same unformatted value that displays when you inserted the 3-D formula.

e. Double-click the cell F3 fill handle to copy the formula to the range F4:F11.

f. Apply Accounting Number Format to the ranges B3:F3 and B11:F11. Apply Comma Style to the range B4:F10. Apply the Total cell style to the range B11:F11. With the range B11:F11 still selected, change the column width to 14. Save the workbook.

INSERT A FORMULA WITH A 3-D REFERENCE

INSERT A FORMULA WITH A 3-D REFERENCE

INSERT A FUNCTION WITH A 3-D REFERENCE

INSERT A FUNCTION WITH A 3-D REFERENCE

a. Click cell F3 in the Yearly Totals worksheet.

a. Click cell F3 in the Yearly Totals worksheet.

b. Type =SUM( You start the 3-D formula with =, the function name, and the opening parenthesis.

b. Type =SUM( You start the 3-D formula with =, the function name, and the opening parenthesis.

e. Double-click the cell F3 fill handle to copy the formula to the range F4:F11.

e. Double-click the cell F3 fill handle to copy the formula to the range F4:F11.