| 1 | Start Excel. Download and open the file named Exp19_Excel_Ch09_ML1_Expenses.xlsx. Grader has automatically added your last name to the beginning of the filename. | 0 |
| 2 | You want to enter a title and apply a cell style to three worksheets at the same time.
Group the April, May, and June worksheets. Type Savings, Income, & Expenses in cell A1. Select the range A1:C1 and apply the Heading 1 cell style. | 9 |
| 3 | With the worksheets grouped, you want to calculate the Ending Savings Balance.
In cell C6, add the Beginning Savings Balance to the Monthly Savings Added. | 9 |
| 4 | With the worksheets grouped, you want to unlock a range so that it can be edited.
Unlock cell C9 and the range B12:B20 in the grouped worksheets. Then, ungroup the worksheets. | 9 |
| 5 | Three worksheets should have 3-D references to other worksheets.
Display the May worksheet. In cell C4, create a formula with a 3-D reference to cell C6 (the
Ending Savings Balance) in the April worksheet.
Display the June worksheet. In cell C4, create a formula with a 3-D reference to cell C6 (the
Ending Savings Balance) in the May worksheet.
Display the Qtr 2 worksheet. In cell C4, create a formula with a 3-D reference to cell C6 (the
Ending Savings Balance) in the June worksheet.
| 10 |
| 6 | You are ready to build functions with 3-D references to sum data from all three months on the Qtr 2 worksheet.
With the Qtr 2 worksheet active, click cell C9 and insert the SUM function with a 3-D reference to total the salary amounts for all three months. Copy the function in cell C9 to cells C21 and C23. | 10 |
| 7 | Next, you want to insert a SUM function with a 3-D reference on the Qtr 2 worksheet.
Click cell B12 and insert the SUM function with a 3-D reference to total the rent amount
for all three months. Copy the function in cell B12 to the range B13:B20. | 10 |
| 8 | To ensure consistency among worksheets, you will group worksheets and apply formatting.
Display the April worksheet, group all four worksheets, and select the range A8:C23. Fill the formats only across the grouped worksheets to copy the font formatting, indents, and number formatting. Ungroup the worksheets. | 4 |
| 9 | The Qtr 2 sheet contains cells containing April, May, and June text. You will insert hyperlinks to the respective worksheet data.
Select cell E2 containing April, insert a hyperlink to cell C23 in the April worksheet, and include
the ScreenTip with the text April balance.
Select cell E3 containing May, insert a hyperlink to cell C23 in the May worksheet, and include the ScreenTip with the text May balance.
Select cell E4 containing June, insert a hyperlink to cell C23 in the June worksheet, and include the ScreenTip with the text June balance.
Click each cell to test the hyperlinks and correct any errors. | 11 |
| 10 | Use the data in the four worksheets to enter data in the range E7:E12 in the Analysis section to provide either a text or a number that relates to the labels in the range F7:F12. | 6 |
| 11 | You want to create a data validation rule to restrict the type of data the user enters.
Display the April worksheet and create a data validation rule in cell B20. Allow decimal values that are less than or equal to $100. Create the input message title Miscellaneous Expense and input message The maximum miscellaneous expense is $100. (including the period). Create an error alert with the Stop style, error title Invalid Data, and error message You must enter a value less than or equal to $100. (including the period). Test the rule by trying to enter 500. The rule should prevent you from entering that value. Click Cancel to revert to the original $100 value. | 10 |
| 12 | Create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side of all worksheets. | 4 |
| 13 | Protect all four worksheets without a password to enforce the locked cells. | 8 |
| 14 | You want to indicate that you finalized the workbook.
Mark the workbook as final.
Note: Mark as Final is not available in Excel for Mac. Instead, use Always Open Read-Only on the Review tab. | 0 |
| 15 | Save and close Exp19_Excel_Ch09_ML1_Expenses.xlsx. Exit Excel. Submit the file as directed. | 0 |
