New Perspectives Excel 2019 | Module 2: SAM Project 1b Villanova Technology Group FORMATTING WORKBOOK TEXT AND DATA 1

New Perspectives Excel 2019 | Module 2: SAM Project 1b

Villanova Technology Group

FORMATTING WORKBOOK TEXT AND DATA

1. Mitchell Dupree is an associate consultant at the Villanova Technology Group, a consulting firm that

specializes in information technology. Mitchell uses an Excel workbook to track statistics on client billing

and is formatting the workbook to make it easier to read and use.

Change the theme of the workbook to Office to use the standard theme for Villanova Technology.

2. Switch to the

Billing Summary

worksheet.

Merge and center

the range A1:I1 to begin formatting the

contents as the worksheet title.

3. Apply the

Title

cell style to the range A1:I1.

4. Merge and center

the range A2:I2 to clarify the range contains the worksheet subtitle.

5. Format the date information to separate it from the rest of the workbook. For the range A3:B4, change

the background color to

Gold, Accent 4, Lighter 80%

(8th column, 2nd row in the Theme Colors

palette).

6. Format the range A3:B4 as

bold

.

7. Format the range B3:B4 to use a date with a two-digit month, a two-digit day, and a two-digit year (e.g.,

10/04/21).

8. The range B6:I11 serves as a table showing billing details such as service and hours billed. Format the

column headings in the range B6:I7 as

italic

.

9. Center

the contents of each cell in the range B6:I11.

10. For the range B6:I6, change the background color to

Gold, Accent 4, Lighter 60%

(8th column, 3rd row

in the Theme Colors palette).

11. For the ranges C7:E7 and G7:I7, change the background color to

Gold, Accent 4, Lighter 40%

(8th

column, 4th row in the Theme Colors palette).

12. Merge

the range B6:B7, and then middle align the contents of the merged cell.

13. Merge

the range F6:F7, and then middle align the contents of the merged cell.

14. Merge and center

the range C6:E6, and then

merge and center

the range G6:I6.

15. Format the table data to make it easy to read and interpret. For the ranges B9:I9 and B11:I11, change

the background color to

Gold, Accent 4, Lighter 80%

(8th column, 2nd row in the Theme Colors

palette).

16. In the range G8:I11, apply the

Percentage

number format with one decimal place.

17. In the range G8:G11, use

Conditional Formatting Highlight Cells Rules

to format cells with a value

less than

20%

using

Light Red Fill with Dark Red Text

.

18. For the merged range A8:A11, change the rotation of the text to

0

degrees, and then change the indent

to

2

to clarify that the text applies to the table data.

19. Find and replace all instances of the text "hr" with "hour" to use the full word instead of an abbreviation.

(

Hint

: You should find and replace three instances.)

20. Finally, Mitchell wants to format the workbook for printing. Insert a page break to start a new page at row

13.

21. Change the workbook margins to

Narrow

.

22. Use rows 1 and 2 as print titles.

23. Create a custom header for the worksheet. In the left header section, display the

Sheet Name

using a

Header and Footer element. In the right header section, display the

Page Number

using a Header and

Footer element.