Contents
Since Excel tables are most often used for various calculations, budgeting, accounting for goods at a certain place, the information added to the cells can be systematized and based on its forecast for the future. The trend of further development of a certain situation is easiest to consider along the trend line. It is necessary to talk in more detail about how to add, configure and use the data of Excel table elements.
General information
A trend line is one of the varieties of Excel charts, which is an approximating function. This tool is used to make forecasts for a certain period of time based on available statistical data. This function is very useful when building various economic models, performing calculations regarding the profitability of the development of certain outlets, compiling annual reports with a forecast for the coming months, etc.
Varieties
Before understanding how to draw a trend line, it is necessary to consider all the varieties of this function. Each of them has its own characteristics, designed for certain situations, conditions.
Linear approximation
Represents a straight line in the overall image. It is used to create such graphs, where data over the entire time period decreases or increases at the same speed, without sudden changes. Using data from a worksheet, a scatter plot can be constructed using a line fit.
Important! Since the linear approximation is a straight line, the data along it will be marked with points that will be located at a certain distance from the guideline (as close as possible).
Polynomial
It looks like a curve. Its characteristic features are periodic decrease and increase. This type of trend is used in Excel spreadsheets to analyze the data set to reveal the instability of the value. The main advantage of the polynomial trend is the high accuracy of future forecasts compared to other trends.
logarithmic
This trend is used in situations where you need to build a graph with a sharp decrease or increase, after which there is a period of stability. In appearance, it can be called an “optimized” curve, which adapts well to sharp jumps and stable areas in the general data array.
Advice! The most relevant direction for using the logarithmic trend is the sales forecast for a particular product. At the same time, it should only be introduced to the market, and the schedule will determine how it was perceived by buyers.
Exponential
The main feature of the exponential trend is that it cannot be used in situations where the table has negative or zero characteristics.. Such a graph effectively shows itself in those works where all input values are static or slowly increase.
Important! We must not forget that there are certain charts and graphs in Excel spreadsheets that cannot be trended. These include circular, annular, volumetric and other elements.
How to add a trend line
To add a trend to a working document, you need to complete two steps. The first is plotting a chart, the second is creating the trend line itself. Each of them needs to be discussed in more detail. Graphing process:
- Initially, you need to build a simple table with two columns. In the first, write down the time frame by numbers. In the second, add the values that will be displayed on the finished chart.
- Select the table (with the mouse or by pressing the “Shift”, “Ctrl” keys).
- Go to the “Insert” tab. Go to the list with “Charts” tools.
- In the window that appears, select the first chart.
The chart is ready, but for further work with it, it needs to be configured. First of all, you need to give a name to this element of the table. To do this, go to the “Chart Name” item, which is located on the main toolbar. Select the option to add a title – “Above the chart”.
A free field will open above the chart picture, where you need to enter its name. After that, go to the “Axis Names” tab (located in the same tab as the previous setting). Click on the “Name of the main horizontal axis” button, select the “Name under the axis” parameter. A free field should open in which you need to write the name of the horizontal axis.
Next, you can add a title for the vertical axis. It is necessary to repeat the same steps as described above, only select the item “Name of the main vertical axis”, and then click on the button “Rotated title”. Only due to such settings it is possible to achieve the most convenient appearance of the diagram for further work with it.
The next step is to create a trend line. Procedure:
- After adding and configuring a chart, there is no need to leave the chart editing tab.
- You need to go to the “Layout” item, click on the “Trend Line” button (it can be found in the block with tools called “Analysis”).
- Next, a list should appear in which you need to select the “Exponential Approximation” option (its second name is “Linear Approximation”).
After completing the steps above, a trend line should appear. Its color designation without additional editing is black. Next, you need to configure the added element:
- To do this, go to the “Layout” tab, go to the “Analysis” section, the “Trend line” item, select the editing mode.
- A window with numerous settings should appear on the screen. Here you can select a specific type of trend, set smoothing, change the color of the chart.
- To create a forecast based on the available data, you need to go to the tab of the same name, specify the number of periods for which the line will apply, and click the “Close” button.
Conclusion
If the trend line is added and set up correctly, it can be used to roughly evaluate your actions in the near future, avoid certain mistakes, and achieve significant success. We must not forget that each type of trend is suitable for certain situations, the accuracy of the forecast directly depends on this.