Challenge

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

a. To format the data, select the table and use the options in the "Home" tab to apply borders, font size, and appropriate alignment.

b. To make a summary of Products Sales:

Select the column containing sales data
Use the "AutoSum" button to calculate the total sum, average, standard deviation, and variance
Use the "Max" and "Min" functions to find the maximum and minimum sales

c. To make a worksheet for categorical representation using Pivot Table:

Select the data and go to the "Insert" tab
Click on "Pivot Table" and choose the location for the table
Drag the "Status", "ProductLine", and "Sales" fields into the appropriate areas in the Pivot Table Field List
Choose the appropriate calculations and formatting options

d. To make a customized Ribbon:

e. To analyze quantities ordered by each country in different quarters:

Select the data and go to the "Insert" tab
Click on "Pivot Table" and choose the location for the table
Drag the "Country", "Year", "Quarter", and "QuantityOrdered" fields into the appropriate areas in the Pivot Table Field List
Choose the appropriate calculations and formatting options

f. To use conditional formatting to highlight values in the "Status" and "ProductLine" fields:

g. To clean the data with respect to date:

Select the date column and go to the "Home" tab
Click on "Number Format" and choose the appropriate date format
Replace the values of 2003, 2004, and 2005 with 2019, 2020, and 2021, respectively

h. To calculate the total sales of certain products:

Use the "SUMIF" function to add up the sales for each product with the specified status

i. To add two new fields with reference to Productcode using text functions:

Use the "LEFT" and "RIGHT" functions to split the Productcode into two separate columns

j. To highlight the top 10% values of the QuantityOrdered field:

Ship work like this
in your own course.