Homework answers / question archive / Illustrated Excel 2019 | Module 2: SAM Project 1b San Antonio University FORMAT WORKSHEETS GETTING STARTED ? Open the file IL_EX19_2b_ FirstLastName _1

Illustrated Excel 2019 | Module 2: SAM Project 1b San Antonio University FORMAT WORKSHEETS GETTING STARTED ? Open the file IL_EX19_2b_ FirstLastName _1

MS Excel

Share With

---

Illustrated Excel 2019 | Module 2: SAM Project 1b

San Antonio University

FORMAT WORKSHEETS

GETTING STARTED

?

Open the file

IL_EX19_2b_

FirstLastName

_1.xlsx

, available for download from the SAM

website.

?

Save the file as

IL_EX19_2b_

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

IL_EX19_2b_

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.

Santiago Flores is a development officer for San Antonio University in San Antonio, Texas.

He is using an Excel workbook to track the events the Development Office sponsors and

analyze event staffing needs. He has asked you to format his workbook to make the

information clearer and easier to understand.

Go to the

Assignments

worksheet. Rename the

Assignments

worksheet to

Staff

Assignments

, and then change the tab color of the worksheet to

Gold, Accent 5,

Lighter 60%

(9th column, 3rd row of the Theme Colors palette) to match the color of

the other worksheet tabs.

2.

Use AutoFit to resize column B to fit its contents. (

Hint:

The width of column B should be

approximately 13.)

3.

Change the width of column F to

14.00

, so that the text "Administrative" is completely

visible in cell F4.

4.

Apply the

Title

cell style to cell A1, and then increase the font size to

22 point

to make

the worksheet title stand out.

5.

Merge and center the range A1:G1, then bold the cell contents to emphasize that the

text is the worksheet title.

6.

Center

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

White,

Background 1

(1st column, 1st row of the Theme Colors palette) to make the column

headings easier to read.

7.

Use the Format Painter to apply the format from the range A5:G5 to the range A7:G7 to

create consistent shading and borders in the worksheet.

8.

Italicize the contents of the range A11:A12 and change its font to

Calibri Light

to

separate those row headings from the others above them.

9.

In the range A12:G12, apply a

Thick Bottom Border

to the cells, then change the fill

color of the range A11:G12 to

Gold, Accent 5, Lighter 60%

(9th column, 3rd row of the

Theme Colors palette).

10.

Santiago wants to know at a glance which event types require the most time for

development officers.

In the range C5:C10, use Conditional Formatting

Highlight Cells Rules

to format cells

whose contents are greater than

10%

with

Green Fill with Dark Green Text

.

11.

Apply the

Short Date

number format to cell B14 to use a more common date format.

[Mac Hint: The date will displayed using the format m/d/yy.]

12.

Apply the

Percentage

number format with zero decimal places to the range G5:G11 to

clarify the range contains percentage values.

13.

Santiago would like to see the budgeted rate dollar amounts in a rounded format.

Apply the

Currency

number format to the range B12:F12 and then decrease the number

of decimal places shown to zero.

14.

Enter the amount

225,460

in cell B16, then delete row 18 because Santiago no longer

needs to have the worksheet reviewed.

15.

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

16.

Go to the

Event Details

worksheet. Find and replace all occurrences of the text "Hrs" with

Hours

to use the complete word instead of the abbreviation.

17.

For the range E3:F16, increase the number of decimal places displayed to one to display

the full values.

18.

In the range D3:D15, use Conditional Formatting to create a Data Bars rule, and use the

Gradient Fill Orange Data Bar

color option so that Santiago can clearly compare the

hours for each event.

19.

Move the

Staff Assignments

worksheet after the

Event Schedule

worksheet to use a

more logical order. (

Hint

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

follows:

Documentation

,

Event Details

,

Event Schedule

, and

Staff Assignments

.)

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.

Final Figure 1: Event Details Worksheet

Final Figure 2: Staff Assignments Worksheet