Beginner's guide to Excel

This brief tutorial was written using the Windows version of Excel 97. Some of the steps may be slightly different if you are using a different version.

Introduction

Start up Excel and you'll notice you have a blank grid on your screen. The spaces between the lines are called cells. This is where the data goes. To place data in a cell, simply click on the cell and start typing. You may enter data labels by typing text or you may enter numerical data. Press enter when you are done. Note that you cannot use arrow keys to correct your mistakes or you will move into the next cell. Instead, click on the formula bar to fix your errors. The formula bar is located just below the tool bar and is the blank white area next to the equal sign in the picture below. If you would like to delete the data in an entire cell, simply highlight the cell by clicking on it and press delete. Also note that data can be copied and pasted just like text.

Entering Data

Fill in the worksheet with the kinetic data shown in picture below. Note that the columns can be resized by clicking and dragging on the line between the column headers to make things fit.

Doing Math

This is what makes Excel such a powerful piece of software. Excel has the ability to carry out a series of calculations using relatively few key strokes.

We need to convert this data to the log(kX/kH) format so we can graph it and get rho. To enter a mathematical formula, type an equal sign followed by the formula. In this case our formula will read =log(kX/17) (because the unsubstituted case is kH).

However, we need to specify kX. If you have the data entered as it is above we see that we have a series of data that represent kX in column C. In order to carry this out we need to tell Excel where the data is. So in cell D2 first type =log(. Now, without pressing enter, click once in cell C2. Your screen should now look like this:

Finish typing the formula leaving you with =log(C2/17) and press enter.

Instead of repeating this process for each data point, Excel has built-in shortcuts. Click and hold the left button on cell D2 and drag it down to D10. Your screen should look something like this:

You can now select fill then down from the Edit menu or simply press Ctrl + D. The formula will fill down the column, changing the row of the selected variable as it goes. For example, the formula in cell D3 will read =log(C3/17), D4 would have C4, and so on. Although you don't need this here, placing $ in front of a term in the cell will hold it constant. For example, typing C$3 would hold 3 constant as you fill down instead of changing it. Typing $C$3 would hold this cell constant regardless of what direction you fill. Give the column a label, and your spreadsheet should now look like this:

Formatting the table

We can format this text to make it more visually appealing by centering appropriate data and headings. Click and drag to highlight the cells B1 to D10, then select the align center icon from the tool bar.

Plotting Data

Now we can plot this data. Select the sigma data by clicking and dragging. In order to get data in a non-adjacent column, press and hold Ctrl while clicking and dragging over the log(kx/kH) data. Your screen should look like this:

Select chart from the insert menu. A window like the one below will appear. Select XY (Scatter) from the Chart type window and choose the sub-type as shown in the image below then press next.

If your screen looks like the one below, you are in good shape. Press next. If not, go back and try again.

Now, enter a title for your chart, and label the X and Y axes appropriately. Unless you are plotting more than one thing on the same graph, you do not want to display the legend, so click on the legend tab and de-select Show legend. Explore the other tabs to see what kinds of formatting you can use. Press next when you are done.

You will now be prompted regarding the chart location. If you plan on including small graphs in your final report, select As object in: Sheet 1. If you want full page graphs, use As new sheet: and give the graph a name. Click finish.

An unformatted graph will now appear on your page. At this point we should try to make it a little better looking. Double click on the gray background and change it to white. Also, you may want to change the positions of the axes. To move the axes to the left and bottom edges, double click on the y-axis and click on the scale tab. Your screen will look like the image below. Copy the value in the Minimum: box into the Crosses x-axis at: box. Do the same with the x-axis.

Your graph now looks like this:

Now we can get the rho value. Click once on one of the data points and select Add Trendline from the Chart menu. Select which type of function you think will fit your data best. In this case, we have linear data. Click on the options tab and select Display equation on chart. Also, rho plots must go through zero, so select Set intercept = 0. You may not always want to do this, but it applies here. Press OK.

You now should have a graph with a line and equation in the form y=mx on your graph. The value of m is your rho value. In this case it is 3.502.

Now you can copy and paste your graph directly into a Word document. You may also want to include your data. In this case, select the data and paste it into Word. Once in Word, click on the table then select Table auto-formatting from the Table menu to get the table to look nice.