How to add a trend arrow to dashboard
Category: [Excel] Demo Available
How to add a trend arrow to dashboard
Requirement:
The user has 12 months of data - cells E7 to P7, in cell Q7 the user wants to place an arrow (Up, Down Sideways) based on the trend for the previous 12 months
Solution:
Assuming 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.
Obtained from the OzGrid Help Forum.
Solution provided by AAE.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to add lines to a chart |
| How to generate multiple line charts VBA |
| How to resize word charts/pictures in excel |
| How to automate charts with 1 x - axis and multiple y - axis in VBA |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.