he data you’re interested in, create a new worksheet where you can do your calculations and generate your graphs. Much scientific data (especially climate data) is done one row per year, one column per month. This row-oriented format is a problem for Excel since it functions best in column format, especially when creating graphs. There are several ways to switch the row data into column data, but one of the easier ones is to use the Excel “transpose” option.
Transpose is at the bottom center of the box.
To transpose row data into a column, select all the data in the row you care about and copy it by hitting Control “C”. Then go to the calculation worksheet, select the starting cell, then select “Paste Special” in either the Edit tab or by right clicking. One of the options in the box that comes up is “Transpose.” Select it and click “OK.” This will paste the data as a column starting at the cell you select and filling down the column.
A more graceful way to do this is with Excel’s “VLOOKUP” function, but it takes some time to get familiar with exactly how the function works.
Once you’ve got your data into a column, you can graph it by selecting Insert: Chart.
Click on the corner squares to drag in additional data.
Click on the box edge to drag to different data.
Here’s a couple of hints for easier graphing. First, when you’re selecting a lot of data, instead of dragging your cursor over all the data, select the first cell of the data, scroll down to the bottom of the data and then Shift-click the last cell of the data. This will select all the data between the two selected cells as the data you want to graph. Second, if you want to add more than one or two series of data, start by graphing a single data series. Click on the graph and then look at the cells of data. You’ll notice that the data’s name is surrounded by a green box, the Y-axis data is highlighted by a blue box, and the X-axis labels are surrounded by a purple box. If you have multiple columns of data you want on the same chart, you can click the square 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.