Скачать или смотреть New Perspectives Excel 2019 | Module 1-4: SAM Capstone Project 1b

To Get this Solution Contact us on Email and WhatsApp is given in Video
WhatsApp no : +923114151799
Email address: [email protected]

Employees Worksheet Formatting

Change workbook theme

Go to Page Layout → Themes → Office.

Set column widths (D:I)

Select columns D through I → Right-click → Column Width → enter 11.

Set row heights (2 and 6)

Select rows 2 and 6 → Right-click → Row Height → enter 30.

Merge and center header (B3:K3)

Select B3:K3 → Merge & Center.

Format merged header (B3:K3)

Apply Cell Style: 40% - Accent 2.

Make it Bold, set Font Size = 12.

Enter table headings (B6:F6)

Code | Department | Directors | Clerks | Staff


Format row 6 (B6:K6)

Center text.

Font size 11.

Fill color: Orange, Accent 2, Lighter 80%.

Wrap Text in cell K6.

Apply border to table (B7:K17)

Select range → All Borders → color Light Gray, Background 2, Darker 10%.

Top border row 18 (B18:K18)

Thin Top Border in Orange, Accent 2.

Format percentages (I7:I17)

Apply Percentage, 0 decimals.

Add Conditional Formatting → Highlight Cell Rules → Greater Than 20% → Light Red Fill with Dark Red Text.

Data bars (J7:J17)

Conditional Formatting → Data Bars → Gradient Fill → Red.

Top/Bottom Rules (K7:K17)

Conditional Formatting → Top/Bottom Rules → Top 5% → Green Fill with Dark Green Text.

Conditional Formatting → Bottom 5% → Light Red Fill with Dark Red Text.

VLOOKUP formula (N8)

=VLOOKUP($N$7, $B$7:$K$17, 2, FALSE)


Copy formula (N9:N16)

Paste formulas only.

Update the column index number (based on label in col M).

TODAY function (D21)

=TODAY()


Delete column P

Right-click column P → Delete.

Hide row 22

Right-click row 22 → Hide.

Department Comparison Worksheet

Create 2-D Pie Chart (B12:B16 + F12:F16)

Insert → Pie Chart.

Move chart to J4:P17.

Apply Chart Style 4.

Title: Bottom Five Depts - 2022.

Edit Pie Chart (Bottom Five Depts - 2019)

Data Labels → show Percentage only, position = Inside End.

Legend → move to Right.

Update Top Five Departments Column Chart (B19:H39)

Vertical axis max = 13,000,000.

Add axis titles:

Left Vertical: Budgeted $

Right Vertical: Total Budget

Remove Horizontal title.

Plot area fill = Gray, Accent 3, Lighter 80%.

Delete Projections worksheet

Right-click sheet tab Projections → Delete.