Plotting with Microsoft Excel

This handout assumes that you have had some background in using Microsoft Excel. If you haven’t had any experience in Excel, you will need to check with another student or someone else who has.

Introduction

Excel makes plotting (charting) fairly easy. You simply enter your data on the spreadsheet and select it, then click the charting icon (shown to the right) on the standard toolbar, or choose Chart… in the Insert menu.

(If the standard toolbar isn’t visible, choose Toolbars… in the View menu and click the toolbars that you want to see. The Standard and Formatting toolbars are usually be visible in Excel. The Drawing toolbar is also handy.)

After entering your data, select the columns that you want to plot. You can optionally label the columns (for example, X and Y, as pictured to left) and also select those labels so that Excel uses them to label the axes Then click the charting icon on the toolbar, or select Chart in the Insert menu (shown at left). The Chart wizard will open.

For most scientific plotting, use the XY (Scatter) chart type. You can select different types of that chart: points that are not connected, points that are connected with smoothed lines, and points that are connected with straight lines. When ready, click Next. The Chart Wizard will lead you through some choices that you can make about your graph. The last page asks you where you want to put your plot. The default choice is as an object on your worksheet. This will let you put your chart on the worksheet and display it beside your data.

Sample chart. You can drag the chart around on the spreadsheet with the mouse. If you change the data on your spreadsheet, the chart will be updated. You can resize the chart by dragging on the corners. You can also move the elements of the plot around.

Editing your plot

You can edit your plot after you’ve created it. Make sure the plot is selected, then choose Chart Options… in the Chart menu to edit the axis titles, to turn the legend on or off, etc. Or double-click parts of the graph to edit those parts. For example, if you don’t like the gray fill in the chart, double-click that part of the chart to bring up the Format Plot Area dialog box. Set the area to None. You can also change the colors and types of points, the thickness (weight) of the lines, etc.

Linear Regression

Excel can do simple linear regression on your data. Use the Statistical Functions Slope and Intercept to find the values for m (slope) and b (intercept) in the function y = mx + b.

Function example: Slope. First put your cursor in the cell where you want your function to be inserted. To insert a function, choose Function in the Insert menu. Then select the statistical function. Slope. Excel will tell you what data is needed to compute the function. For slope, you need the Y values and the X values from your data. You can drag the box so that you can view your data. Then place your cursor in the Known_y’s box and select your Y values. Do the same with the Known_x’s. Click OK when you’ve selected all the values for your function.

	Excel can plot a regression line on your graph automatically. Select the chart, then choose Add Trendline… in the Chart menu. The box to left will pop up. For simple linear regression, choose the first category. Then click the Options tab to choose from the available options for the trendline. One option you might want to choose is Display Equation on Chart.
	The plot to left shows the trendline added and the equation displayed on the plot. Note that the values in the equation match the values computed for slope and intercept using the function. If you want to edit the trendline, you can doubleclick it to change its weight, color,etc. You can drag the equation to a different place on the plot too. To delete the trendline, select it and hit DEL.

Transforming your data

Sometimes you may need to transform your data to achieve a straight line when you plot it. The best way to do this is to add another column of data containing the transformed data. Examples of transforms are 1/X, 1/Y, or LOG(X) or LOG(Y). There are functions that will find the log or ln of your values for you.

Selecting columns for plotting Once you have several columns of data, you may want to plot columns that are not next to each other on the spreadsheet. Hold down the CTRL key while selecting each column that you want to use in your plot. Remember that you can only have one X column, but you can have multiple Y columns if you want.

Logarithmic axes

To use logarithmic axes for your plot, doubleclick the axis that you want to change to logarithmic to bring up the Format Axis box. (Make sure that you click in the right place so that you see this particular box, not the Format Gridlines box or some other dialog box. You may need to experiment with this.) Click the Scale tab. At the bottom you can place a checkmark to change your axis to logarithmic. This is the only way to make logarithmic axes; there is not a choice to use this kind of axis when you are first creating your chart.

The Y axis has been changed to a logarithmic scale in this plot of the data.

In this plot, both axes have been changed to a logarithmic scale. The data points fall on a straight line

This plot is created using transformed data. The results are the same as changing the axes to a logarithmic scale as above. A trendline can now be added; the equation for the trendline is y=2x.