Website Home Page |

Excel Assignments List

| Froyo Technologies Public Resources

 

| Product Name | Category    | Price | Quantity | Sales Date |

|--------------|-------------|-------|----------|------------|

| Laptop       | Electronics | 1000  | 5        | 2023-01-01 |

| T-shirt      | Clothing    | 20    | 10       | 2023-01-05 |

| Smartphone   | Electronics | 500   | 8        | 2023-01-10 |

Task :

1. Create a PivotTable to summarize total sales by Category.
2. Add a calculated field to the PivotTable to calculate profit margin (Sales - Total Cost).
3. Insert a PivotChart based on the PivotTable created.
4. Filter the PivotTable to show only Electronics category products.
5. Group the Sales Date field in the PivotTable by month and year.

To perform the tasks outlined in Assignment  using Excel, follow these step-by-step instructions:

Step 1: Creating PivotTable

·         Steps:

1. Select the data range (assuming A1 to E4).
2. Go to Insert > PivotTable.
3. In the Create PivotTable dialog box, ensure the range is correct and choose where to place the PivotTable.
4. Drag "Category" to Rows and "Quantity" (or "Price" if considering revenue) to Values.
5. Rename "Sum of Quantity" to "Total Sales" (or "Total Revenue").
6. Your PivotTable should now show total sales by category.

Step 2: Adding Calculated Field

·         Steps:

1. With the PivotTable selected, go to PivotTable Analyze > Fields, Items & Sets > Calculated Field.
2. Name the calculated field (e.g., Profit Margin).
3. Enter the formula to calculate profit margin:

= 'Sales' - 'Total Cost'

Adjust the field names as per your PivotTable setup.

4. Click OK. The calculated field will now appear in your PivotTable.

Step 3: Inserting PivotChart

·         Steps:

1. With the PivotTable selected, go to PivotTable Analyze > PivotChart.
2. Choose the chart type you want to create (e.g., Column chart, Bar chart).
3. Customize the PivotChart layout and formatting as needed.
4. The PivotChart will now be linked to the PivotTable and update dynamically.

Step 4: Filtering Data in PivotTable

·         Steps:

1. Click on the drop-down arrow next to "Category" in the PivotTable.
2. Uncheck the box for categories you want to hide (e.g., Clothing).
3. Click OK or Apply to filter the data.
4. The PivotTable will now display data only for the Electronics category.

Step 5: Grouping Dates in PivotTable

·         Steps:

1. Right-click on any date in the Sales Date field in the PivotTable.
2. Select "Group..."
3. In the Grouping dialog box, select "Months" and "Years".
4. Click OK. The Sales Date field will now be grouped by month and year in the PivotTable.