Forum Discussion

anupambit1797's avatar
anupambit1797
Iron Contributor
Mar 29, 2025

Data trend comparison

Dear Experts,

                   Greetings!

I have a data like below, in the Home Tab:-

What , I want to study is the trend of each KPI from Column "J" to "T", w.r.t to the comment, I give an example as below( but in this process , I need to go Pivot for each KPI :( , so for the KPI say - M55116C00012, the trend is like below so, it's increase when "Feature Off n20", reduces when "Feature on" and is min when "Feature off n50", but then it'll take 11 Pivots individually..

I tried to use something like sparklines but also not useful as difficult to see the trend w.r.t Comment(Feature Off n20, Feature Off n50 or Feature On)

Can we have any idea to create a Trend Sheet as below, with Sparklines in Column B,C,D:-

Thanks in Advance,

Br,

Anupam

BI & Data Analysis
excel
Formulas and Functions

2 Replies

  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Mar 30, 2025

    How about this:

     

    1. Using Conditional Formatting with Sparklines

    You could use sparklines for quick visualization in columns B, C, D for each KPI:

    • Introduce conditional formatting in the adjacent columns, highlighting cells based on values corresponding to the comments. This way, trends become easier to interpret alongside sparklines.
    1. Aggregating Data with Formulas

    Instead of PivotTables, consider using formulas like:

    • SUMIF() or AVERAGEIF() to aggregate data for specific KPI conditions (e.g., Feature Off n20).
    • Create a summary sheet where these formulas auto-calculate trends per KPI against each comment. This summary can then display sparklines reflecting those aggregated trends.
    1. Dynamic Tables and Charts

    Set up dynamic named ranges for your KPI data. Then use Excel charts (e.g., line charts or bar charts) to automatically update based on the selected comment filter (via slicers or dropdown menus). This approach gives a clear visual of the trends without needing multiple pivots.

    1. Power Query for Trend Automation

    Use Power Query to:

    • Unpivot the data in columns J-T (making comments a single column).
    • Group data by KPIs and comments to calculate trends. Once loaded back into Excel, it enables quick refresh and visualization.
    1. Excel VBA for Custom Trend Sheet

    If you're comfortable with VBA:

    • Write a macro to automate PivotTable creation for each KPI or even generate a combined Trend Sheet with sparklines pre-added.
    • anupambit1797's avatar
      anupambit1797
      Iron Contributor
      Mar 30, 2025

      Thanks Kidd_Ip Can you please share the solutions using 1,2,3,4 in the attached sheet.

       

      Thanks in advance

      Br,

      Anupam

Resources