MOS19_XL_PROJ2-Disbursements 1.1

1 Start Excel. Download and open the file named Student_MOS19_XL_PROJ2_Disbursements.xlsx. Save the file as Last_First_MOS19_XL_PROJ2_Disbursements. 0
2 On the Disbursements worksheet, replace all three occurrences of Expenditures with Disbursements. 6
3 On the Disbursements worksheet, select cell A1 and then insert a new row above row 1. 3
4 On the Expenses worksheet, copy the text in cell A1 and then paste to cell A1 of the Disbursements worksheet. 3
5 On the Disbursements worksheet, use the Format Painter to copy the formatting in cell A12 to the range A3:G3. Save the file. 3
6 Merge & Center the range A1:G1. 3
7 AutoFill the text in B3 to the range C3:E3. AutoFill cell B12 to the range C12:F12. Copy the range B3:E3 on the Disbursements worksheet to C4:F4 of the Expenses worksheet using Paste Values. 9
8 On the Disbursements worksheet, wrap the text in the range F3:G3. Apply Middle Align and Center to the range A3:G3. 6
9 Apply the Accounting Format with no decimals places to the range B4:F4. Apply the Total cell style to the range B12:F12. 8
10 Apply the Comma number style with no decimal places to the range B5:F11. Apply the Percent Style with one decimal place to the range G4:G11. 8
11 Apply the Orange, Accent 2, Lighter 80% fill color to the range A3:G3. Save the file.
3
12 On the Disbursements worksheet, in the range H4:H11, insert Line Sparklines for the 1st, 2nd, 3rd, and 4th Quarter data. Change the Sparkline Style to Brown, Sparkline Style Accent 1, Darker 25%, and show the High Point. 12
13 On the Disbursements worksheet, apply Gradient Fill Blue Data bars to the Department Totals. On the Expense Table worksheet, clear the conditional formatting from the range C33:F33. 8
14 On the Expenses worksheet, apply Icon Sets 4 Arrows (Colored) Conditional Formatting to the totals. Delete row 3. Clear the formatting in cell B4.
8
15 On the Expenses worksheet, group the data in A3:F31 by row. Subtotal each Department and add subtotals to the 1st, 2nd, 3rd, and 4th Quarters. Select A41:F41 and shift the cells down one row.
10
16 On the Expense Table worksheet, rename the table Expenses. On the Disbursements worksheet, name the range B4:B11 Qtr_1st. Save the file. 10
17 Ensure the worksheets are in the following order: Disbursements, Expenses, Expense Table. Save and close Last_First_MOS19_XL_PROJ2_Disbursements.xlsx. Exit Excel. Submit the file as directed. 0