The
Bluffer's Guide to Using Spreadsheets in Experimental Physics
(This was e-mailed to the IoP discussion list courtesy of Mark Cramoysan, Leeds Grammar School)
It is assumed that you are using Microsoft Excel.
Simple Graphs
The first step is to get your data into two columns in a spreadsheet which are adjacent. Select the block of data then use the Chart Wizard Icon.
99% of the time you will need an X-Y scatter graph and you will NOT want to have a smoothed line joining them up. What you want is a best fit line of some sort. If you pass through the screens to produce a basic graph you can put in titles and labels as you see fit.
To get a best fit line you will need to go to the Chart menu and select Add Trendline..
You are presented with various options. If your data is clearly a straight line then select the straight line (doh !). If you can apply your massive intellect to work out what sort of curve it should be - a power law or exponential for example will be the most common - then choose that. In either case it would be convenient to have the equation inserted directly onto the graph for later use.
Go to the options page and elect for the equation to be inserted. This is also the place to tell the line (verbally) whether you wish to force it to go through the origin or not. Ponder this carefully.
Once all this has been done you will get a best fit line superimposed over your data points. If you don't like it then double click on the line and either delete it or change it.
More Complex Graphs
Sometimes you may wish to eliminate anomalies before fitting the line or you may wish to fit only the first part of the line such as in the case of stretching copper wire where we wish to fit a straight line to the first part so that we might determine Young's Modulus.
The simplest solution is to have a third column in the spreadsheet where you put on the data you want fitted -
|
X |
y |
|
y (with errors ignored) | ||
|
0 |
0.2 |
0.2 |
|||
|
1 |
1.1 |
1.1 |
|||
|
2 |
2.0 |
2.0 |
|||
|
3 |
6.0 |
(anomalous) |
|||
|
4 |
3.9 |
3.9 |
|||
|
5 |
4.8 |
4.8 |
|||
|
6 |
5.5 |
(the points have started to curve off here) |
|||
|
7 |
6.0 |
||||
Then select all three columns and go through the chart wizard as before. When you get the chance to add a trendline you can specify which column you want to use - the last one in this case. The best fit line will only apply to these points. Again you can choose to have the equation of the line printed.
When the final graph appears you may find that Excel has chosen stupid axes. In which case double click on the axis that is bizarre and you can change the scale to your own choice of minimum and maximum values.
Conclusion
These procedures will lead you to a useable graph. It is then up to you how far you go with resizing the labels and general prettifying. Excel is astonishingly powerful - but only if we tell it what to do based on our vast experience does it become astonishingly useful.
10 Steps to making your Excel graphs look more professional
- Create your chart as a separate chart rather than having it drawn over the top of your data. This makes it much easier to see the effects of changing font sizes, etc.
- Double click on the axes and adjust the number format so that it makes sense - often scientific notation with 2 dp.
- Double click on the axes and adjust the scales so they are what you would choose rather than what Excel often bizarrely thinks you want.
- When you have a trendline with an equation double click on the equation and set it so that there ARE both automatic borders and background of white.
- Get you trendline to extrapolate along by inserting a row into your data and entering a value in the x-axis column which stretches the line. Note that you should NOT insert anything in the y-axis column.
- Double click on the plot area and set the background to none.
- Remove the legend (almost always !)
- Insert text boxes by clicking on insert/picture/autoshapes at the top of the screen. Drag a box shape and use it to insert your conclusions. If necessary change the font size of the text.
- Use a speech or thought bubble to point towards key features. Click on the text box and drag the yellow spot to move the point of the bubble.
- Format superscripts and subscripts in your axis labels. etc. by selecting the text and clicking on format at the top of the screen.
Physics Home|Resources|Links|The Department|Monitoring/ALPS|The Course|Teaching strategies|Ideas|IT Equipment|Software|Recruitment|Improving results & retention|Coursework