Project 1 - Data Cleaning and Transformation using Pivot table and Charts for the Report on requirement of Team Hiring
Excel PROJECT 1 Submission!
Project 1 - Data Cleaning and Transformation using Pivot table and Charts
Company ABC Private Limited is manufacturing and selling below mentioned Products:
- Classic Cars
- Motorcycles
- Planes
- Ships
- Trains
- Trucks and Buses
- Vintage Cars
As Business Analyst of the Company, you are required to perform following mentioned tasks on the provided dataset.
a. Do proper formatting of the data with using borders, font size and appropriate alignment of the content.
Answer) Sheet "Question A&I"
b. Make summary of Products Sales by providing
- Total Sum of Sales
- Average of Sales
- Standard Deviation of Sales
- Variance of Sales
- Maximum Sales
- Minimum Sales
Answer) Sheet "Question B"
c. Analyse the sales happened by product line & status using pivot table
Answer) Sheet "Question C"
d. Make Customized Tab by naming ABC and keep few data formatting commands in the newly created Tab.
Answer) Sheet ''Question D" as well as the Screenshot attached for your reference.
e. Analyse the Quantities ordered by each Country in Year 2019,2020 and 2021 for different Quarters using Pivot Table.
Answer) Sheet "Question E"
f. Present the data in different colours for values in field Status and ProductLine using conditional formatting.
Answer) Sheet "Question F"
g. change values of 2003 to 2019, 2004 to 2020 and 2005 to 2021 by using proper date formatting options.
Answer) Sheet "Question G" (Also refer to Sheet "Question A&I")
h. Calculate the total sales of all “Shipped”, “Cancelled”, “Disputed”, “In Process”, “On Hold”, “Resolved” products. (Hint:Use pivot table or sumif function)
Answer) Sheet "Question H"
i. Split the productcode field into two using text functions Example :S10_1678 -> S10 in one column and 1678 in another column.
Answer) Sheet "Question A&I"
j. Highlight the top 10% values of QuantityOrdered field using Conditional Formatting
Answer) Sheet "Question J"
(PS: Though I've created separate Worksheets for each answer(Combined one for Question A & I), but for the reference of all the answers, kindly also consider Sheet "Question A&I")