Using Excel to Analyze Data

Background on Data Analaysis
During the course of the semester, you will need to write lab reports. In these reports, you will be expected to compile your data use it to present your results in a meaningful way. This is an important part of all scientific research.

Using Excel will help you a great deal in organizing your data and in presenting it in a way that makes it easy to understand, and in distilling a cogent, accurate summary of your studies. You can then use these in formulating conclusions to explain the results of your work to others.

To begin, open Excel. If it is not in the QuickLaunch toolbar, open the Hard Drive, and look for it in the Applications folder. Look at the top of the screen. If there is no toolbar, go to View->Toolbars, and select Standard. Once you have entered some data into your spreadsheet, you can begin to extract meaningful information from it. Too much data will overwhelm a reader interested in your results. The goal of data analysis is to present your findings in a way that doesnít leave out important information, but doesnít include so much as to muddle your report.

The most important (and probably most common) function is the average or arithmetic mean. Suppose you run 5 trials of an experiment in order to insure the accuracy of your measurements. You now have 5 individual numbers, but this can be unwieldy. To represent the general trend of these numbers, you may want to use the average. This way, you only have one number to report, but it takes all 5 of your other numbers and represents them (hopefully) accurately.

But the mean is not necessarily always an accurate representation. Suppose that four of your trials have results in the range of 5-6 units. The fifthís result is in the .001 range. Averaging these numbers would be somewhat misleading. There are several ways around this problem, but we will only discuss the two most important ones. The first way around this would be to use the median. The median of a data set is the number that is in the middle when the numbers are arranged in increasing order. This has the benefit of not averaging in outliers, but its drawbacks should also be apparent.

The second method is to use standard deviation. This function allows you to see how close your data are to the mean value. For a given data set, a low standard deviation is desirable. This indicates that a data set is very close to the mean value, and that there arenít any significant outliers. A high standard deviation means that a data set has a high range of variability. When talking about running trials, where repeatability is desired, a low standard deviation value is what we would like to see.

Excel has many functions to help you in finding averages, standard deviations, and can also be very helpful in making charts and graphs, which, when included in your report, make data easier to visualize and understand.

Functions in Excel
Suppose you have a data set entered in your spreadsheet already. If you want to find the average, click in the box where you want the average to appear. Make sure that the toolbar is showing, and click the f(x) button on it. This opens up a window with several options. Immediately, you are given the option to use the most recently accesssed functions. On a typical computer, average and standard deviation will be on this list, but if they are not, click on All in the left drop down list and look through the alphabetical list of the many functions Excel has to offer.

Once you have found the function you want to use (in this case, Average), click Ok. You are given the chance to enter which boxes you want the function to apply to (it will appear as F1:F5 or something like that). If you want to average a given set of trials, just make sure that those boxes are selected, and click Ok. If the right boxes are not the ones selected, move the window, and drag select the boxes you do want the function to apply to, and click Ok. The spreadsheet will update automatically. This is the process you will use for any function in Excel, from Median to Average to StDDev.

There are some convenient ways to save yourself time in using functions. If you have already declared a function for one of your trials and you have five more, and you want to use the same function (say, Average) on the other trials, you donít have to input the function for every single square again. Just click on the bottom right corner of the box that has the function in it, and drag it over to whichever other cells you want the function to apply to. Excel will handle the rest.

Graphing in Excel
Once you have a data set, you may want to graph it. When you graph data, it is important to recognize the distinction between dependent and independent variables. A dependent variable is usually the one being measured. An independent variable is one that occurs at regular intervals, such as time. Dependent variables are placed on the y--axis, while indpendent variables are placed on the x-axis. If you were to measure the temperature of a cup of coffee over time, then time would be your indpendent variable, and temperature would be your depdendent variable. The temperature of the coffee will change; time moves forward incrementally, independent of what the coffee is doing. The graph of the function would be described as a ìTemperature vs. Timeî graph (note that the dependent y-axis is first, and the indpendent x-axis comes second).

Excelís graphing wizard allows you to easily graph your dependent variable against an independent variable. If you have your x-axis variables on the left, and the y-axis variables on the right, you can graph them easily without rearranging. It is therefore recommended that you think beforehand what your dependent and independent variables are before you begin inputting data into Excel. It will save you a great deal of time and unnecessary hassle.

Once you have your variables lined up, just drag select from the top left corner of your x-axis variables o the bottom right of the y-axis variables. Click on the bar grap icon on the toolbar. A new window will pop up. Select the XY Scatter, and clic Next. A preview of your graph will pop up. Title your chart and axes appropriately, click Next, and then Finish. A graph will pop up in your document.

Showing Trends with Graphs
A graph is a useful visual aid, but you may want to indicate trends on it more clearly. To do this, you may want to add a Trendline, or a best-fit line, a mathematically computed lin which best approximates the given data points. Go to the Chart menu and Add Trendline. Double click on the Trendline to bring up the Format Trendline options. Under the Options Tab, check the options to display the equation on the chart and to show the R squared value. Then drag these to a location so that they donít interfere with the graph itself.

This is a useful step because it gives you a slope for your graph, and that is a very useful piece of information. As all calculus students know, slopes indicate change and rate of change. In many calculations, this can be useful. The R squared value is a way of telling how accurately the trendline approximates the data, or how strong a correlation there is. An R squared value of 1 is perfect correlation.

This should teach you the basics of working in Excel.

This page was last updated 1/21/08

Return to syllabus

Return to Stark home page