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.
Answer: Excel file has been attached with the Formatted Data (Sheet name: Sales_Data)
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: Summary of the Product Sales made in Sheet Name: Sales_Data.
c. Make worksheet from the given Data for categorical representation of table based on fields Status, ProductLine and Sales using Pivot Table.
Answer: Pivot Table made and is found in the Excel file attached and Sheet name is “Answer c”.
d. Make Customized Ribbon by naming ABC and keep all data formatting commands in the newly created Ribbon.
Answer: Customized Ribbon has been created with the formatting commands by naming the ribbon as “ABC”(Excel file attached)
e. Analyse the Quantities ordered by each Country in Year 2019,2020 and 2021 for different Quarters using Pivot Table.
Answer: Answer in Excel File attached, Sheet Name “Answer e”
f. Present the data in different colours for values in field Status and ProductLine using conditional formatting.
Answer: Used Conditional Formatting for colouring and will be found in the Excel file attached (Sheet name: Sales_data.
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.
Answer: Data Formatting done. (Sheet name: Sales_data)
h. Calculate the total sales of all “Shipped”, “Cancelled”, “Disputed”, “In Process”, “On Hold”, “Resolved” products.
Answer: Total Sales calculated and is in the excel file and sheet name: Sales_data
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.
Answer: Two new fields/columns added and by using text function, splited the text into two columns. Excel file attached and sheet name: Sales_data.
j. Highlight the top 10% values of QuantityOrdered field using Conditional Formatting
Answer: By using Conditional Formatting, top10% values have been highlighted and will be found in excel sheet attached in Sheet name: Sales_data.