Illustrated Excel 2016 | Module 3: SAM Project 1a Cassandra’s Grades FORMATTING A WORKSHEET GETTING STARTED ? Open the file IL_EX16_3a_ FirstLastName _1

Subject:MS ExcelPrice:19.87 Bought3

Share With

Illustrated Excel 2016 | Module 3: SAM Project 1a

Cassandra’s Grades

FORMATTING A WORKSHEET

GETTING STARTED

?

Open the file

IL_EX16_3a_

FirstLastName

_1.xlsx

, available for download from

the SAM website.

?

Save the file as

IL_EX16_3a_

FirstLastName

_2.xlsx

by changing the “1” to a

“2”.

o

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

IL_EX16_3a_

FirstLastName

_2.xlsx

still open, ensure that your

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

o

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

copy from the SAM website.

PROJECT STEPS

1.

Cassandra will finish her Spring classes in the coming weeks. She is using an

Excel workbook to analyze her grades to help her focus on the classes that need

more improvement. Cassandra has asked you to format her workbook to make

the analysis clearer and easier to understand.

Switch to the

Grades

worksheet. Use

AutoFit

to resize column A to fit its

contents.

2.

Set the width of column E to approximately

14.00

, so that the word

“Composition”

is completely visible in cell E4.

3.

Apply the

Accent5

cell style to the range A1:E1, then increase the font size in

that range to

15 pt

.

4.

Merge and center

the range A3:E3, then

bold

the cell contents.

5.

Center

the text in the range B4:E4 and change the font color of the range to

White, Background 1

(1

st

column, 1

st

row of the Theme Colors palette).

6.

Use the Format Painter to apply the format from the range A7:E7 to the range

A9:E9.

7.

Italicize

the contents of cell A11

and change its font to

Times New Roman

.

8.

In the range A11:E11, apply

Thick Outside Borders

to the cells, then change

the fill color to

Yellow

(4

th

column, 1

st

row

of the Standard Colors palette).

9.

Cassandra wants to know at a glance which courses she is passing.

In the range B11:E11, use Conditional Formatting

Highlight Cells Rules

to

format cells whose contents are

greater than 70

with

Green Fill with Dark

Green Text

.

 

10.

Apply the

Short Date

number format (e.g., 6/20/2018) to cell

C14.

11.

Apply the

Percentage

number format to cell C15 and decrease the number of

decimal places shown to

zero

.

12.

Cassandra would like to see the amount of tuition paid in a monetary and

rounded format.

Apply the

Accounting

number format to cell C16 and then decrease the

number of decimal places shown to

zero

. (

Hint

: The Accounting number format

may change to Custom after you decrease the number of decimal places.)

13.

Find and replace all occurrences of the text “Sem.” with

Semester

. (

Hint

: You

should make two replacements.)

14.

Check the Spelling in the workbook to identify and correct any spelling errors.

(

Hint

: You should find and correct at least three spelling errors.)

15.

Cassandra added a row to her worksheet to account for her final exam grades in

each of her classes, so she no longer needs the reminder note in cell A18

.

Delete row 18.

16.

Rename the

Grades

worksheet

Spring 2018 Grades

, then change the tab

color of the worksheet to

Blue

(8

th

column, 1

st

row of the Standard Colors

palette).

17.

Cassandra also keeps track of her General Education graduation requirements

in this workbook.

Switch to the

Graduation Requirements

worksheet. For the range B3:C8,

increase the number of decimal places displayed to

one

.

18.

In the range B6:C6, use Conditional Formatting to create a

Data Bars

rule, and

use the

Gradient Fill Blue Data Bar

color option for these data bars.

19.

Move the

Graduation Requirements

worksheet after the

Spring 2018 Schedule

worksheet

. (

Hint

: The order of the worksheets in the workbook should be as

follows:

Documentation

,

Spring 2018 Grades

,

Spring 2018 Schedule

,

Graduation

Requirements

.)

Your workbook should look like the Final Figures on the following pages. Save your

changes, close the workbook, then exit Excel. Follow the directions on the SAM website

to submit your completed project.

Final Figure 1: Spring 2018 Grades Worksheet

Final Figure 2: Graduation Requirements Worksheet