Excel Basics
Microsoft Excel is a Spreadsheet program created by the Microsoft Company, originally for the Windows operating system. Excel allows users to perform many data handling and processing tasks, including :-
1. Tables and data handling
2. Calculations (simple and complex)
3. Graphs and graphical analysis
4. Macro programming
Microsoft Excel can be used to great effect within the Senior Phase of Physics, especially in relation to experimental data handling in the Assignments and project. The key areas that are relevant to the Senior Phase courses are the first three tasks above.
Note - It is important to clarify that the procedures below are all based upon working within the Microsoft Excel 2013 program, which is standard across Stirling council schools. Any other version of Excel will work in a similar way, but may not function exactly as described below. A good source of guidance with Excel is simply to search YouTube for "how to" videos, most of which are extremely helpful. Please refer to your system Admin/Computing department if you require any further assistance.
Tables in Excel
The starting point of Excel is data handling within a table format. The creation of a table within Excel is a very basic skill upon which all other work is based, so a solid understanding of the procedure is vital.
The first stage of creating a table within Excel is having data to handle. Below shows a data set (an Ohm's Law investigation) to be inputted into Excel :-
When Excel is first opened, the following spreadsheet is generated :-
In order to input the data, simply click one of the boxes (known as cells) within the spreadsheet and type. The data in each cell can be edited using the controls at the top of the screen, just like a Word document. Cells can be widened to fit larger amounts of text simply by "double clicking" the line between the cell title letters in the top bar:-
In order to create table lines, highlight the cells to be given borders then click on the border icon in the toolbar (circled in red below) and select the required borders :-
Once border lines have been added, the table is complete :-
Basic Calculations Within Excel
The creation of a table within Excel is the basis of all other work within the program. In order to perform calculations, the table above will be used as our starting point.
The first stage of performing a calculation is to first extend the table to contain any new columns required for the calculation (in this case the conversion of mA to Amperes and then calculation of Resistance) :-
To perform a calculation, select the cell required and press the "=" key. An equation can then be simply types into the cell, and on pressing "enter" the calculation will be complete. If the calculation requires content from another cell, simply click that cell at the appropriate point in the calculation (as shown below) . This is very useful as if the content in the targeted cell changes, then the calculation automatically updates to give the new required answer :-
By clicking and dragging the box at the bottom right of the cell (circled again in red), the calculation can be copied down to each row. The system will automatically change the selected cell contained within to the correct row (C3 to D3 to E3 etc.) :-
A similar calculation can be performed to give the Resistance column :-
Note - In the above image, the value for Resistance of the first line (0V, 0mA) was entered manually, Excel will return an error message for a calculation that divides by zero.
To calculate the average Resistance value, a SUM calculation can be performed. This is performed by typing "=SUM" into the required cell then "clicking and dragging" to highlight the required data. Through the use of brackets, this SUM calculation can be divided by the number of readings (in this case 6) to calculate the average value :-
The resultant value for the Resistance calculated using the above method has too many significant figures. This can be corrected by highlighting the required cells and "right clicking" on them. From the drop down menu select "Format Cells", then in the pop up window select the number tab (as shown below) and set decimal points to the required value (in this case 0) :-
Note - There are many more possible calculations that can be accomplished using Excel, more than will be covered in this tutorial. For further guidance, again see Youtube or your school's computing staff.
The final result of this processing is shown below :-
Basic Charts / Graphs Within Excel
In order to create graphs, the table above will be again used as our starting point.
The first stage in creating a graph is to first identify and highlight the data sets to be used (in this case the Voltage and Current columns). As we do not require the mA column, in order to highlight only the required data, simply "Click and Drag" down the first column, press and hold "ctrl" then select the second required column :-
Once the required data has been highlighted the type of chart or graph can be selected. To do this, click on the "Insert" tab in the toolbar, then select the required graph type to insert into the document. For almost all graph work within Physics, select the Scatter graph, as shown below :-
Next, click the "Move Chart Location" in the toolbar and select "new sheet". This opens your graph as a new page at the bottom of the spreadsheet, which will show the following window :-
The graph in this form is not complete, several aspects of editing is required, firstly the addition of a "Line of Best Fit". In order to add a line of best fit, simply "left click" on a data point to highlight the set, then "right click" and select "Add Trendline" from the pop-up menu. The following sidebar will open and can be used to edit the trendline :-
For most experiments within Physics, the "Linear" trendline will be used. As seen from the Physics course, a straight line equation can be used to calculate additional variables (in this case the gradient will give the 1/Resistance value), and the equation can be added to the graph by selecting the option at the bottom of the list. By clicking the "bucket" option, the line itself can be edited (colour, thickness etc.). Once this been completed, the graph will appear as shown below :-
Note - The colour of the trendline has been set as Grey, in order for the black data points to be more easily visible (see below). Coloured lines should not be used as all printing will generally be done in Grayscale.
The next stage of editing will be the data points themselves. In Physics, circular data points must not be used. The correct data point will be a cross, as the center of the cross shows the exact position of the data point. They should be small, but of a size that ensures they are visible through the trendline, and finally they must show data labels. In order to edit the data points, simply "left click" on a data point to highlight the set, then "right click" and select "Format Data Series" from the pop-up menu. The following sidebar will open and can be used to edit :-
By clicking the "bucket" option, the points can be edited. By clicking the section "Marker Options" the points can be changed to crosses, by using the drop-down menu circled above.
Note - If the cross option does not appear as shown above, then select the square second from bottom. This makes points appear as a square, but open the "Fill" drop down menu, and change the fill colour to transparent, and this will then show the required cross shape.
Once this been completed, the graph will appear as shown below :-
The next stage of editing is to add Major and Minor Gridlines, Axis Titles and Data Labels to the graph. This is done by clicking on the "+" symbol and selecting "Gridlines" and ticking all boxes, then ticking the boxes for Axis Titles and Data Labels :-
Once these boxes have been ticked and the axes labels corrected, the graph will appear as shown below :-
If the minor grid lines are still not fine enough, the number of minor grid lines can be edited also. To edit these, "right click" on each axis and select "format Axis" from the pop up menu. The following sidebar will open and can be used to edit :-
By changing the minor grid line unit value, the number of grid lines will be altered. The smaller the value, the more minor grid lines are shown. This may take some trial and error to find the optimum value.
Once this has been completed the graph is complete and will appear in its final form as shown below:-
Complex Calculations - LINEST Function
Microsoft Excel can also be used to perform more complex calculations than those described above. As part of the Higher and especially Advanced Higher courses, an understanding the LINEST function is valuable.
The LINEST function allows a statistical analysis of the data used to generate a graph, and generates a set of data giving the uncertainty values for the gradient of the graph and the graph's intercept. This information can then be used to clearly show whether two variables have a directly proportional relationship or not. A directly proportional relationship is shown if the origin of a straight line graph falls within the Error margin of the intercept.
To calculate this, the starting point required is the same as for a graph :-
To allow the calculation of a LINEST function, an additional 4x4 cell section needs to be used. The following shows the additional layout, as well as labels for each :-
To perform the necessary calculations to populate the LINEST Table, the following formulae must be added to the empty cells.
To add the calculation, click and drag to highlight all four cells, then type "=LINEST( Y-Value Cells,X-Value Cells,1,1)" into the selection. The two variables should be changed to show the cells giving each variable, for instance in this example the calculation will read "=LINEST(E6:E11,C6:C11,1,1)".
Once this calculation has been typed into the first cell, press Ctrl+shift+Enter, and the table should now show four values :-
Note - By pressing "Ctrl+Shift+Enter" at the same time, Excel treats this function as an array over all four cells. If only "Enter" is pressed it does not work. Also in the above screen shot, the function in the cells is shown to be contained within " { } ". This is how Excel denotes an array, but do not type these when inputting the LINEST function as again, this will prevent the function working.
The values now show in the array show that :-
Gradient of the graph = 5x10-4 ± 6.96x10-6
Y-Intercept of the graph = -2x10-6 ± 2.71x10-5
As was stated earlier, the gradient of this graph shows 1/Resistance. If the axes are reversed (Current X axis, Voltage Y axis), the gradient will then give Resistance, as shown below :-
To reverse the axes, simply change the order of the variables within the LINEST calculation (circled in red), to give the required results :-
Gradient of graph = 1996 ± 27.77 Ω
Y-intercept = 0.01 ± 0.05
As the Y-intercept is 0.01 with a range of 0.06 to -0.04, the origin falls within the error range, and as such this graph shows a directly proportional relationship and the experiment "proves" Ohm's Law.
Note - As can be seen in the above image, there are two different average values given. The arithmetic average based upon the data point values, and the LINEST average, based upon the line of best fit. In an investigation, the LINEST average should always be used.
