Instructions for Using Excel

Graphs are an important part of scientific publications. While many scientists use software such as MatLab or Mathematica to produce fully customizable graphs, the most widely available software package used by students and professionals alike is Microsoft's Excel. Unfortunately, the default settings for producing graphs in Excel are pretty poor. In this section you will learn how to prepare and format graphs so that they are ready for publication.

The instructions listed here are for Microsoft Excel for Mac 2016, which is the version used on school computers. Since 2011, Excel has been using a toolbar called the "ribbon," which looks like this:

The ribbon can be minimized by clicking the up arrow to the right of the Share command. You will also be using the main menu, which appears as follows:

Example data

Let's start by inputting a data set with normal force in column A and static friction force in column B. Before we begin typing in numbers, change the font for the entire document to Times New Roman 12 pt by typing CTRL-A or by clicking on the Select All button shown by the dashed blue line in the picture below.

While all the cells of the spreadsheet are selected, change the font from the Calibri default to Times New Roman 12 pt (make sure the Home tab is selected). Now select the A and B column by clicking on the letter A, which selects all of column A, and shift clicking the letter B to select all of column B. Using Command-1 brings up the Format Cells menu:

Select the Number tab at the top of the Format Cells window, then select "Number" under the Category group at the left. Make sure a "2" appears under Decimal Places. Enter the following data in columns A and B:

The subscript in the header is made possible by selecting the letter you wish to appear as a subscript, typing Command-1, and selecting the box next to "Subscript." You can also use the shortcuts Command-B and Command-E to generate bold and centered text.

Creating a Graph

Begin by clicking on the middle of cell A1 and dragging to the middle of cell B6 to select all the data. You can either use Insert, Chart, X Y (Scatter) from the main menu or click on the insert tab in the ribbon. You should see the following chart menu in the ribbon:

Clicking on the Scatter icon opens the Scatter menu:

In most cases we will select Scatter (top left), whereby a chart will be generated and inserted in your worksheet. You will then need to move the chart into its own sheet by clicking on Move Chart from the Chart Design tab in the ribbon, or control-click the chart and select Move Chart from the options. You will see the following dialogue box, where you select the radio button next to "New sheet" and name the sheet "Friction." All graphs should be printed as a full page and placed at the back of your lab report.

Formatting the graph

The first thing we will do is to change the font for all graph elements to Helvetica Neue 12 pt. Make sure the graph is selected by clicking in the white space near one of the corners (don't select inside the graph itself). Then click the Home tab in the ribbon, and change the default font from Calibri 10 pt. to Helvetica Neue 12 pt. The font for axis numbers and chart title should change correspondingly. Then click the font color icon shown by the red circle below, and click on the color black to change the font color from the default gray to black.

The color of the data points needs to be changed from the default blue to black. This is done by double clicking any data point, which will select all data points and cause a Format Data Series window to pop up.

First click the Fill & Line tab, then the Marker tab underneath. Under the Fill option, the color should be changed from blue to black, and under the Border option, the color should be changed from blue to black. The end result should appear similar to the picture below.

Gridlines

Get rid of the horizontal and vertical gridlines by clicking on the Chart Design tab in the ribbon, selecting the Add Chart Element, and selecting Gridline followed by Horizontal Gridline. Repeat for the vertical gridlines. Alternatively, you can select the gridlines directly and press the delete key.

Axis titles and Chart Titles

We will not use chart titles, because we plan to use figure captions underneath the graph. Select the F_s (N) chart title and press the delete key to remove it. Axis titles are required for every graph. To add a horizontal axis title, make sure the Chart Design tab is selected in the ribbon, select the Add Chart Element and choose Axis Titles, Primary Horizontal. Change the default "Axis Title" to "Normal Force (N) on the horizontal axis. Now repeat for the Primary Vertical Axis, changing "Axis Title" to "Static Friction Force (N)." Note that if your units require a superscript, you will have to manually format as a superscript using the procedure provided above. The carat ^ is not acceptable for indicating an exponent.

Trendlines

A trendline is the term Excel uses for a least-squares linear regression. We will be using the linear trendline mostly, although there are other types as well. Click on one of the data points, then make sure that the Chart Design tab is selected. Under Add Chart Element, choose Trendline, then Linear. A dashed blue line will appear through the data points. Now select the trendline by clicking on it. In the Format Trendline window to the right, click on the Fill & Line tab. Under Color, change the default blue to red (second to the left under standard colors). Then under Dash type, choose the solid line (top selection). Width should be set to two. Then choose the Trendline Option tab, which appears as three vertical bars. Select the "Display equation on chart" option. Your trendline options should appear similar to those below.

Finally, drag the trendline label (the equation) away from the data set so that it does not overlap. Under the Format Trendline Label tab, choose Number under category, and change the number of decimal places to 3, which is appropriate for this data set. Your trendline options should match those below.

Final Result

Your graph should look as follows: