Excel Summary

Entering an equation

Select the desired cell, click on it, and type an equal = sign. Then click on the cells that you need for the calculation, typing in the proper math operators (+, -, *, /, ...) in between the cell addresses.

Automatically filling a column

Click on the cell that contains the equation you wish to fill into the cells below it. Move the cursor, which becomes a white or black cross, to the bottom right-hand corner of the cell, click on that corner, and drag down the appropriate number of cells.

To calculate a table of values from the same formula, click on the cell containing the formula. Then click on the lower right-hand corner of the box with the desired formula and while holding the mouse button down, drag the cursor down as many rows as desired. When you release the mouse button, the new values appear.

Relative referencing

As the default, Excel uses relative referencing when doing calculations. When you drag down a formula Excel assumes you want to use a relative position for the data to calculate the new result. For example, consider the situation where cell A1 contains the number 1 and cell A2 contains the number 2. You write the formula = A1+1 in cell B1 and then drag the formula down into B2. The formula in B2 will be = A2+1. The results in B1 and B2 will be 2 and 3, respectively.

Absolute referencing

Suppose you have a certain value that will be used in many calculations, and you want to type it in once, and then reference it after that. To do this, instead of just clicking on the box which holds the number, type in a dollar sign before the column letter and then again in front of the row number. For example, instead of B16 you type $B$16. Then if you drag that formula down into other cells the formula will always contain $B$16.

Excel Graphing

Plotting data already entered into a spreadsheet:

After creating, entering, or importing the data you want to have plotted, drag the mouse to select the data you wish to have graphed. After selecting the data, click on the Chart Wizard button. This is a tiny picture of a bar graph, or on some versions of Excel it is a tiny picture of a magic wand waving over a graph. The program will ask several questions. When it asks for a chart type, choose X-Y (Scatter) to get a scatter graph. After you have answered a few questions (make sure you give your graph a title, axis labels and no legend), you will get a graph. Most versions of Excel have different steps but they all give the same output. To modify part of the graph, you can generally click on the part of the graph you want to modify to get a window giving you some choices.

Adding a linear line

If you want to add a regression line, click once on the graph to select it. The menu bar should now include a Chart menu. From that pull-down menu, choose Add Trendline, and then choose Linear. Next, go to Options" and click on the display equation and display R-squared value buttons. If the line you are producing does not cross the y-axis you will need to extend the line. On the forecast

box, still in Options", add some units to either the forward or backward boxes. You can return to this screen by double clicking on the regression line on the chart if you need to add or remove units.

Click on OK" and return back to your graph. You can move the equation and R-squared value where it can be easily read by dragging it with the mouse. If you highlight the whole box you can increase the font size by choosing a different font from the above bar.

Uncertainty (Error) Bars

To add error bars to the graph, click on the data points until they are highlighted. Select Selected Data Series" from the Format pull-down menu. Here you can add error bars to the graph. Choose which error bars and display are appropriate and click on the error amount for that error bar. If you are given uncertainties or you have to calculate them, use the custom error amount to add given values entered into Excel for the uncertainties.