Use Directional Trending in Your Excel Dashboards and Reports
Trending is very popular in Excel dashboards and reports. A trend is a measure of variance over some defined interval — typically time periods such as days, months, or years. A directional trend is one that uses simple analysis to imply a relative direction of performance.
Do you work with a manager that’s crazy for data? Are you getting headaches from trying to squeeze three years of monthly data into a single chart? Although it’s understandable to want to see a three-year trend, placing too much information on a single chart can make for a convoluted trending component that tells you almost nothing.
When you’re faced with the need to display impossible amounts of data, step back and think about the true purpose of the analysis. When your manager asks for a three-year sales trend by month, what’s he really looking for?
It could be that he’s really asking whether current monthly sales are declining versus history. Do you really need to show each and every month or can you show the directional trend? The key attribute of a directional trend is that the data used is often a set of calculated values as opposed to actual data values.
For instance, instead of charting each month’s sales for a single year, you could chart the average sales for Q1, Q2, Q3, and Q4. With such a chart, you’d get a directional idea of monthly sales, without the need to look into detailed data.
Take a look at this figure, which shows two charts. The bottom chart trends each year’s monthly data in a single chart. You can see how difficult it is to discern much from this chart. It looks like monthly sales are dropping in all three years.
The top chart shows the same data in a directional trend, showing average sales for key time periods. The trend really jumps at you, showing that sales have flattened out after healthy growth in 2011 and 2012.