Carmelo Miraglia is an executive consultant at
Tallridge Regional Medical Center. Carmelo is building a workbook that
summarizes income and expenses for the organization.
Change the theme of the workbook to Office.
On the Departments
worksheet, change the width of columns C through H to 12.00 characters.
Change the height of row 1 to 21 points.
Merge and center the contents of the range
Format the merged range A2:J2 as described
Apply the 40%
– Accent 5 cell style.
Change the font size to 14.
Enter the values shown in Table 1 into the
corresponding cells in the range A5:E5.
1: Data for the Range A5:E5
Format the range A5:J5 as described below:
Center cell contents.
Change the font size to 11 pt.
Change the background color to Blue, Accent 5, Lighter 40% (9th
column, 4th row of the Theme Colors palette).
Text to the text in each cell.
Select the range A6:J14 and then add a White,
Background 1 border to all sides of each cell.
Select the range A14:J14 and then add a thin top
border to each cell using the Automatic color.
Select the range H6:H13 and then format the
range as described below:
Format the range with the Percentage number format with zero
Add a Highlight
Cells conditional formatting rule that formats cells that are greater than 0.24 as light red fill with dark red
Select the range I6:I13 and then use conditional
formatting to add solid orange data bars.
Select the range J6:J13 and then add top/bottom
conditional formatting rules to format the top 10% of values as green fill with
dark green text and the bottom 10% of values as light red fill with dark red
Enter a formula in cell M7 using the VLOOKUP function to find an exact match
for the department abbreviation. Look up the department abbreviation (cell M6) using an absolute reference, search
the staff table data (the range A6:J13)
using an absolute reference, and return the department name (the 2nd column).
Copy the formula in cell M7 to the range M8:M15,
and edit the copied formulas to return the value from the column indicated by
the label in column L.
In cell C17, enter a formula using the TODAY function that displays the
Delete column O.
Hide row 18.
On the Budget
worksheet, create a 2-D pie chart based on the non-adjacent range A5:A12 and
F5:F12. Modify the chart as described below:
Resize and reposition the chart so that the
upper-left corner is located within cell H4 and the lower-right corner is
located within cell O22.
Style 3 to the chart.
Budget by Department as the chart title.
In the 2018 Budget by Department 2-D pie chart
(located in the range H23:O43), make the following changes:
Change the data labels to display only the percentage
and a label position of Center.
Reposition the legend on the right side of the
Update the Departmental Budget Comparison
2018-2022 combo chart in the range A15:G36 as follows:
Change the minimum bound of the right vertical
axis to 40,000,000.
Add axis titles to the chart. Use Department Budgets as the left vertical
title, use Total Budget as the right
vertical title, and remove the horizontal axis title.
Apply a shape fill to the chart using the Blue,
Accent 5, Lighter 80% fill color.
Delete the Hiring
Your workbook should look like the Final Figures on the
following pages. Save your changes, close the workbook, and then exit Excel.
Follow the directions on the SAM website to submit your completed project.