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

a). STEP1 - For boarders : HOME>>>FONT>>>BOARDERS(ALLBOARDERS AND THICK BOARDERTS)

STEP2 - FOR HEADLINES :

HOME>>>FONT>>>BOLD(FOR BOLD)

HOME>>>ALIGNMENT>>>MERGE CENTRE(FOR PROPER CENTRE HEADLINE)

STEP3 - FOR FILTERS : HOME>>>SORT&FILTER>>>FILTER

STEP4 - FOR PROPER TABLE : HOME>>>FORMAT AS TABLE AND TABLE CELLS

b). 1. Total Sum of Sales = 10032628.85 (=SUM(E2:E2824))

2. Average of Sales = 3553.889072 (=AVERAGE(E2:E2824))

3. Standard Deviation of Sales = 1841.865106 (=STDEV(E2:E2824))

4. Variance of Sales = 3392467.068 (=VAR(E2:E2824))

5. Maximum Sales = 14082.8 (=MAX(E2:E2824))

6. Minimum Sales = 482.13 (=MIN(E2:E2824))

c). STEP1 - INSERT>>>PIVOT TABLE

STEP2 - IN PIVOT TABLE FIELD SELECT SALES AND STATUS AS A ROWS AND PRODUCTLINE AS A COLUMN

STEP3 - DID REQUIRED MODIFICATIONS

d). DOUBLE RIGHT CLICK ON THE RIBBON SELECT CUSTOMIZED RIBBON AND PROCEED

e). STEP1 - INSERT>>>PIVOT TABLE

STEP2 - IN PIVOT TABLE FIELD SELECT COUNTRY AS A ROWS AND YEAR_ID AND QTR_ID AS A COLUMNS

f). STEP1 - HOME>>>STYLES>>>CONDITIONAL FORMATTING

STEP2 - IN CONDITIONAL FORMATTING I SELECT TEXT THAT CONTAINS FOR SHIPPED WITH COLOR LIGHT green

STEP3 - I SELECT LIGHT RED FOR CANCELLED AND LIGHT YELLOW FOR ON HOLD

STEP4 - FOR RESOLVED I SELECTED BLUE AND FOR IN PROCESS THICK GREEN AND LIGHT BLACK FOR DISPUTED

STEP5V - SIMILARLY I DID THIS FOR PRODCTLINE

g). HOME>>>EDITING>>>FIND&SELECT

I FIND 2004 AND REPLSCED WITH 2019 SIMILARLY 2004 TO 2020 AND 2005 TO 2021

h). The total sales of all Shipped = 9291501.08 (=SUMIF(G2:G2824,"Shipped",E2:E2824))

The total sales of all Cancelled = 194487.48 (=SUMIF(G2:G2824,"Cancelled",E2:E2824))

The total sales of all Disputed = 72212.86 (=SUMIF(G2:G2824,"Disputed",E2:E2824))

The total sales of all In process = 144729.96 (=SUMIF(G2:G2824,"In process",E2:E2824))

The total sales of all On Hold = 178979.19 (=SUMIF(G2:G2824,"On Hold",E2:E2824))

The total sales of all Resolved = 150718.28 (=SUMIF(G2:G2824,"Resolved",E2:E2824))

i). TO SPLIT THE PRODUCT CODE

DATA>>>TEXT TO COLUMN

j). HOME>>>CONDITIONAL FORMATTING>>>TOP10%