Dr. Mary Lou West,
244 Richardson Hall, 973-655-7266
e-mail:
westm@mail.montclair.edu
http://www.csam.montclair.edu/~west
Mathematical Sciences /
Physics ,
973-655-5132
I. Excel version 2007
1. In the top few lines of the spreadsheet please include the name of the lab, the date, the name of the course, and the names of all the lab partners in your team. Skip a line before the body of the data.
2. Enter the titles of the columns.
Think about the order in which the data were taken. Put the independent variable first, then the various measurements of the dependent variables, then their averages or other calculations. In the row below the titles put the units. Below these put the data numbers (with no units).
3. To plot a graph
If the x-axis numbers are in one column and the y-axis numbers are in the next column immediately to their right, then mouse click and hold to highlight all the data numbers in both columns.
If there are intervening columns you want to skip over, please highlight the x-axis numbers first. Then release the mouse, then hold down the Control key (Ctrl) while you highlight the y-axis numbers.
Select INSERT, Scatter/Just Dots not lines. If there is only one set of data on this graph, please click on “Series 1” and delete it. This leaves more room for the actual graph.
To label the axes of your graph select LAYOUT/Axis Titles/ For the x-axis select Primary Horizontal Axis Title/Title Below Axis. Type your title. For the y-axis select Primary Vertical Axis Title/Vertical Title/Rotated title. Type your title. To make a title for the whole graph select LAYOUT/Chart Title/Above Chart.
Most graphs for physics data need both horizontal and vertical major gridlines. You can grab the white area around the graph or the top of the graph to reposition it in the spreadsheet so that it doesn't cover up any data entries. In some labs you might want to put all the graphs together below the data so that they print on a second page. (Sometimes you might want to put all the graphs together to the right of the data, so that they print on a separate page.)
To fit an equation to your data points select LAYOUT/Trendline/More Trendline Options. Choose Linear (or Power, or …) and click Display Equation of the chart, and also click Display R-squared value on chart. When you are pleased, click Close. Grab the equation and move it to white space where it can be read more clearly.
You can change the maximum or minimum values on a graph's axis by selecting LAYOUT/Axes/Primary Vertical (Horizontal) Axis/More Axis Options. Then type into the Fixed boxes. You may also want to change where the horizontal (vertical) axis crosses.
Before printing, please check File (Microsoft Office 4-square logo)/Print/Print Preview. Then adjust your spacings as necessary so that you do not cut a graph in half or leave an orphan column. On the spreadsheet the lines of tiny dots indicate where the edges of the printed pages will be.
4. To do calculations click in the cell where you want the calculation to show up. Type the equals sign ("=") to tell the computer that a calculation is coming. Type the cell numbers (C5, or D7, or whatever), or just highlight the cells, the functions +, -, *, /, and functions such as SQRT, COS, SIN, etc. Many functions can be found by clicking on fx in the formula entry bar.
Note that the trigonometric functions expect the angle to be in radians rather than degrees, so you have to divide degrees by 57.29 to get radians. For instance, if angle theta is located in cell B9, then =COS(B9/57.29) will calculate the cosine of angle theta. (It is always a good idea to check the first calculation value with a calculator you are comfortable with.)
To repeat a calculation for a long column of numbers, click on the cell with the calculation already in it. Gently move the cursor to the lower right hand corner of that cell. When the cursor changes from a white cross to a black cross, double click on the lower right hand corner, and Excel will fill in values for the rest of the column.
If there is some variable which you want to have the same value in each calculation, then use dollar signs in its cell name. For example $B$9.
5. To sort a spreadsheet
Highlight a column of cells you wish to have sorted by rows. Choose Data, Sort A to Z, Expand the selection, Sort.
6. If you copy (Ctrl c) a column which used a formula and want to paste it elsewhere in the spreadsheet, use Edit, Paste/Paste Values instead of just Paste (Ctrl v). Otherwise the cells will take on new values because the formula will do relative addressing.
7. Remember to save your work (Ctrl s) to the proper Physics folder (in My Documents) or to a flash drive every minutes or so. At the end of the lab period you should also email it to your lab partners and to yourself as an attachment.
II. Excel version 2003
1. In the top few lines of the spreadsheet please include the name of the lab, the date, the name of the course, and the names of the lab partners. Skip a line before the body of the data.
2. Enter the titles of the columns.
Think about the order in which the data were taken. Put the independent variable first, then the various measurements of the dependent variables, then their averages or other calculations. In the row below the titles put the units. Below these put the data numbers (with no units).
3. To plot a graph
If the x-axis numbers are in one column and the y-axis numbers are in the next column immediately to their right, then mouse click and hold to highlight all the data numbers in both columns.
If there are intervening columns you want to skip over, then highlight the x-axis numbers first. Then release the mouse, then hold down the Control key while you highlight the y-axis numbers.
Click the chart wizard (colored bar chart icon on the toolbar). Choose XY Scatter, and unconnected points. Next type the title of your graph and titles for the x-axis and y-axis. Select Gridlines, and check x-axis major gridlines as well as y-axis major gridlines (usually on). Select Legend and un-check "show legend" if it is not necessary. This gives more room for the graph itself.
Finish by saving the graph as an object in your spreadsheet. When the small graph appears, please double click in the grey chart area, choose the white sample, then click OK to change the graph area to white, and thus save printer ink later.
You can change the maximum or minimum values on a graph's axis by double clicking on the axis line itself. Then choose the Scale tab, and type into the boxes or click the choices.
You can grab the top of the graph to reposition it in the spreadsheet so that it doesn't cover up any data entries. In some labs you might want to put all the graphs together below (or to the right of) the data so that they print on a second page.
Excel can fit a line or curve to your data. With the graph selected, pull down Chart, Add a Trendline. Click on Linear, or Power, or whatever. Click Options, Display equation on chart, OK. Click on the equation, move the cursor until it becomes an arrow, then grab the equation and move it to white space where it can be read more clearly.
Before printing, please check File, Print Preview. Then adjust spacings as necessary so that you do not cut a graph in half or leave an orphan column.
4. To do calculations click in the cell where you want the calculation to show up. Type "=" to tell the computer that a calculation is coming. Type the cell numbers (C5, or D7, or whatever), the functions +, -, *, /, and functions such as SQRT, COS, SIN, etc. Many functions can be found under Insert, Function.
Note that the trigonometric functions expect the angle to be in radians rather than degrees, so you have to divide degrees by 57.29 to get radians. For instance, if angle theta is located in cell B9, then =COS(B9/57.29) will calculate the cosine of angle theta. (It is always a good idea to check the first calculation value with a calculator you know how to use well.)
To repeat a calculation for a long column of numbers, click on the cell with the calculation already in it. Gently move the cursor to the lower right hand corner of that cell. When the cursor changes from a white cross to a black cross, click and drag to the bottom of the column. All the calculations will be done for you. (Or just double click on the lower right hand corner of the calculation cell.)
If there is some variable which you want to have the same value in each calculation, then use dollar signs in its cell name. For example $B$9.
5. To sort a spreadsheet
Highlight those cells you wish to have sorted by rows. Choose Data, Sort. Select which column to sort on. OK
6. If you copy a column (say) which used a formula and want to paste it elsewhere in the spreadsheet, use Edit, Paste Special (values only) instead of just Paste. Otherwise the cells will take on new values because the formula will do relative addressing.
7. Remember to save your work to the desktop or a floppy disk every 5 minutes or so. At the end of the lab period you should also email it to your lab partners and to yourself as an attachment.
This page is http://www.csam.montclair.edu/~west/exceltips.html