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 : File attached. sheet name : sales_data

b. Make summary of Products Sales by providing

1. Total Sum of Sales
2. Average of Sales
3. Standard Deviation of Sales
4. Variance of Sales
5. Maximum Sales
6. Minimum Sales

Ans : File attached. sheet name Summary of Products Sales. 

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

Ans : File attached. Sheet name categorical representation

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

Ans : File attached.

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

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

Ans : Highlighted top 10% values in QuantityOrdered column. 

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 : File attached. in Sales date sheet P and Q column

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 : File attached.

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

Ans : File attached.