New Perspectives Excel 2019 | Module 2: End of Module Project 2 Shingle Rock College FORMAT WORKBOOK TEXT AND DATA GETTING STARTED ? Open the file NP_EX19_EOM2-2_ FirstLastName _1

New Perspectives Excel 2019 | Module 2: End of Module Project 2

Shingle Rock College

FORMAT WORKBOOK TEXT AND DATA

GETTING STARTED

?

Open the file

NP_EX19_EOM2-2_

FirstLastName

_1.xlsx

, available for download from

the SAM website.

?

Save the file as

NP_EX19_EOM2-2_

FirstLastName

_2.xlsx

by changing the “1” to a

“2”.

?

If you do not see the

.xlsx

file extension in the Save As dialog box, do not type it. The

program will add the file extension for you automatically.

?

With the file

NP_EX19_EOM2-2_

FirstLastName

_2.xlsx

still open, ensure that your first

and last name is displayed in cell B6 of the Documentation sheet.

?

If cell B6 does not display your name, delete the file and download a new copy from the

SAM website.

PROJECT STEPS

1.

Sid Kellerman works in the Office of Admissions at Shingle Rock College. Sid is adding

formatting to the workbook he uses to track applications for the upcoming academic

year to make the data easier to read and print.

Switch to the

Applications

worksheet, then format the headings. Merge and Center the

range A1:F1.

2.

For the range A1:F1, apply the Accent 6 cell style, then bold the contents and change the

font size to 18 point.

3.

Merge and center the range A2:F2, apply the 40% - Accent 6 cell style, then change the

font size to 12 point.

[Mac Hint: The font size may automatically be changed to 12

points.]

4.

For the range A4:A5, change the alignment to right.

5.

Unmerge the range B4:C4.

6.

Format the date and application total. For the range A4:B5, apply the 40% - Accent 5 cell

style, then change the font size to 12 point.

7.

For the range B4:B5, bold the cell contents.

8.

For cell B4, apply the Long Date number format (e.g., Friday, July 16, 2021).

9.

Find and replace all instances of the text "aplications" with

Applications

(

Hint

: Do not

include the quotation marks. You should find and replace three instances.).

10.

Format the application table by clearing the formatting from the range A6:F6.

11.

For the range B8:B60, reduce the number of decimal places to 0.

12.

For the range C8:C60, apply the Percentage number format with 2 decimal places, then

copy the format to the range D8:E60.

13.

For the range E8:E60, add conditional formatting using the Highlight Cells Rules,

specifying a

Less Than

rule that formats a cell with

Light Red Fill

when cell contents

are less than -

0.01

14.

For the range E8:E60, add additional conditional formatting using the Highlight Cells

Rules, specifying a

Greater Than

rule that formats a cell with

Yellow Fill with Dark

Yellow Text

when cell contents are greater than

0.02

.

15.

For the range F8:F60, apply the Accounting number format with 2 decimal places and $

as the symbol.

16.

For the range B60:C60, apply the Total cell style, then apply the style to cell F60.

17.

Format the

Applications

worksheet for printing. Select row 7 as the print titles to be

repeated on each page. Change the top and bottom margins to

0.8

inch, and the left and

right margins to

0.5

inch. Change the print area to the range A1:F60.

18.

On the left side of the footer, add the text

Class of 2025 Applications

. On the right

side of the footer, use Header & Footer elements to insert the current page number, a

space, the word

of

, another space, and the total number of pages.

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.

New Perspectives Excel 2019 | Module 2: End of Module Project 2

Final Figure 1: Applications Worksheet - Page 1

Final Figure 2: Applications Worksheet - Page 2