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

To perform the tasks outlined for Company ABC Private Limited, follow these steps in Excel:

a. Proper Formatting of Data

1. Borders and Alignment:

   • Select the entire dataset.
   • Apply borders by navigating to the "Home" tab -> "Borders" -> "All Borders".
   • Adjust alignment: For text, use "Align Left" or "Center". For numerical data, use "Align Right" or "Center".

2. Font Size:

   • Select the dataset and change the font size to 11 or 12 for better readability under the "Home" tab.

b. Summary of Product Sales

1. Total Sum of Sales:

   • Use the formula: =SUM(SalesColumn)

2. Average of Sales:

   • Use the formula: =AVERAGE(SalesColumn)

3. Standard Deviation of Sales:

   • Use the formula: =STDEV.P(SalesColumn)

4. Variance of Sales:

   • Use the formula: =VAR.P(SalesColumn)

5. Maximum Sales:

   • Use the formula: =MAX(SalesColumn)

6. Minimum Sales:

   • Use the formula: =MIN(SalesColumn)

c. Sales Analysis by Product Line & Status using Pivot Table

1. Insert Pivot Table:

   • Select your data range.
   • Go to "Insert" -> "PivotTable".
   • Place it on a new worksheet.

2. Configure Pivot Table:

   • Drag "Product Line" to the Rows area.
   • Drag "Status" to the Columns area.
   • Drag "Sales" to the Values area and set it to sum the sales.

d. Create Customized Tab Named ABC

1. Create Tab:
   • Go to "File" -> "Options" -> "Customize Ribbon".
   • Click "New Tab" and rename it to "ABC".
2. Add Commands:
   • Add commonly used commands such as "Bold", "Italic", "Conditional Formatting", "Format Cells", etc., by dragging them into the "ABC" tab.

e. Quantities Ordered by Country for Different Quarters (2019-2021)

1. Insert Pivot Table:

   • Select your data range.
   • Go to "Insert" -> "PivotTable".
   • Place it on a new worksheet.

2. Configure Pivot Table:

   • Drag "Country" to the Rows area.
   • Drag "Order Date" to the Columns area and group by "Years" and "Quarters".
   • Drag "Quantity Ordered" to the Values area and set it to sum the quantities.

f. Conditional Formatting for Status and Product Line

1. Apply Conditional Formatting:

   • Select the cells in the "Status" column.
   • Go to "Home" -> "Conditional Formatting" -> "New Rule".
   • Use a formula or predefined formats to set different colors based on the value.

   Repeat the process for the "Product Line" column.

g. Change Year Values Using Date Formatting

1. Using Replace:

   • Select the "Order Date" column.
   • Use Ctrl+H to open the Replace dialog.
   • Replace "2003" with "2019", "2004" with "2020", and "2005" with "2021".

2. Using DATE Function:

   • If dates are in text format, use =DATE(YEAR(A2)+16, MONTH(A2), DAY(A2)) to adjust the years accordingly.

h. Calculate Total Sales by Status

1. Insert Pivot Table:

   • Select your data range.
   • Go to "Insert" -> "PivotTable".
   • Place it on a new worksheet.

2. Configure Pivot Table:

   • Drag "Status" to the Rows area.
   • Drag "Sales" to the Values area and set it to sum the sales.

i. Split Product Code

1. Using Text Functions:
   • Assume product code is in column A.
   • Use =LEFT(A2, FIND("_", A2)-1) to get the first part.
   • Use =MID(A2, FIND("_", A2)+1, LEN(A2)) to get the second part.

j. Highlight Top 10% Values of Quantity Ordered

1. Conditional Formatting:
   • Select the "Quantity Ordered" column.
   • Go to "Home" -> "Conditional Formatting" -> "Top/Bottom Rules" -> "Top 10%".
   • Set the formatting style you want to apply.

Following these steps should enable you to format and analyze the dataset effectively, providing valuable insights for Company ABC Private Limited.