Project 1 - Data Cleaning and Transformation using Pivot table and Charts for the Report on requirement of Team Hiring
Q A. Do proper formatting of the data with using borders, font size and appropriate alignment of the content.
Q 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
Q C. Make worksheet from the given Data for categorical representation of table based on fields Status, ProductLine and Sales using Pivot Table.
Q D. Make Customized Ribbon by naming ABC and keep all data formatting commands in the newly created Ribbon.
Q E. Analyse the Quantities ordered by each Country in Year 2019,2020 and 2021 for different Quarters using Pivot Table.
Q F. Present the data in different colours for values in field Status and ProductLine using conditional formatting.
Conditional formatting of field Status -
Conditional formatting of field Product Line -
Q 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.
2003 -> 2019
2004 -> 2020
2005 ->2021
Q H. Calculate the total sales of all “Shipped”, “Cancelled”, “Disputed”, “In Process”, “On Hold”, “Resolved” products.
Q 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.
Q J. Highlight the top 10% values of QuantityOrdered field using Conditional Formatting
