Working with Excel

 

This appendix will cover ways that Excel can be used to display and analyze data. Since this is a major component of the lab, the student is encouraged to take notes and document in his/her own words how a method or tool can be used. Example spreadsheets can also be stored on the network for future reference. This is a brief guide. Excel has many features and a host of methods to accomplish the same result. If you know a method that differs from the one contained in this guide then you may use it and share it with your colleagues. As with most applications the student should explore beyond the specific lab instructions until the method is well understood.  Your instructor should be able to help clarify.  Once you understand the concept or method add details and summarize in your own words for future reference.

 

WHAT IS A SPREADSHEET

Spreadsheets store data in tables. Excel refers to each table as a WORKSHEET. One can change to a different worksheet using the tabs along the bottom (sheet1, sheet2). A specific location in a table (worksheet) is called a CELL. A letter and a number, for example A3, identify a cell. “A” identifies the column. “3” identifies the row.  To choose a cell, move the cursor to the cell location and click the mouse.  The contents of the cell are shown in the cell and in more detail in a space at the top  (part of a toolbar). The cell may be empty. The cell ID (e.g. A3) also appears at top in the tool bar. Cells contain data of all types: numbers, dates, labels, formulas, and functions. The data in a cell can be displayed in many formats: date formats, percent, dollars, integer, and others.

 

ENTERING DATA

Choose a cell. You may then enter numbers or text from the keyboard. You can edit the cell’s data either in the cell or in the location provided as part of the toolbars. Use your mouse to choose the cell and the data entry or edit point for the cell chosen.

 

MOUSE

The mouse is a powerful tool in the Excel environment. The normal left-click is used to choose cells and locations (data entry windows). The left-click normally is also used to hit buttons (cancel, ok).  Holding the left button down allows you to select a range of cells. The right button often reveals advanced features in a pull-down menu format. This is very useful when working with plots and graphs. Often the original plot needs updates and the mouse can be used to select a region of a plot (e.g. title or data) and then pull-up options for that region (format title, change the data source).

 

SAVING DATA

If the work will be important for future experiments you should save the file to the network or a floppy disk. If you want to safeguard current work you can save the file on the local computer (Data erased every Sunday). Your instructor will help students create a temporary area to save files and show you how to save the spreadsheet in this area using the "Save As" menu item under the "File" menu. Once you have saved the work in a file with a unique name you can periodically use the "Save" item in the "File" menu to update your work. It is good practice to periodically store your work. This enables you to recover from mistakes made while using the spreadsheet and computer problems.

 

NAMING CELLS

Names will simplify the use of Excel equations. Choose "Name" under the "Insert" menu. Choose "Define..." in the list. A dialog box appears. Enter "chosen name" at the top of the dialog box. (Note: If there is a name in an adjacent cell Excel will use this name by default.) The cell that is being named appears at the bottom of the dialog box. The cell name will include the worksheet name and have $ characters added. If this is the correct cell simply click the OK-button. (Note: Excel defaults to the current cell location.)  If you want to chose a different cell then click the button in the bottom right-hand corner (RED arrow button) of the dialog box. This lets you select which cell or range of cells will be named.  You will need to complete the cell selection with the ENTER key. The dialog box will disappear while you are choosing the cells and reappear when ENTER is hit.

 

Test the process. Name a cell. Choose a new cell. Choose "Name" under the "Insert" menu. Now choose "Paste". A dialog box appears with the names of all the named cells. Choose one of your named cells and then hit OK. The new cell now has a formula that refers to the named cell. Hit enter. The cell contents should now be the same as the named cell. Change the value in the named cell and the new value appears in both cells.

 

ENTERING FORMULAS

A cell’s contents are interpreted as a formula if the first character is an equal sign. A formula can refer to another cell by call name or by naming the cell as described above.

 

=B3     set the cell’s contents to whatever is in cell B3

=vo     set the cell’s content to the cell named vo.  (If no cell has been named vo the error message  #NAME? appears.)

 

Common math operations can be used in formulas

*          multiplication

-           subtraction

^          raise to the power

/           divide

()         sets order of operations

 

=36*B3+B4+7            multiply the contents of cell B3 by 36 and add the contents of B4 and the value 7.

 

There are a many special functions that can be used within a formula. Choose a cell and “insert  (on toolbar)  “function” (on this menu). Choose a familiar function from the dialog box. When you have chosen your function hit OK. A new dialog box appears. This will aid in getting the arguments needed. The dialog box is similar to the one used in naming cells. The RED arrow button returns you to the spreadsheet so you can choose cells. The ENTER key completes the selection.  If you chose the function =sum(   ) then you need to supply a list of cells to sum. If you chose =sin( ) then you need to supply an angle. The arguments of functions can be other cells.

 

=sin(B3)          takes the sine of the value in cell B3.

=sum(B4:B8)  sums the range of cells from B4->B8.

 

Functions can be typed directly from the keyboard and the arguments for formulas and functions can be supplied by choosing cells with the mouse.

 

PLOTTING

Graphs or plots are powerful ways to visualize and analyze data. These tools will be used frequently in the lab.

 

To plot data decide which columns should be plotted.

1.     Choose "Insert" in the menu and then "Chart".

2.     Choose "XY (Scatter)" on the first dialog window. Click "Next >".

3.     Choose the data to plot by switching from "Data Range" to "Series" using the folder tab near the top of the dialog window.

a.      Click "Add" to get your first data series.

b.     Click the button at the right of the "X Values:" entry window. The dialog box disappears and you highlight the cells in the time column. Hit Enter after the box shows that all the desired times are selected.

4.     Now use the "Y Values:" entry window and choose your position data.

5.     Click finish.

 

There are a number of refinements available for improving the graph. Labels, colors, or additional data can be added or changed. Experiment by clicking with either the left or right mouse button on various portions of the graph and seeing what you can change. You will need to explore the various options to become proficient at plotting data.

 

A sample plot is shown below. The excel datasheet that generated this plot can be found in the desktop folder

 

==    Intro Physics Lab/excel worksheets/ 2CurvesOn1Chart.xls

or download excel spreadsheet

 

The worksheet describes some methods for creating charts (plots). Students may open up this folder and experiment with plotting.

 

 

 

TRENDLINE

To add a trendline you click on the graph on a data point. Right click to bring up a menu. (Choosing different sections of the graph will cause different menus to appear.) Choose "add trendline". Put the equation on the graph by setting the appropriate option on the trendline options page.

 

ERROR BARS

 

There are several methods for including error bars on the plot.

  1. Uncertainty known for each point
    1. Create a column that will contain the error bar values for X+, X-, Y+, Y-.

                                                    i.     You may decide that the X value uncertainty is not important and therefore not create columns for X+ and X-

                                                  ii.     You may decide that the uncertainty for the Y data is symmetric so only one column for Y error bars is necessary.  In this case the Y+=Y-.

    1. Right-Click on a data point and choose the format data series option.

                                                    i.     Choose which error bars you want to include by choosing the appropriate tab. {e.g. y error bars}

                                                  ii.     Choose the custom option on this window and then identify the columns created above either by directly entering in the excel data range or by using the data identification tool that is available by clicking on the icon next to the window.

                                                iii.     Choose the type of error bar desired using the pictures along the top of the window. {i.e.  +/- , - only, + only}

 

  1. A fixed uncertainty can be assigned either as an absolute value or as a percent value. No data columns are required.
    1. Choose the same window as outlined above in steps b.

                                                    i.     enter the fixed value into the appropriate window.

 

Plotting Data and Fits

 

The methods discussed above should allow you to create a plot with both data and a function.  The “chart type” option, that can be reached via the right-click on the line or a data point, can be used to display individual points or a line.  To display both data and fit it is better to keep the data display mode as “points only” and the function display mode as “line with no points”.

  1. Start a plot
  2. Include your data with error bars
  3. Adjust the plot parametersè titles, axis etc.

Now we need to add the fit. To do this we need to generate columns of data for the fit. Usually this is simply X-columns, Y-column.

  1. Generate data that follows the fit function.
    1. choose a range for the xdata that matches the data already plotted.
    2. enter the first value in the column
    3. enter the next value using a function that increments the value above
    4. drag this formula down the column until you reach the final value you want plotted on the x-axis.
    5. Now fill in the Y-column by using a formula that reflects the formula in the fitting routine.

                                                    i.     One can directly insert the parameters into the formula

= 14.7*A6+22.

 

OR

 

                                                  ii.     Use named cells

= slopePARM*A6+interceptPARM

 

    1. Once the data is calculated simply include it on the plot in the standard way.