Graphing on Microsoft Excel
Navigate the knowledge tree: 🌿 Skills ➡ Life Processes
When you need to display data in a graph, you can draw the graph by hand, or use a computer software package, such as Microsoft Excel, to produce a graph for you.
Microsoft Excel can be used to generate a graph and also to add a trend line. For example, you may be interested in determining whether there is a relationship between altitude and air temperature. Imagine you have collected the data in the following table.
When you generate a graph in Excel, remember that the data is discontinous, so make sure that there are no lines connecting the data points.Â
Then, you want to add and edit your chart elements. Click through the slides below to see the chart elements you need to add and edit.
To add a trend line in Excel, follow these steps.
Click on the graph area to access the Chart Design tab.
Go to Add Chart Element.
A dropdown menu will open and you can select the type of trend line you want to add. In most cases, you will want to add a linear trend line.
In the same dropdown menu, you can select More Trendline Options.
Clicking this option allows you to decide whether you would like the equation of the trend line to appear on the graph. This can be useful if you want to find the mathematical relationship between the two variables.
When you have done this, your graph should look like the following example.
The closer the data points are to the trend line, the more strongly correlated the two variables are. For the graph above, air temperature and altitude are negatively correlated. This means that as altitude increases, air temperature decreases.
By finding the gradient of the trend line, you can also determine what the relationship is between the two variables. For the trend line above, the gradient is -0.0057. This means that for every 1 m of elevation, the temperature will drop 0.0057 °C. Alternatively, if you multiply both 1 m of elevation and -0.0057 °C by 1000, you could say that for every 1000 m of elevation, the temperature will drop by 5.7 °C.