Creating charts and trendlines in Excel 2010
Charts
The way Excel 2010 handles charts is quite different to Excel 2003. There are 11 chart types in total — Column, Line, Pie, Bar, Area, Scatter (XY Chart), Stock, Surface, Doughnut, Bubble and Radar. However, the Scatter (XY) chart is by far the most, if not the only type, useful to chemists.
To create a chart, select the data to be plotted. These will usually be located in two columns with or without a title line. Next, click on the Insert tab and select one of the options for a Scatter plot. One thing you need to learn over the next year(s) is to make a sensible judgement whether to plot just data points, a smooth line, or data points linked by lines.
This action should create a new chart:
When you create a new chart or click onto an existing chart, Chart Tools will be highlighted.
The Design tab lets you change the charge type, select a new data range, or move the chart onto a sheet of its own. You can also alter the layout using one of a few standard formats. Unfortunately, Excel is optimised for business presentations, and scientists tend to have a different view what a chart should look like.
Soon enough you will learn that your Chemistry lecturers are no different and that they will insist on axis titles. The Layout tab allows you to change or add axis titles, but also a chart title, extra text boxes, a trendline and error bars.
So, select the Layout tab and “Primary Horizontal Axis Title” and “Title Below Axis”.
Similarly, you need “Primary Vertical Axis Title” and “Rotated title”. When you do so, a textbox will be created on the chart next to your scale where you can type in the name of your axis. You can also click on an axis title to select it and change the name (again) at a later stage.
Most Chemistry lecturers, textbook authors and science publishers are not so keen on horizontal gridlines. Simply select, then delete them by pressing the Del key, or go to Gridlines and switch them off. The result will be as follows:
As regards a chart title, the convention is that you either have a chart title (on top of the chart) or a figure legend (at the bottom), but not both. In a report in Word, figure legends are more common and figures are numbered consecutively (e.g. “Figure 1. Absorbance plot for …”). Have a look at your textbook to see how the authors of the textbook explained their charts and figures.
Equally important when creating graphs is to think about sensible axis labels. To change these, click on an axis. The Format Axis menu should show up. Axis maximum, minimum, major and minor unit are normally selected automatically. To change this, click Fixed, then enter your own preference.
The Number option allows you to choose scientific notation (e.g. 1E+6 in preference to 1000000), or to specify the number of decimal places. Think carefully how many significant figures are sensible for your axis labels.
The Formattab is probably the least useful, except for the Size option which allows you to specify the precise dimensions (height and width) of your chart.
By now, your chart should look more like this:
If you want to plot more than one graph inside a chart, simply select the y values for your second plot then click on the graph and paste (press “Ctrl” and, while keeping the key down, also “V”). Excel normally assumes that the x values will be the same as for your first graph. If this is not the case, the easiest way to correct this is to click on the data points in the chart. This will display the SERIES function in the formula bar
.
Inside the brackets are several parameters. Between the “(“ bracket and the first comma will be the location of the title if any.
Between the first and second comma are the x values. So, if they need changing, select what’s there (in the above example this would be “Sheet1!$A$2:$A$6”) and select the new x values from the worksheet (“Sheet1!$M$2:$M$8”), then press the Return or Enter key.
Adding trendlines
To add a trendline, click on the data points in your chart.
The Layout tab now has a Trendline option. Select the appropriate trendline; usually this will be a linear trendline. A linear trendline will be added to your chart.
By using a right-mouse click on the trendline on the chart, you find the Format Trendline option.
Format Trendline not only allows you to change the colour or thickness of the trendline, you can also extend the line forwards or backwards, force it through a particular point on the y axis (e.g. through the origin by setting the intercept to 0). Whether or not this is scientifically accurate, requires some chemical intuition and knowledge. Another useful box to tick occasionally can be “Display Equation on chart” and/or “Display R-squared value on chart”.
wYour finished graph with trendline might then look like this:
1