New Perspectives Excel 2016 Module 2: SAM Project 1a
Levette Whitlock
GE ver. 17.1.0-rc0000
1.
0/0
Apply a theme to the workbook.
2.
0/0
Apply a cell style to a merged range.
Align cell content.
3.
0/0
Merge and center a range of cells.
Change the font size of range of cells.
4.
0/0
Change the font style of cell contents.
Change the font of cell contents.
Change the font color of cell contents.
5. Format cell B3 using the Short Date (e.g., 3/13/2018) number format.
0/0
Apply a number format to a cell.
6. Use the Format Painter to copy the format from cell A6 to the range B6:G6.
0/0
Copy and paste formatting to a range.
7.
0/0
Create a formula.
Copy a formula into a range.
8.
0/0
Create a formula.
Copy a formula into a range.
9.
1/1
Change the fill color of a range of cells.
10.
1/1
Apply a conditional formatting rule to a range.
Format a conditional formatting rule.
11. In cell A11, increase the indent of the cell contents twice.
1/1
Indent cell contents.
12.
1/1
Create a formula using a function.
Copy a formula into a range.
13.
1/1
Apply a number format to a range of cells.
14. In the range G7:G11, apply the Percentage number format with one decimal place.
1/1
Apply a number format to a range of cells.
15. For the merged range A20:A30, rotate the cell contents to 0 degrees.
1/1
Change the orientation of cell content.
16.
0/1
Find and replace cell values.
17.
1/1
Change the orientation of a worksheet.
Set the margins of a worksheet.
18.
1/1
Insert a page break in the worksheet.
19.
0/1
Add print titles to a worksheet.
In the Hourly Compensation worksheet, rows 1-3 should be formatted as print titles.
20.
0/1
Add a footer to a workbook.
SUBMISSION #1 | SCORE IS:
9
OUT OF
12
Stephanie manages the employees at the Good Breeze Hotel. For the new year, Stephanie
decided to update the workbook she uses to track her team’s compensation data in order to
make the information easier to understand.
Change the theme of the workbook to Office.
Switch to the Hourly Compensation worksheet. Apply the Title cell style to the merged range
A1:G1, and then center the text in that merged range.
Merge and center the range A2:G2, and then change the font size of the merged range to 14
pt.
Italicize the contents of the range A3:B3, change the font to Times New Roman, and then
change the font color to Orange, Accent 2 (6th column, 1st row of the Theme Colors palette).
In the Hourly Compensation worksheet, the range A3:B3 should be formatted using the Times New
Roman font.
In cell E7, create a formula that calculates the salary for Sarah, one of the servers at The
Good Breeze Hotel. Her salary is calculated by multiplying the number of regular hours
worked (cell B7) by her hourly rate (cell D7), and then adding to that the number of overtime
hours worked (cell C7) multiplied by her hourly rate (cell D7) times 1.5. (Hint: Sarah is paid
50% more than—or 1.5 times—her regular hourly rate when she works overtime.)
To calculate the salary for all of the employees, copy the formula you created and the
formatting in cell E7 into the range E8:E10.
Stephanie wants to see what percentage of her employees' total working hours are spent
working overtime. In cell G7, enter a formula that divides Sarah's overtime hours (cell C7) by
her total hours worked (cell F7).
Copy the formula and the formatting in cell G7 into the range G8:G10.
Change the fill color of cells E8, E10, G8, and G10 to Blue, Accent 1, Lighter 80% (5th column,
2nd row of the Theme Colors palette).
To quickly see which employees worked overtime, use the Highlight Cells Rules conditional
formatting to format cells in the range C7:C10 with a value greater than 0 using Light Red
Fill with Dark Red Text.
Stephanie would like to know the average number of regular hours worked by her
employees. In cell B11, create a formula using the AVERAGE function to find the average
number of regular hours her employees work in a week (B7:B10).
Copy the formula from cell B11 into the range C11:G11 to find the average of the overtime
hours, hourly rates, salaries, work hour totals, and percentage of overtime hours for the
team.
In the range E7:E11, apply the Currency number format with zero decimal places and $ as the
symbol.
Find and replace all instances of the text “Weekly Hour” with Weekly Hours. (Hint: You
should find and replace two instances.)
In the Hourly Compensation worksheet, all instances of the text "Weekly Hour" should be replaced
with "Weekly Hours".
Stephanie plans to print the worksheet to review with her team.
Change the page orientation to Landscape, and then set the margins to Wide.
She wants the table and the chart to appear on separate pages when printed. Select cell
A13, and then insert a page break.
Set rows 1-3 as print titles. (Hint: Rows 1-3 should repeat at the top of each printed page of
the worksheet.)
Create a custom footer for the worksheet. In the left footer section, display the current Page
Number using a Header and Footer element. In the center footer section, display the Sheet
Name using a Header and Footer element.
Switch back to Normal View if necessary.
In the Hourly Compensation worksheet, the footer should display the worksheet name in the center
section and the page number in the left section.
