Project 1 - Data Cleaning and Transformation using Pivot table and Charts for the Report on requirement of Team Hiring
----------------------------------------------------------------------------------------------------------------------------------------------
Company ABC Private Limited is manufacturing and selling the below-mentioned Products:
- Classic Cars
- Motorcycles
- Planes
- Ships
- Trains
- Trucks and Buses
- Vintage Cars
---------------------------------------------------------------------------------------------------------------------------------------------
First, I have done formatting, creating borders and making proper alignment and font size, and more formatting. Then Making a summary of Products Sales by providing, Total Sum of Sales, Average of Sales, Standard Deviation of Sales, Variance of Sales, Maximum Sales, and Minimum Sales. After that Make Data for categorical representation of the table based on fields Status, ProductLine, and Sales using Pivot Table. After that Make a Customized Ribbon by naming ABC and keeping all data formatting commands. After that Analyzing the Quantities ordered by each Country in Years 2019,2020 and 2021 for different Quarters using Pivot Table. After that Present the data in different colors for values in the field Status and ProductLine using conditional formatting and then Clean the Data with respect to date by proper date formatting and changing the values of 2003 to 2019, 2004 to 2020, and 2005 to 2021. After that Calculate the total sales for all “Shipped”, “Cancelled”, “Disputed”, “In Process”, “On Hold”, and “Resolved” products. After that Add two new fields in the data sheet with reference to the Product code using text functions and then at the end Highlight the top 10% values of the Quantity Ordered field using Conditional Formatting.
-----------------------------------------------------------------------------------------------------------------------------------------------