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

(a).The formatting has been done for the given dataset in the "sales_data"worksheet in the workbook "Challenge 7".

(b).From the given dataset

Sum of sales = 19324129.93

Average of sales = 6842.822213

Standard deviation of sales = 174787.9532

Variance of sales = 30550828596

Maximum of sales = 9291501.08

Minimum of sales = 482.13

(c). From the analysis of the product line, status and sales we can see the sum of sales of the different product line mentioned here such as Classic cars,Motorcycles,Ships,Trains etc with reference to their status that is whether the product is Shipped,Disputed,Cancelled etc.Here the sum is calculated with reference to the categories of product and the status. This analysis using pivot table has been mentioned in a seperate worksheet labelled in the name "Product&Sales" in the workbook "Challenge 7".

(d). Made a customized tab named ABC in the sheet with Fonts,Tables and Charts ribbons in it on the excel sheet an also i have attached a screenshot with the tab for your reference in the name "Screenshot ABC Tab".

(e). Here by comparing the Quantities ordered,Country,Year,Quarters,Sales we can get the total sales of an individual country for the year with reference to the quarter of the year. This analysis using Pivot table is mentioned in a seperate worksheet in the name "Quantities Ordered" in the workbook "Challenge 7".

(f). The conditional formatting has been done for the product line and status and mentioned in the worksheet "sales_data"in the workbook "Challenge 7".

(g). The values are changed for the years from 2019 to 2003, 2020 to 2004 , 2021 to 2005 and mentioned in the "sales_data" worksheet in the workbook "Challenge 7".

(h). In the worksheet "Status & Sales" using pivot table we can get the total of sales for all the status mentioned as below For

Cancelled = 194487.48

Disputed = 72212.86

In process = 144729.96

On hold = 178979.19

Resolved = 150718.28

Shipped = 9291501.08

(i). By Using the Left and Right Functions we can split the product codes as shown in the "sales_data" worksheet in the workbook "Challenge 7" in the columns "SPILLITED" and "CODE" in which for example splitted column consists of "S10" and code column consists of "1678".

(j).The Highlighting of the top 10% values has been done for the column "QUANTITY ORDERED" in the "sales_data"worksheet on the workbook "Challenge 7".

Attached a workbook "Challenge 7" and a screenshot named"Screenshot ABC Tab" for your reference.