Project 1 - Data Cleaning and Transformation using Pivot table and Charts for the Report on requirement of Team Hiring
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 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.
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
c. Make worksheet from the given Data for categorical representation of table based on fields Status, ProductLine and Sales using Pivot Table.
d. Make Customized Ribbon by naming ABC and keep all data formatting commands in the newly created Ribbon.
e. Analyse the Quantities ordered by each Country in Year 2019,2020 and 2021 for different Quarters using Pivot Table.
f. Present the data in different colours for values in field Status and ProductLine using conditional formatting.
Feature used - conditional formatting ( text that contain specific word i.e status )
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.
-By using find and replace command,values 2003,2004,2005 changed to 2019.2020,2021 repectively.
-Using general formatting option (DATE) ,format of the field- "order date" is changed into proper date formate(short date).( see colum F )
h. Calculate the total sales of all “Shipped”, “Cancelled”, “Disputed”, “In Process”, “On Hold”, “Resolved” products.
- By using SUMIF() function following values are calculated for the corresponding criteria.
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.
Tool used- Convert Text into column wizard
output- (see column M and N)
j. Highlight the top 10% values of Quantity Ordered field using Conditional Formatting
-Feature used- Conditional formatting (Highlight top 10%)(see column B)
