• 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

    Dashboards & Interactivity

    What You’ll Learn:

    • How to build an interactive dashboard

    • Use slicers to filter pivot tables and charts dynamically

    • Apply dynamic named ranges to auto-update your charts

    • Combine charts and filters to make data exploration intuitive

    Prerequisites:

    • Understanding of pivot tables and charts

    • A base dataset with multiple rows and dimensions (e.g., sales, product, region, date)

    Step 1: Sample Dataset – Sales Report

    Paste this data into your 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
    2025-07-04 Ravi North Shoes 5 3750
    2025-07-04 Meera West Jeans 4 1600

     

    Step 2: Create Pivot Tables

    1. Select the dataset → Go to Insert > Pivot Table

    2. Choose New Sheet

    3. Add Rows: Salesperson

    4. Add Values: Revenue (Summarize by: SUM)

    5. Create another pivot table for:

      • Rows: Product

      • Columns: Region

      • Values: Units Sold

    Step 3: Insert Charts from Pivot Tables

    1. Select the pivot table range

    2. Click Insert > Chart

    3. Choose appropriate chart type:

      • Bar chart for revenue by salesperson

      • Column chart for product-wise region sales

    Step 4: Add Slicers for Interactivity

    A slicer allows you to filter pivot tables and charts from one central filter.

    Steps:

    1. Click on your pivot table or chart

    2. Go to Data > Add a slicer

    3. Set slicer column (e.g., Region or Salesperson)

    4. Position the slicer near your dashboard area

    5. When clicked, slicer filters all linked tables/charts

    Note: Make sure slicers are connected to the correct pivot tables (you can check using the slicer settings gear icon)

    Step 5: Create Dynamic Named Ranges (Optional – Advanced)

    If your dataset is growing over time, use a dynamic named range so pivot tables auto-update.

    1. Select Data > Named ranges

    2. Name: SalesData

    3. Formula:

    =QUERY(Sheet1!A:F, "select * where A is not null", 1)

    1. Use SalesData in your pivot table and chart sources

    Step 6: Organize Your Dashboard

    1. Rename sheet: Dashboard

    2. Move charts to the top half

    3. Place slicers on the left or top-right

    4. Add summary metrics using formulas:

      • Total Revenue: =SUM(F2:F100)

      • Top Salesperson: Use SORT & INDEX

    5. Format charts with clear titles and legends

    Mini Project: Interactive Sales Dashboard

    Objective: Build a live dashboard where a user can:

    • Select region or salesperson using slicers

    • View pivot-based summaries

    • See visual insights in charts

    ❮ Previous Next ❯