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

To perform the tasks mentioned on the provided dataset, you can use spreadsheet software like Microsoft Excel or Google Sheets. Here's a step-by-step guide to completing each task:

a. Formatting the Data:

• Open the dataset in a spreadsheet software.
• Select the entire dataset.
• Apply borders, adjust font size, and align the content as desired.

b. Summary of Products Sales:

• Create a new section or sheet in the spreadsheet to perform calculations.
• Use appropriate formulas to calculate the requested summary statistics.
  • Total Sum of Sales: Use the SUM function.
  • Average of Sales: Use the AVERAGE function.
  • Standard Deviation of Sales: Use the STDEV function.
  • Variance of Sales: Use the VARP function.
  • Maximum Sales: Use the MAX function.
  • Minimum Sales: Use the MIN function.
• Apply the necessary formatting to present the results clearly.

c. Creating a Pivot Table:

• Select the dataset.
• Go to the "Insert" or "Data" tab and choose "Pivot Table" or "Pivot Table & Chart."
• Set the desired fields in the pivot table:
  • Place the "Status" field in the Rows area.
  • Place the "ProductLine" field in the Columns area.
  • Place the "Sales" field in the Values area.
• Apply any necessary formatting to the pivot table.

d. Creating a Customized Ribbon:

• Open the spreadsheet software's options or settings.
• Look for an option to customize the ribbon or toolbar.
• Create a new ribbon and name it "ABC."
• Add the desired data formatting commands to the newly created ribbon.

e. Analyzing Quantities Ordered:

• Create a new section or sheet in the spreadsheet to perform calculations.
• Use a pivot table to analyze quantities ordered by each country in different years and quarters:
  • Place the "Country" field in the Rows area.
  • Place the "Year" and "Quarter" fields in the Columns area.
  • Place the "QuantityOrdered" field in the Values area, and set it to summarize by sum.
• Apply any necessary formatting to the pivot table.

f. Conditional Formatting:

• Select the range of cells containing the "Status" and "ProductLine" values.
• Go to the formatting or conditional formatting options.
• Create rules to apply different colors based on the values in the "Status" and "ProductLine" fields.

g. Cleaning the Data:

• Identify the column with the date values.
• Select the column and apply the appropriate date formatting.
• Use the find and replace functionality to change the values of 2003, 2004, and 2005 to 2019, 2020, and 2021, respectively.

h. Calculating Total Sales:

• Create a new section or sheet in the spreadsheet to perform calculations.
• Use the SUMIF or SUMIFS function to calculate the total sales for each status category.

i. Adding New Fields with Text Functions:

• Identify the column with the ProductCode values.
• Insert two new columns adjacent to the ProductCode column.
• Use text functions like LEFT, RIGHT, MID, or SPLIT to extract the desired parts of the ProductCode into the new columns.

j. Highlighting the Top 10% Values:

• Select the range of cells containing the "QuantityOrdered" values.
• Go to the formatting or conditional formatting options.
• Create a rule to highlight the top 10% of values based on the "QuantityOrdered" field.