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


    Thanks

  • 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.