• Login / Signup

    • Welcome Back

    Google icon Sign in with Google
    OR
    Forgot Password?
    I agree to abide by Pharmadaily Terms of Service and its Privacy Policy

    Create Account

    Google icon Sign up with Google
    OR
    By signing up, you agree to our Terms of Service and Privacy Policy
    Instagram
    youtube
    Facebook

    Pivot Tables

    What You’ll Learn:

    • What pivot tables are and when to use them

    • How to summarize large data sets with just a few clicks

    • How to group, filter, and aggregate data

    • How to create a simple sales summary project using pivot tables

    Step 1: What is a Pivot Table?

    A pivot table is a tool that allows you to summarize, analyze, explore, and present large datasets. It rearranges and groups your raw data to help extract meaningful patterns.

    Step 2: Sample Dataset – Sales Log

    Enter the following data into a sheet:

    Date Salesperson Region Product Units Sold Revenue (₹)
    2025-07-01 Aman East T-Shirt 10 2000
    2025-07-01 Meera West Jacket 5 4000
    2025-07-02 Aman East Jeans 8 3200
    2025-07-02 Meera West Shoes 6 4500
    2025-07-03 Ravi North T-Shirt 7 1400
    2025-07-03 Aman East Jacket 3 2400

     

    Step 3: Insert a Pivot Table

    Steps:

    1. Select the entire table (A1:F7)

    2. Go to Insert > Pivot table

    3. In the dialog box, choose:

      • Create in new sheet

      • Click Create

    Now the Pivot Table Editor will open on the right.

    Step 4: Build a Summary Table – Total Revenue by Salesperson

    Steps in Pivot Table Editor:

    1. Rows → Add Salesperson

    2. Values → Add Revenue

      • Set Summarize by: SUM

    You now have a pivot table showing each salesperson’s total revenue.

    Step 5: Add More Analysis – Revenue by Product and Region

    Try the following setup:

    • Rows → Region

    • Columns → Product

    • Values → Revenue (SUM)

    This creates a matrix view of revenue per product in each region.

    Step 6: Apply Filters (Optional)

    To focus on specific data:

    1. In the Pivot Table Editor, add Filter → Salesperson

    2. Filter to show only “Aman” or others

    Mini Project: Build a Sales Dashboard

    Using the same dataset:

    • Create 3 pivot tables:

      1. Revenue by Salesperson

      2. Units Sold by Product

      3. Revenue by Region

    • Apply filters to compare data over time

    • Format the pivot table with currency formatting for ₹ values

    Bonus:

    • Create bar charts from the pivot table outputs

    ❮ Previous Next ❯