Smooth Data 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.
Certain lines of business lend themselves to wide fluctuations in data from month to month. For instance, a consulting practice may go months without a steady revenue stream before a big contract comes along and spikes the sales figures for a few months. Some call these ups and downs seasonality or business cycles.
Whatever you call them, wild fluctuations in data can prevent you from effectively analyzing and presenting trends. This figure demonstrates how highly volatile data can conceal underlying trends.
This is where the concept of smoothing comes in. Smoothing does just what it sounds like — it forces the range between the highest and lowest values in a dataset to smooth to a predictable range without disturbing the proportions of the dataset.
Now, you can use lots of different techniques to smooth a dataset. Take a moment to walk through two of the easier ways to apply smoothing.
Smoothing with Excel’s moving average functionality
Excel has a built-in smoothing mechanism in the form of a moving average trend line. That is, a trend line that calculates and plots the moving average at each data point. A moving average is a statistical operation used to track daily, weekly, or monthly patterns.
A typical moving average starts calculating the average of a fixed number of data points, then with each new day’s (or week’s or month’s) numbers, the oldest number is dropped, and the newest number is included in the average. This calculation is repeated over the entire dataset, creating a trend that represents the average at specific points in time.
This figure illustrates how Excel’s moving average trend line can help smooth volatile data, highlighting a predictable range.
In this example, a four-month moving average has been applied.
To add a moving average trend line, follow these steps:
-
Right-click the data series that represents the volatile data, and then select Add Trendline.
The Format Trendline dialog box appears, shown in this figure.
-
In the Format Trendline dialog box, select Moving Average, and then specify the number of periods.
In this case, Excel will average a four-month moving trend line.
Creating your own smoothing calculation
As an alternative to Excel’s built-in trend lines, you can create your own smoothing calculation and simply include it as a data series in your chart. In this figure, a calculated column (appropriately called Smoothing) provides the data points needed to create a smoothed data series.
In this example, the second row of the smoothing column contains a simple average formula that averages the first data point and the second data point. Note that the reference to the first data point (cell D2) is locked as an absolute value with dollar ($) signs. This ensures that when this formula is copied down, the range grows to include all previous data points.
After the formula is copied down to fill the entire smoothing column, it can simply be included in the data source for the chart. The following figure illustrates the smoothed data plotted as a line chart.
