Add a trend arrow to dashboard

  • I have 12 months of data - cells E7 to P7, in cell Q7 I want to place an arrow (Up, Down Sideways) based on the trend for the previous 12 months

    Looking for the calculation and how to format it, to achieve the result


  • Re: Add a trend arrow to dashboard

    Probably best to upload a sample workbook so we have sufficient details of your needs if answer is not what you need/want.
    I will assume you want to track the trend for the current month compared to previous month.

    We'll use Conditional Formatting using an icon set.

    Q7 formula: =--(INDEX(E7:P7,COUNT(E7:P7))>INDEX(E7:P7,COUNT(E7:P7)-1))

    (Note: you can format the cell font to be same color as cell to hide the formula result so that only the arrow is visible in the cell)

    Select Q7 and click on Conditional Formatting (Home tab in ribbon).
    Choose the Arrow icon set, then edit the rule.

    Icon1: green up arrow
    Value1: Select >, then enter 0 (zero) in the box
    Type1: set this to use Number type

    Icon2: change yellow arrow to red down arrow
    Value2: Select >=, then enter 0 (zero) in the box
    Type2: set to use Number

    Icon3: red down arrow (default)

    Alternately, you could use a Sparkline in Q7.
    Select Q7 then select Insert on Ribbon Menu, locate and click on "Line" in the Sparklines section.
    Select E7:P7 as the data range, click OK - cell will show a trend line for the data.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!