Google Sheets Basics
Google Sheets is Google's equivalent program to Micrsoft's Excel. Sheets 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
Google Sheets 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.
Tables in Google Sheets
The starting point of Google Sheets is data handling within a table format. The creation of a table within Google Sheets 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 Google Sheets is having data to handle. Below shows a data set (an Ohm's Law investigation) to be inputted into Google Sheets :-
When Google Sheets 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 Google Doc 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 Google Sheets
The creation of a table within Google Sheets 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 'tick' in the pop-up menu, 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 :-
To calculate the average Resistance value, an AVERAGE calculation can be performed. This is performed by typing "=AVERAGE into the required cell then "clicking and dragging" to highlight the required data :-
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 then clicking on the 'decrease decimal places' button (circled in red) :-
Note - There are many more possible calculations that can be accomplished using Google Sheets, 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 Google Sheets
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 'chart' to insert a chart into the document :-
The chart will appear as a floating box within the Google Sheet. To move it to it's own tab, click the "three dots" at teh top right of the chart and select "new sheet" :-
The graph in this form is not complete, several aspects of editing are required, all of which can be done through the 'edit the chart' button :-
The first step is to convert the chart into the correct format, in this case a 'scatter graph'. To do this, click the dropdown menu 'chart type' then select 'scatter chart' :-
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). To add a trendline, click 'customise' in the 'chart editor' menu, then 'series' then tick the 'trend line' box :-
From this menu, the type, thickness and colour of the trend line can be adjusted. To display the equation of the thrend line on the graph, click the dropdown menu 'label' then select 'use equation' :-
Note - The colour of the trendline has been set as Black with 'Line opacity 50%', 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 must allow the points location to be accurately determined. In order to edit the data points, simply "left click" on a data point to highlight the set, make the required changes using the 'chart editor' menu :-
Once this been completed, the graph will appear as shown below :-
The next stage of editing is to format the Axes Titles to the graph. This is done by clicking on any of the axes and then making the required changes using the 'chart editor' menu :-
The next stage of editing is then to add Major and Minor Gridlines to the graph. This is done by clicking on any of the axes and then making the required changes using the 'chart editor' menu. Both major and minor gridlines shouls be used, with the quantity of the gridlines controlled by changing the values in the 'Major step' and 'Minor count' boxes.
This is a judgement call; you require enough lines to allow the location of the points to be read clearly, but not so many that the graph is hard to read :-
Once these boxes have been ticked and the axes labels corrected, the graph will appear as shown below :-
Complex Calculations - LINEST Function
Google Sheets can also be used to perform more complex calculations than those described above. As part of the Advanced Higher course, an understanding the LINEST function is essential for your project.
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 3x6 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 top left empty cell, as shown above.
In this cell, type "=LINEST(Y-Value Cells,X-Value Cells,1,1)" . The two variables should be changed to show the cells giving each variable, for instance in this example the calculation will read "=LINEST(D6:D8,B6:B8,1,1)".
Once this calculation has been typed into the first cell, press Enter, and the table should now populate :-
The values now shown in the array show that :-
Gradient of the graph = 5.00x10-4 ± 6.96x10-6
Y-Intercept of the graph = -2.00x10-6 ± 2.71x10-5
Note - The rest of the array has uses beyond AH Physics, but it is only the first two rows that are of interest in terms of the LINEST calulation for uncertainty purposes.
In terms of analysis, as the Y-intercept is -2x10-6 with a range of +25.1x10-6 to -29.1x10-6, the origin falls within the error range, and as such this graph shows a directly proportional relationship between Voltage and Current and therefore the experiment is a valid demonstration of Ohm's Law.
