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

A. Do proper formatting of the data with using borders, font size and appropriate alignment of the content.

Ans: Proper formatting is given to the EXcel sheet, Please find below

B. Make summary of Products Sales by providing.

Ans: By using Formulas output is given below, and output is in Excelsheet (D2827:E2832) cell.

1. Total Sum of Sales

   10032628.85

2. Average of Sales

   3553.889072

3. Standard Deviation of Sales

   1841.865106

4. Variance of Sales

   3392467.068

5. Maximum Sales

   14082.8

6. Minimum Sales

   482.13

C. Make worksheet from the given Data for categorical representation of table based on fields Status, ProductLine and Sales using Pivot Table

Ans: Created a worksheet using pivot table, worksheet is attached below,Please find below, worksheet name-->categorical representation (C).

D. Make Customized Ribbon by naming ABC and keep all data formatting commands in the newly created Ribbon.

Ans: Created Ribbon with name ABC, and data formating commands are created , Below attached worksheet please find.

E. Analyse the Quantities ordered by each Country in Year 2019,2020 and 2021 for different Quarters using Pivot Table.

Ans: Created worksheet using pivot table for Quantities ordered by each Country, Worksheet is attached please find below, And worksheet name is Quantities ordered (E).

F. Present the data in different colours for values in field Status and ProductLine using conditional formatting.

Ans: Filed Status(G) and ProductLine(K) column's are given different colours.Attached below please find.

G. Clean the Data with respect to date by proper date formatting and change values of 2003 to 2019, 2004 to 2020 and 2005 to 2021.

Ans: Cleaned the data by proper date formatting and changed the values 2003-->2019,2004-->2020,2005--->2021.

H. Calculate the total sales of all “Shipped”, “Cancelled”, “Disputed”, “In Process”, “On Hold”, “Resolved” products

Ans: Total sale of all products below, And also attached worksheet below. worksheet name--> Total sales for status wise

• Shipped--->

  9291501.08

• Cancelled--->

  194487.48

• Disputed--->

  72212.86

• In Process--->

  144729.96

• On Hold--->

  178979.19

• Resolved--->

  150718.28

I. Add two new fields in data sheet with reference to Productcode using text functions
Example S10_1678 to be split as S10 in one column and 1678 in another column.

Ans: By using TEXT function, New columns are added to worksheet (N&O columns). Please find below attachment.

J. Highlight the top 10% values of QuantityOrdered field using Conditional Formatting

Ans: Top 10% values of Quantity Ordered files are Highlight in the worksheet on B column, Please find the below attachment.