Project 1 - Data Cleaning and Transformation using Pivot table and Charts for the Report on requirement of Team Hiring

---

Step-by-Step Formatting Instructions
1. Apply Borders

---

2. Set Font Style and Size

---

3. Column Width Adjustment

---

4. Alignment

---

5. Header Row Styling

---

6. Format Data Types

• Select the entire dataset (including headers).

• Go to Borders:

  • Apply All Borders.

  • For headers, apply Thick Bottom Border (optional for emphasis).

• Use a professional font like Calibri, Arial, or Segoe UI.

• Set font size to:

  • 12 pt for content

  • Bold and 14 pt for headers

• Adjust column widths so all data is fully visible.

  • You can double-click between column letters to auto-fit.

• Go to Home → Alignment section to change this.

• Bold text

• Apply a fill color (e.g., light blue or gray) for visual separation

• Change font color to black or dark blue for contrast

• Date Columns → Format as DD-MMM-YYYY (e.g., 01-Jan-2024)

• Currency Columns (e.g., Revenue, Unit Cost) → Format as ₹ or $, depending on business currency

  • Use: Format Cells > Currency

• Number Columns → Use commas (1,000+) and 2 decimal places if needed

B.

Metric	Formula (example for "Classic Cars")
Sum	=SUMIF(A:A, "Classic Cars", B:B)
Average	=AVERAGEIF(A:A, "Classic Cars", B:B)
Std. Deviation	=STDEV.S(IF(A:A="Classic Cars", B:B)) (use Ctrl+Shift+Enter if array formula needed)
Variance	=VAR.S(IF(A:A="Classic Cars", B:B))
Maximum	=MAX(IF(A:A="Classic Cars", B:B))
Minimum	=MIN(IF(A:A="Classic Cars", B:B))

C.

Product Line	Shipped	On Hold	Cancelled	Grand Total
Classic Cars	₹265,000	₹0	₹0	₹265,000
Motorcycles	₹100,000	₹45,000	₹0	₹145,000
Planes	₹0	₹0	₹70,000	₹70,000
Ships	₹120,000	₹0	₹0	₹120,000
Trains	₹60,000	₹20,000	₹10,000	₹90,000
Grand Total	₹545,000	₹65,000	₹80,000	₹690,000

D.

Command	Purpose

Bold	Emphasize header or important data

Font Size

Adjust readability

Fill Color

Highlight key cells

Font Color

Make text stand out

Borders

Add cell boundaries for clarity

Currency

Format sales/revenue values

Center Align

Clean visual structure

Wrap Text

Handle long data in one cell

E.

Order Date	Country	Quantity Ordered
12-Feb-2019	Germany	120
25-Mar-2020	USA	200
06-Jul-2021	Japan	160
...	...	..

F.

ProductLine	Color
Classic Cars	Light Blue
Motorcycles	Light Green
Planes	Light Yellow
Ships	Light Orange
Trains	Lavender
Trucks and Buses	Gray
Vintage Cars	Peach

G.

Original Date	Updated Date
01-Jan-2003	01-Jan-2019
15-Mar-2004	15-Mar-2020
23-Jul-2005	23-Jul-2021
...

H.

=SUMIF(A:A, "Shipped", B:B)

+ SUMIF(A:A, "Cancelled", B:B)

+ SUMIF(A:A, "Disputed", B:B)

+ SUMIF(A:A, "In Process", B:B)

+ SUMIF(A:A, "On Hold", B:B)

+ SUMIF(A:A, "Resolved", B:B)

=SUM(SUMIFS(B:B, A:A, {"Shipped","Cancelled","Disputed","In Process","On Hold","Resolved"}))

Status	Total Sales
Shipped	$150,000
Cancelled	$12,000
Disputed	$5,000
In Process	$22,000
On Hold	$18,500
Resolved	$8,000
Total	$215,500

I.

To split the ProductCode field like "S10_1678" into two parts:

• S10 in one column

• 1678 in another column

1. Select the column with the ProductCodes.

2. Go to Data → Text to Columns.

3. Choose Delimited, then click Next.

4. Select Underscore (_) as the delimiter.

5. Click Finish.

Excel will automatically split S10_1678 into S10 and 1678 in separate columns.

J.

Step-by-Step:

1. Select the column that contains QuantityOrdered (e.g., Column C).

2. Go to the Home tab → Click on Conditional Formatting.

3. Choose Top/Bottom Rules → Top 10%...

4. In the dialog box:

   • Leave "10%" as is.

   • Choose a formatting style (e.g., fill color).

   • Click OK.

Result: The top 10% of quantities ordered will be highlighted with your chosen format.