Datanalytics

3.2-Pivot Table Analysis Creation, Formatting, What If Analysis

3 min read

2025-08-29 19:42

Status: Child

Tags: Excel Data Analysis Power-Pivot

PivotTable Analysis:

Calculated Fields/Items, Date Grouping, Sorting/Filtering, Slicers (Creation, Formatting, Dynamic Use)

This guide provides a comprehensive overview of creating, formatting, and dynamically using PivotTables in Microsoft Excel for advanced data analysis. It covers calculated fields/items, date grouping, sorting/filtering, slicers, and what-if analysis.

Calculated Fields and Items

Revenue Calculation

To calculate revenue in a PivotTable:

  • Formula: Revenue = Unit Price × Quantity Sold
  • Steps:
    1. Create a PivotTable from your dataset.
    2. Navigate to PivotTable Analyze > Fields, Items & Sets > Calculated Field.
    3. Select the Price and Quantity fields.
    4. Enter the formula: =Price*Quantity.
    5. Name the new field (e.g., “Revenue”) and add it to the PivotTable.

Agent or Branch-Wise Revenue

  • Create a PivotTable with Agent or Branch in the Rows area and Revenue in the Values area.
  • Sort: Right-click the PivotTable, select Sort > Sort A to Z in the settings.
  • Formatting: Apply standard formatting (e.g., currency format for revenue, bold headers).

Sorting and Filtering

  • Top/Bottom Filters:
    • Use Value Filters to display top or bottom performers (e.g., top 3 agents by revenue).
    • Filter by branch name to focus on specific regions.
  • Value Field Settings:
    • Summarize data using options like Sum, Average, or Standard Deviation.
    • Display values as:
      • Percent of Grand Total
      • Difference from Total
      • Grand Total

Date Grouping

  • Steps:
    1. Add a Date field to the Columns or Rows area.
    2. Right-click a date, select Group, and choose Quarters (or other intervals like Months or Years).
    3. This transforms monthly data into quarterly summaries.
  • Trend Visualization:
    • Insert a Line Sparkline to visualize trends:
      1. Go to Insert > Line > Sparkline.
      2. Customize with options like Win/Loss, Column, or change colors.
      3. Highlight High/Low Points, First/Last Points, or add Markers.
      4. Format with light border colors for all borders.

Slicers

Slicers provide an interactive way to filter PivotTable data.

  • Steps:
    1. Go to Insert > Slicer.
    2. Select columns with categorical data (e.g., Branch: Central, North County, Westside).
    3. A slicer appears, allowing quick filtering by clicking options.
  • Best Practice: Choose columns with fewer unique values for effective slicers.
  • Timeline Slicer:
    • For date-based filtering, insert a Timeline slicer:
      1. Go to Insert > Timeline.
      2. Filter by specific days, months, quarters, or years (e.g., July 2017).
    • Use for time-series analysis or to identify periods with no sales.

What-If Analysis

What-if analysis helps evaluate scenarios, such as determining quantities needed to achieve a revenue goal.

  • Example Scenario:
    • New business with a product price of 8,523 in revenue.
    • Goal: Determine the quantity needed to achieve $6,000 in revenue.
  • Steps:
    1. Go to Data > What-If Analysis > Goal Seek.
    2. Set parameters:
      • Set Cell: The cell containing the final result (e.g., revenue).
      • To Value: The desired result (e.g., $6,000).
      • By Changing Cell: The variable cell (e.g., quantity).
    3. Ensure a formula (e.g., Price*Quantity) is in place for accurate analysis.
  • Profit Calculation:
    • Formula: Profit = Revenue - Total Cost
    • Use Goal Seek to adjust variables like quantity or price to meet profit targets.
  • For Multiple Products/Prices:
    • Use Microsoft’s Solver add-in for complex scenarios involving multiple variables.

References

  • Microsoft Excel Documentation
  • PivotTable and What-If Analysis Tools

References

Backlinks