Microsoft Excel is known to offer a lot of options when it comes to performing trend analysis in Excel.
As the name suggests, trend analysis Excel is quite a useful tool that can help you make the appropriate business decision and has been adopted widely in Inventory Control, Operations, Finance, Marketing, and Sales.
In this article, we shall be learning about the different choices and options that are available to us that can aid us in finding the trend analysis Excel in our data.
Also, we will be focusing primarily on the quantities techniques.
Four Methods that Can Be used for Trend Analysis in Excel
Here, we will be focusing on the following methods that will help you identify the changes in the trend analysis Excel data:
- Graphical Plotting
- Using Averages (or making use of moving averages)
- Using Forecast and Trend Function
- Studying data by using running regression analysis
As an example, we will be utilizing the following set of data:
Quebec: Car Sales from 1960-1968 (Download)
PS: If you wish to know the best root cause analysis tools, and how to recover an unsaved Excel file, just read more.
Graphical Plotting – Visual Performance and Plotting a Graph
In most cases, the first step that you need to take to analyze any type of data is to plot it. In this case, we shall be considering the time-series data of car sales.
From the sheet, you will see that the data consists of the number of cars sold in Quebec from 1960 to 1968. If we consider each month, the data will consist of 72 data points, with each point representing a single month.
Plotting the data can be done by heading towards the Insert tab, then to Charts, and the Insert the Line Chart; alternatively, you can also tap on Alt+N+N+Enter to insert this chart.
In the corresponding figure, you will see a clear trend; the cyclic nature of the data will be visible, with an increasing trend. This type of representation shows seasonality in the shorter term, whereas an increase in the trends is good news for the car manufacturers as it demonstrates improvement in the overall car sales demand.
Mostly, moving averages are used when you want to illustrate a more robust and quantitative basis for the overall decision-making process. This moving average makes use of the old past data and provides a result that is quite realistic.
Let us take the example of car sales again and add the moving average element to it so that the comparison between the two can be studied. You can check the table below out for an idea:
On the current cell’s lower right side, you need to double-tap on the green-outlined box; you will see the formula filling down from the column.
As a result, you will see a sequence of averaged values stacked against the authentic sales value. Once plotted, you will be able to plot the line chart to end up with the following result:
Here, you will see the original data that is illustrated by the blue line. Meanwhile, the maroon line represents the moving average. Here, you will see a ‘lag’ in the estimated value.
This is one of the main drawbacks of using the moving average. However, you will also observe that the moving average has accounted for the trend analysis of Excel in data as well as seasonality.
Making use of Forecast and Trend Functions
Most did not know this, but Excel has an in-built function that can do forecasting. To use this forecast function (i.e., FORECAST () FUNCTION), you need to use the second data sheet with values from a scientific experiment. If you choose right, you should see a table that looks something like this:
On this syntax, we can make use of the FORECAST function
FORECAST (x, known_x’s, known_y’s)
From here, you need to head towards the formula bar, enter the formula mentioned above, and enter the following arguments:
- X is the value you will be making a forecast for
- known_x’s are the known values of x
- known_y’s are the known values of y, against the respective values of x
Once you tap on entering, the formula will offer you the forecast for the given value of x.
From here, you need to note before plotting that you can always have the choice to plan the data to study the trend analysis in Excel. If you take similar arguments, a similar type of forecast can be predicted as well by using the TREND () function.
The results will be very much similar to what you have achieved via the Forecast function.
Using Regression Analysis for Trend Analysis in Excel
Finally, we will discuss the last trend analysis Excel tool, the Regression Analysis. You need to enable the trend analysis Excel tool pack for this to run. This can be done by heading to options, Add-ins, the Go, and finally, the Analysis Tool Pack.
Once you have enabled this feature, you can see the option available on the Data Tab. On tapping the Data Analysis button, you will be shown the dialogue boxes illustrated below; we have used the same to refer to ranges and provide the trend analysis Excel to you.
In the output you see below, we have these terms that we can use instantaneously:
Adjusted R-Square and R-Square: These two will explain how well this model is explained in this data. We are sure that our data is 95% and 90% explained; this is a sign of positivity. The X-Variable and the Intercept also explain the equation that is represented by:
Y= 8.8 X -19.15
You can also make use of this equation to plot the data manually.
Can You Analyze the Trends in Data with Sparklines?
Yes, you definitely can! Sparklines are small charts you see inside separate worksheet cells that can be used to show a trend analysis in Excel or visually represent your data and display the trend in the data.
Sparklines will be able to draw attention to more important tabs like economic cycles and seasonal changes. Additionally, the minimum and maximum values will be represented by different colors.
With the help of sparklines, showing trends on your data becomes much easier, especially if you share it with others.
Here is how you can do this:
- Tap on a blank cell near the data you want to illustrate in a sparkline.
- Then, you need to tap on the Insert button; from there, you head to the Sparklines set on Column, Line, or Win/Loss.
- Head to the Data Range box, where you will have to put in the cell ranges containing the data you want to display in the sparkline. For instance, if your data is located in A, B, C, and D of Row 3, you need to enter A3: D3.
- Alternatively, you can select the range of cells on the worksheet directly. You can temporarily collapse the dialog box and then bring it back again.
- Once you tap OK, you will see the Sparkline Tools section on the ribbon. You can use guidelines on the Design tab to tailor your sparklines according to your preferences.
How Do You Customize Your Sparklines?
Once you are done with creating your sparklines, you can change their format, style, and type at any time:
- You first need to select the sparklines that you wish to customize.
- To do this, you need to head toward the Design tab and choose the options you want.
- Here, you can change the format or style of the sparklines and the axis settings.
- On tapping the Date Axis Type option, Excel will open a new dialog box called Sparkline Date Range; here, you can select the range of your workbook that has all the data values that you are looking for in your sparkline data.
- Once the customization is done, you will see the results in your Excel sheet.
The above three are the most common methods of trend analysis in Excel. The first one is the most basic form and only makes use of the graphical presentation of the available data. The second one made use of the past data and averaged it out.
The last one used a formal regression analysis procedure provided in Excel’s data analysis tool pack and is today the standard procedure to assess relationships and trends. You can also download the sample file from this link.