Sheets BASICS
Google Sheets is a Spreadsheet program created by Google as part of their online workspace environment. Just like 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
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.
Note - It is important to clarify that the procedures below are all based upon the current (March 21) version of Google Sheets. As this is an online resource, it is constantly being updated and as such, changes may be made. A good source of guidance with Sheets 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 Sheets is data handling within a table format. The creation of a table within 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 Sheets is having data to handle. Below shows a data set (an Ohm's Law investigation) to be inputted into Sheets:-
When 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 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 Sheets
The creation of a table within 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 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, Sheets 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 then clicking the "decrease decimal places" button (circled in red), setting the number of decimal points to the required value (in this case 0):-
Note - There are many more possible calculations that can be accomplished using 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 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" from the dropdown menu:-
For almost all graph work within Physics, select the Scatter graph, as shown below :-
Next, click the three dots on the top right of the graph and select "move to own sheet". This opens your graph as a new page at the bottom of the spreadsheet:-
The graph will now be much easier to edit on its own tab, as shown below:-
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 click the "edit the chart" button, as shown below:-
Clicking the "edit the chart" button opens the menu shown below. To add a "line of best fit", click "customise", then "series", then tick the "Trend line" box.
The graph will now show a linear line of best fit, as shown below:-
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 "label" option at the bottom of the list.
Note - If another trend line is required (such as an exponential curve) this can be selected by changing the "Trendline type".
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 centre 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, change the "point shape" option to "X mark":-
Once the point size, shape and colour have been edited, 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 again done by using the appropriate sections of the "customise" menu on the right:-
By changing the major and minor count 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:-
