re on the blue/green box corner and drag to include the other columns. Third, you can also make new charts of the same type as your original by copying the original chart, clicking on it, and then dragging the edge of the blue or green box until the new column of data is highlighted.
Once you’ve got your data on your graph, generating a trendline is easy. Click on the data on the graph, then right-click to bring up the formatting options. One of the options will be “Add Trendline.” Excel will give you several trendline options to choose between, as well as the option to display the trend’s equation and “R-squared” (R2) value on the chart. You’ll use the linear and polynomial trends most often, and you can add both by clicking “Add Trendline” twice. You should also display both the equation and the R2 value since they're what will tell you whether or not the data you’re sanity-checking makes sense.
If the data you’re working with comes from a scientist, you can compare your trends and R2 values against what is published in the paper. If your trend and/or R2 are close to what the paper says, then you can have some confidence in the results. If your numbers are significantly different, however, that’s a clue that something may not be right, and if you double-check your numbers and they’re still different, then it might be time to dig deeper into the paper or find expert help.
The reason you check both linear and polynomial trends is to see if there's a major difference in the R2 value between the two.The polynomial curve’s R2 value should always be higher than the linear trend’s R2, but if there is a big difference between the two (0.05 or more), then a linear approximation probably isn’t valid. Alternatively, if an accelerating trend is expected but the difference between the linear and polynomial trends’ R2 is 0.01 or less, then a linear approximation is more valid. In either case, if you’re sanity-checking someone else’s data and they’re claiming a trend that seems invalid from your check, then that’s a red flag. Don’t use polynomial trends with an order greater than two, however, as it’s easy to misinterpret the meaning of higher order polynomial trends.
The absolute value of R2 is as important as how the relative values of R2 for both types of trend are. Any R2 value that’s less than about 0.35 can be a result of random chance, so any conclusions based on such a low R2 should be well explained. The higher the R2 value is, the more certain it is that the trend is real.
While I’ve assumed data vs. time thus far, it’s also useful to graph one set of data vs. another, such as in the case of global temperature anomaly vs. atmospheric CO2 concentration. To do this, you generate a “scatter” chart (instead of a “line” chart) with CO2 on one axis and temperature on the other. Then you can generate both trends and the R2 values for comparison. In the case of CO2 and temperature, the positive trends and the high R2 values indicate that there’s a pretty strong correlation between high CO2 concentrations and higher temperature anomaly.
Cross-correlation of temp anomaly with CO2 concentration for CO2 lagging temp by 8 years. Note:1890 through 2000 are hidden for convenience.
Finally, while correlation does not prove causation, there is a way to check if causation is likely via correlations. To test causation, you run a set of what are known as “cross-correlations” between two sets of data. We do this with the Excel function “CORREL” where we select the two data series. In order to check if a causal claim is reasonable, you can run cross-correlations with different delays between one set of data and another. You delay one set of data by starting it a certain number of cells late and finishing the other set of data the same number of cells early. Square the correlation result to get the R2 value.
Be careful with cross-correlations. First, you can get in trouble fast if you’re just guessing about a correlation and don’t have some understanding of what you’re correlating (think of the high correlation between global warming and the drop