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

a. Data Formatting

• Borders & Alignment: Select your dataset, navigate to the Home tab, and apply All Borders. Use Center Alignment for headers and appropriate alignment for data cells.​

• Font Size: Adjust the font size to enhance readability, typically size 11 or 12.​

---

b. Summary of Product Sales

Utilize Excel functions to compute the following:

• Total Sum of Sales: =SUM(SalesColumn)​

• Average of Sales: =AVERAGE(SalesColumn)​

• Standard Deviation of Sales: =STDEV.S(SalesColumn)​

• Variance of Sales: =VAR.S(SalesColumn)​

• Maximum Sales: =MAX(SalesColumn)​

• Minimum Sales: =MIN(SalesColumn)​

Replace SalesColumn with the actual range of your sales data.

---

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

1. Select your dataset.​

2. Go to Insert > PivotTable.​

3. In the PivotTable Field List:​

   • Drag ProductLine to Rows.​

   • Drag Status to Columns.​

   • Drag Sales to Values.​

This will provide a matrix of sales figures segmented by product line and status.

---

d. Customized Tab Named 'ABC'

To create a custom tab:

1. Right-click on the ribbon and select Customize the Ribbon.​

2. Click New Tab, rename it to 'ABC'.​

3. Add desired commands (e.g., formatting tools) to this tab for quick access.​

---

e. Quantities Ordered by Country and Year/Quarter Using Pivot Table

1. Ensure your dataset includes columns for Country, OrderDate, and QuantityOrdered.​

2. Add helper columns:​

   • Year: =YEAR(OrderDate)​

   • Quarter: =CHOOSE(MATCH(MONTH(OrderDate),{1,4,7,10}),"Q1","Q2","Q3","Q4")​

3. Insert a PivotTable:​

   • Rows: Country​

   • Columns: Year, then Quarter​

   • Values: Sum of QuantityOrdered​

This will display quantities ordered per country across different years and quarters.

---

f. Conditional Formatting for 'Status' and 'ProductLine'

1. Select the columns Status and ProductLine.​

2. Go to Home > Conditional Formatting > New Rule.​

3. Choose Format only cells that contain.​

4. Set the rule to format cells equal to specific text (e.g., "Shipped") and assign a distinct color.​

Repeat this process for each unique value in both columns to assign different colors.

---

g. Update Year Values Using Date Formatting

If your dataset has years like 2003, 2004, and 2005 that need to be updated to 2019, 2020, and 2021 respectively:

1. Add a helper column with the formula:​

   • =YEAR(OrderDate) + 16​

2. Replace the original year with the new value as needed.​

Ensure that this change aligns with your data analysis requirements.

---

h. Total Sales by Specific Statuses

To calculate total sales for statuses like "Shipped", "Cancelled", etc.:

• Use the formula:​

  • =SUMIFS(SalesColumn, StatusColumn, "Shipped") + SUMIFS(SalesColumn, StatusColumn, "Cancelled") + ...​

Replace SalesColumn and StatusColumn with the actual ranges, and continue the formula for each status.

Alternatively, create a PivotTable:

1. Rows: Status​

2. Values: Sum of Sales​

Then, filter the PivotTable to show only the desired statuses.

---

i. Split 'ProductCode' into Two Columns

Assuming 'ProductCode' is in the format "S10_1678":

1. Use the formula:​

   • =TEXTBEFORE(ProductCode, "_") for the prefix (e.g., "S10")​

   • =TEXTAFTER(ProductCode, "_") for the suffix (e.g., "1678")​

Ensure your Excel version supports these functions; if not, consider using LEFT, RIGHT, and FIND functions.

---

j. Highlight Top 10% Values in 'QuantityOrdered'

1. Select the 'QuantityOrdered' column.​

2. Navigate to Home > Conditional Formatting > Top/Bottom Rules > Top 10%...​

3. Choose a formatting style to highlight these values.​