Project 1 - Data Cleaning and Transformation using Pivot table and Charts for the Report on requirement of Team Hiring
• The data was imported, reviewed for errors or inconsistencies, and cleaned by removing duplicates, fixing formatting issues, and standardizing the data.
• Once the data had been cleaned, we used pivot tables to summarize and analyze the data. Pivot tables are a powerful Excel tool that allows us to quickly summarize and analyze large amounts of data. We used pivot tables to group data by category, calculate averages, sums, and other statistical measures, and create customized reports.
a. Do proper formatting of the data by using borders, font size and appropriate alignment of the content.
b. Make a 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 a worksheet from the given Data for categorical representation of the table based on fields Status, ProductLine and Sales using Pivot Table.
d. Make a 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 Years 2019,2020 and 2021 for different Quarters using Pivot Table.
f. Present the data in different colours for values in the field Status and ProductLine using conditional formatting.
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.
h. Calculate the total sales of all “Shipped”, “Cancelled”, “Disputed”, “In Process”, “On Hold”, and “Resolved” products.
i. Add two new fields in the 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.
j. Highlight the top 10% values of the QuantityOrdered field using Conditional Formatting.
