Introduction to Excel



Introduction to Excel

Microsoft Excel is useful for gathering data in a table format, calculating statistical values, evaluating functions, and graphing functions. This introduction is meant to give you an overview, or a guide, to using some of the commands Excel offers to produce presentable tables and graphs.

Creating Data Tables on Excel

When opening Excel, a spreadsheet window, such as the truncated one below, will appear. Notice that there are several buttons below the main menu bar, each with a specific command that is described when moving the mouse over the button of interest as in the example below with the mouse over the Chart Wizard.

[pic]

The spreadsheet consists of rows (1, 2, 3, …) and columns (A, B, C, …) with the current cell appearing boxed as well as appearing in the Name Box. In the picture above, A1 appears as the current cell.

Suppose you would like to create a data table from the data gather during an experiment. You may begin by entering the name of the lab you are currently working on in cell A1 and entering your name in cell A2. You can get creative by selecting a different Font, a different Font Size, or making it Bold or Italicized. An example is shown below.

[pic]

In the picture above the Font was changed to Algerian (by using the pull down tab) and the size was changed to 14. Also, you can see that the lab title is in Bold because the Bold button is highlighted. Selecting such options will only effect the current cell, or highlighted cells. (Highlighting cells is done by selecting a cell, then holding down the left mouse button while dragging the mouse over the other desired cells.) Notice that all the information in cell A1 appears in the Formula Bar. If you would like to edit anything within a cell you can do it from the Formula Bar.

Next, to describe the data you will be entering include a title for your table, and label the columns. Don’t forget to include units when necessary! (Below numbering has been added, and sequential numbering can be done quickly as described in the lab manual.) By highlighting the rows and columns of the table and selecting center (a button you can look for with the mouse), everything becomes centered inside the chosen cells.

[pic]

Unfortunately, “reaction time” in cell B8 has been truncated because of its length. There are a couple ways to fix this problem:

1) Extended the width of the column by moving the mouse directly on the border between B and C for this example. An arrow will appear that will allow you to change the width of the column by dragging the mouse while holding down the left button.

2) Select Format from the main menu, then Cells… (this may be done by right clicking, then selecting the same option). A new window will appear with several tabs. Select the Alignment tab. Here you have many options for fitting text within cells. Try different alignments and see what happens. Remember, if you do something you did not want to do, you can undo it by clicking the undo button, or from the main menu-Edit, then Undo. For example wrapping the text will do the following (the column was also widened).

[pic]

Now, to include gridlines for your table highlight the cells you want to create a border for, then click on the Borders pull down button. {Note: there are many buttons available which may be accessed by clicking the More Buttons pull down just below the Close Window button in the upper right hand corner and selecting Add or Remove Buttons (or right click on any button)}. You should see several border options appear. Again, you have the option to be creative by selecting different borders, such as the example below.

[pic]

At this point, you can enter data. You may make several tables, all in a similar manner. Once you have finished entering data in your table or tables, you can preview what will be printed by selecting the Print Preview button. This is a good way to check that all of your information will be visible. Below is an example (table 1 was copied rather than entering new data).

[pic]

Statistical Analysis and Functions on Excel

When reporting a final result in your notebook the measured value is usually recorded as (measured value x) = ± σ. To determine the average and standard deviation of some repeated measurement on excel you will need to use statistical functions. You may create buttons to make these two calculations easily accessible, or by clicking on the Paste Function button you can find all available functions on Excel. As in the picture below, the Function Name: Average has been selected from the Function Category: Statistical.

Notice that the description of the function is provided. By selecting ok, the function to calculate the average will be allocated to the box you are currently in.

[pic]

A new window will appear after selecting ok, which will allow you to select the values you would like to average. You may need to select the values by clicking the button that allows you to toggle between the function window and the spreadsheet.

[pic]

After selecting the appropriate cells you can see the command you would have to type in the Formula Bar if you wanted to enter the function manually. Once you click ok, the average appears in the designated box. Follow this same procedure for other functions including standard deviation (command: STDEV) so you may show calculated values along with your table or tables.

[pic]

Notice Excel does not place any significance on the calculated values, which means you have to give your final result (in your notebook) with the appropriate significant figures. In this example your result would read: Reaction rate = (3.00 ± 0.30) x 102 ms (± 28 ms would be okay). In general, the last sig. fig. in any stated answer should be of the same order of magnitude (same decimal place) as the uncertainty.

Graphing Functions on Excel

Graphing functions of one variable in Excel is very important because physical constants may be determined from graphical analysis. As a simple example, Ohm’s law says that the potential, V, between two points of an ohmic material is directly proportional to the current, I, passing through the material, or V ( I. The constant of proportionality is called the resistance, R, of the material, so V = IR. Without concerning ourselves about units right now, this equation says that if I can record several different voltages (the independent variable) and plot those values against the corresponding current values (dependent variable), then the slope will be the inverse of the resistance: I = (1/R)V. Below is a data table of some ohmic material.

|Table 1: Current and Voltage for |

|Ohmic Material |

|Current (A) |Voltage (V) |

|0 |0 |

|0.11 |2 |

|0.28 |4 |

|0.4 |6 |

|0.5 |8 |

|0.67 |10 |

|0.82 |12 |

|0.9 |14 |

|1 |16 |

|1.2 |18 |

|1.3 |20 |

To plot this data and determine the slope, begin by clicking on the Chart Wizard. A new window will appear with several Chart-types to choose from. Generally, we will always be plotting points, so choose XY(Scatter) with the Sub-type highlighting scattered points.

[pic]

If you have time to see what other graphs can produce, then test them out. Because you are using a chart wizard, it is meant to guide you through the necessary steps. For now, click Next. At this point the Wizard guesses at what data points you want to use, but it is not always right. To start from scratch remove the information from the Data Range by highlighting it, then deleting it. Now, you will have to click on the Series tab to enter the data you would like to plot, which is done by clicking Add. A new Series 1 appears with designations for X values and Y values. You will select your data by toggling between the wizard and the spreadsheet as described in the previous section. You can also create a name in the legend by entering a name.

[pic]

Click Next. Now you will see several tabs that allows you to alter the appearance of the chart. Enter an appropriate title, and name the x and y axes (again, don’t forget units). Click through the tabs to see what you can do.

[pic]

Notice additional vertical gridlines have been chosen. Now, click Next. The final step allows you to choose whether you want the chart on a separate sheet, or attached to the current spreadsheet. You may choose either one depending on how you would like to present your data tables and graphs in your notebook. Click Finish. Below is a picture of the chart selected as an object in the spreadsheet (in Preview mode).

[pic]

As a final topic, when clicking from the spreadsheet to the chart area, the main menu changes one of the options from Data to Chart. By clicking on the Chart option from the main menu several options appear. The first few options allow you to change some of the options provided throughout the chart wizard. More importantly, there is an Add Trendline option, which allows you to choose a line that fits the data the best. Since the example function I(V) is linear choose the linear fit, and under the Options tab select Display equation on chart and Display R-squared value on chart, then click ok.

[pic]

If the equation appears in the graph area you can move it by clicking on the text, then moving it by holding down the mouse on the border and dragging it to an open space. Also, you can format the graph area by right clicking anywhere in the graph area and selecting Format Plot Area…. By selecting None under the Area box and clicking okay, the final graph should look like the following:

[pic]

Analyzing the equation shows that the slope is 0.0652 and the y-intercept is 0.0009. This means that the resistance of the material is R = 1/0.0652 ( 15 (, and the y-intercept is about zero, which is expected.

Now, you know all of the necessary tools to create presentable tables and graphs for your lab exercises. Again, if you have time, feel free to look through different options and tabs that will affect the appearance of your text or graphs since the undo button will always help you fix any unwanted changes.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download