Excel Commands for 220/230 Lab

Basic spreadsheet commands:

+A4 puts the contents of the cell A4 into the current cell
+A4/A5 can do math with cell contents
=SUM(A4:A8) puts the sum of A4 through A8 into the current cell
=AVERAGE(B2:B9) puts the average of B2 through B9 in the current cell
=STDEV(B2:B9) … and the standard deviation
=SQRT(2) or =SQRT(C4) will square-root a number or another cell
=COS(radians) give your angle in radians, and it returns the cosine

Any text in a cell makes the entire cell contents text, i.e., "3 cm" is not a recognized number.

To find the 90% confidence level for multiple measures of a single quantity:

type: =2*stdev(range)/sqrt(size),
where range is the range of cells which contain the multiple measurements, e.g., c1:c10,
and size is the number of measurements, e.g., 10.

Note: do not use Excel’s confidence function; their algorithm does not account for a small number of multiple measurements.

 

To get

m

b

sem

seb

where y = mx + b is the best fit straight line for the set of data given by xrange and yrange, and sem, seb are the respective standard errors:

Highlight 2x2 cells,
type: =linest(yrange,xrange,i,1) <Ctrl-Shift-Enter>,
where i = 0 to force b = 0, i = 1 otherwise,
xrange is the range of cells which contains the x-values, e.g. b3:b7.

Note: remember that the 90% confidence level is given by two times the standard error.

 

To get the data of the straight line fit (to be graphed):

Highlight a column parallel to x-values (same number of cells),
type: =trend(yrange,xrange,,i) <Ctrl-Shift-Enter>,
where i = 0 to force b = 0, i = 1 otherwise.

 

To graph data:

Note: if you have titles at the top of each column, highlight that, too, while highlighting column data.

Highlight your column of x-values,
holding down <Ctrl>, highlight columns of y-values,
from the menu, choose Insert, Chart, and choose whether you want to put it on your spreadsheet, or on a new page.

The Chart Wizard will run:
Step 1: choose Next,
Step 2:
choose XY (Scatter), Next,
Step 3:
choose option 1 or 3, Next,
Step 4: choose Next,
Step 5: if you highlighted column titles, leave legend as yes, type in graph, x-axis and y-axis titles, choose Finish.

 

To add another set of data to your graph:

Highlight the column of x and y-values and copy it,
Select the graph object,
Select Edit and Paste Special.

 

To edit graph: if a regular black line surrounds it, double click on it so that a heavy black line surrounds it:

Data Graphed Incorrectly: If the x-values were not the furthest left column, you will have to change the way the data was plotted. Double click on a data point, choose Names and Values, click in the Y Values box, and highlight the correct y-values on the spreadsheet. Do the same for the x-values under the X Values tab. 

Editing Legend: To add or change a legend, double click on a data point, choose the Names and Values tab and add or change the name (this can refer to a cell or be text). 

Change Graph Type: To change graph type, click on it with right mouse button, choose chart type, and choose a new type.

Lines Connecting Points: To add or delete a line connecting points, double click on a data point, under the Patterns tab, choose Automatic or None under the Line option.

Editing Labels: To edit a title or axis label, single click the text on the plot twice (not a fast double-click). Edit the text, clicking the mouse away from the text when through.

Insert Labels or Gridlines: To insert a graph title, axes titles, or gridlines, click on the graph with the right mouse button and choose appropriate option. Check (click) appropriate check-boxes, hit okay, and then edit text if necessary (see Editing Labels).

Hints for Printing: If the column widths are the standard (i.e., you haven’t changed them; Excel’s standard is 8.43 wide), then only everything through column I will print on a page, if you go past that to the right, then it will get cut off and print on a second page. Shrink graphs which are not on a sheet of their own so that they fit on a single page.

Switching Between Charts and Spreadsheets on Different Sheets: You can go back and forth between graphs (plotted on their own sheet) and spreadsheets, or just between different spreadsheets, by clicking on the tabs at the bottom of the page (e.g. Sheet1 or Chart1). So, e.g. you want to change the data plotted on a sheet on its own, Chart1, double click the data point, then click on the spreadsheet tab, Sheet1, and choose the correct spreadsheet column.