Project 1 - Data Cleaning and Transformation using Pivot table and Charts for the Report on requirement of Team Hiring

QUESTION (a). Do proper formatting of the data with using borders, font size and appropriate alignment of the content.

ANSWER - i'm attching excel file for all answer, kindly find the attachment.

                 for QUESTION (a) kindly check "MAIN DATA" worksheet.

QUESTION (b). Make summary of Products Sales by providing

1. Total Sum of Sales
2. Average of Sales
3. Standard Deviation of Sales
4. Variance of Sales
5. Maximum Sales
6. Minimum Sales

ANSWER - for QUESTION (b) kindly check MAIN DATA worksheet.

QUESTION (c)-. Make worksheet from the given Data for categorical representation of table based on fields Status, ProductLine and Sales using Pivot Table.

ANSWER - for QUESTION (c) kindly check "Q-C" worksheet.

QUESTION (d)-. Make Customized Ribbon by naming ABC and keep all data formatting commands in the newly created Ribbon.

ANSWER - DATA FORMATTING COMMANDS ARE AS FOLLWS-

•  CELL FORMATTING - MERGE AND CENTER, WRAP WORD, FORMAT PAINTER, CONDITIONAL FORMATTING, FIND AND SEARCH, FIND(), SEARCH(), CTRL+F(FOR FIND), AND CTRL+H (FOR REPLACE)
• NUMBER FORMATTING - PERSENTAGES, CURRENCY, TEXT, NUMBER
• CONDITIONAL FORMATTING
• FORMAT PAINTER
• FIND() & SEARCH ()

 KINDLY SEE NEW TAB NAME (SKILL_LYNC) INSIDE THIS TAB THERE IS ONE RIBBON (ABC) inside this ribbon all the custumized data formatting commands are available.

QUESTION (e)-. Analyse the Quantities ordered by each Country in Year 2019,2020 and 2021 for different Quarters using Pivot Table.

ANSWER -KINDLY CHECK EXCEL FILE > OPEN "Q-e" FOR THE ANSWER.

QUESTION (f). Present the data in different colours for values in field Status and ProductLine using conditional formatting.

ANSWER - KINDLY CHECK EXCEL FILE > OPEN "Q-f" FOR THE ANSWER.

QUESTION (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 - KINDLY CHECK EXCEL FILE > OPEN MAIN DATA > KINDLY CHECK COLUMN NAME "G" FOR THE ANSWER.

QUESTION (h). Calculate the total sales of all “Shipped”, “Cancelled”, “Disputed”, “In Process”, “On Hold”, “Resolved” products.

ANSWER - KINDLY CHECK EXCEL FILE > OPEN "Q-H" FOR THE ANSWER.

QUESTION (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 - KINDLY CHECK EXCEL FILE > OPEN MAIN DATA > KINDLY CHECK COLUMN NAME "N" ,"O", "P" FOR THE ANSWER. AND WORKSHEET "Q-i" also.

QUESTION (j). Highlight the top 10% values of QuantityOrdered field using Conditional Formatting

ANSWER - KINDLY CHECK EXCEL FILE > OPEN "Q-j"     [   column name - "A"  ]  FOR THE ANSWER.