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

a. Formatting the data:

• Open the dataset in Excel.
• Select the data range that you want to format.
• Apply borders, adjust font size, and align the content as desired using the formatting options in the Excel toolbar.

b. Summary of Product Sales:

• Use the SUM, AVERAGE, STDEV, VAR, MAX, and MIN functions to calculate the respective values for the sales column.

c. Analyzing sales by product line and status using a pivot table:

• Select the entire dataset, including headers.
• Insert a pivot table based on the selected range.
• Add the "Product Line" field to the rows section and the "Status" field to the columns section of the pivot table.
• Add the "Sales" field to the values section of the pivot table.
• Summarize the sales data by product line and status.

d. Creating a customized tab:

• Insert a new tab in Excel by clicking on the "+" icon at the bottom.
• Rename the tab by right-clicking on the tab and selecting "Rename."
• Apply data formatting commands to the newly created tab as desired.

e. Analyzing quantities ordered by country, year, and quarter using a pivot table:

• Select the entire dataset, including headers.
• Insert a pivot table based on the selected range.
• Add the "Country" field to the rows section, the "Year" field to the columns section, and the "Quarter" field (if available) to the columns section of the pivot table.
• Add the "Quantity Ordered" field to the values section of the pivot table.
• Summarize the quantities ordered by country, year, and quarter.

f. Conditional formatting for values in the "Status" and "ProductLine" fields:

• Select the range containing the "Status" and "ProductLine" fields.
• Apply conditional formatting options in the Excel toolbar to assign different colors based on the values in these fields.

g. Changing values of 2003 to 2019, 2004 to 2020, and 2005 to 2021:

• Select the range containing the dates.
• Use the find and replace function in Excel by pressing Ctrl + H.
• Replace 2003 with 2019, 2004 with 2020, and 2005 with 2021.

h. Calculating the total sales of specific product statuses:

• Use the SUMIF function to calculate the sum of sales based on specific criteria.
• For example, if the statuses are in column A and the sales are in column B, use the formula: =SUMIF(A:A, "Shipped", B:B) to calculate the total sales for "Shipped" products.
• Repeat the formula for other statuses ("Cancelled", "Disputed", etc.) to calculate their respective total sales.

i. Splitting the product code field into two using text functions:

• Assuming the product code is in column A, use the following formulas in adjacent columns:
  • To extract the first part (e.g., "S10"): =LEFT(A1, FIND("_", A1)-1)
  • To extract the second part (e.g., "1678"): =MID(A1, FIND("", A1)+1, LEN(A1)-FIND("", A1))

j. Highlighting the top 10% values of the QuantityOrdered field using conditional formatting:

• Select the range containing the "QuantityOrdered" field.
• Apply conditional formatting options in the Excel toolbar to highlight the top 10% values based on the values in the field.

Please note that the steps provided are general guidelines, and the exact steps may vary slightly depending on the specific version of Excel you are using.