Blog

Google Sheets

This will be a quick introduction to using spreadsheets. Spreadsheets are very useful tools for managing changes, or for displaying information. Graphs can be made to display data graphically, and calculations and repetitive processes can be easily achieved using a spreadsheet. 

To start, we will open Google Sheets.

Next we will choose ‘Blank’ to create a new spreadsheet.

Start out simple.

Type ‘Monday’ in cell B1, and ‘Week 1’ in Cell A2 (make sure that you have a space in ‘Week 1’)

Hover over the bottom right hand corner of cell A2 (where the dot is), left click and hold left click down, while you drag the mouse down to A7, and you should get this.

Left click on Cell B1 (with Monday in it) to select that cell.

Hover over the bottom right hand corner (where the dot is), left click and hold left click down, while you drag the mouse to the right to F1.

The weekdays have been automatically added to the cells C1 through F1. Spreadsheets have a number of pre-defined lists, like days of the week and months of the year. Advanced users  can create their own special list that recurs a lot, although most users would never need to do that.

Now put some numbers in the cells B1 >> F7, like this.

Now select all cells B2 through F7. This happens by selecting cell B2, and then (when NOT hovering over the corner, just somewhere in the centre of the cell), let click and hold left click while you drag the mouse down an right, until all cells are selected at once.

If we press the $ button above column B, then all columns will be formatted in $.

(You may need to go the word ‘File” then ‘Spreadsheet settings’ and change the ‘Locale’ to Australia)

Select cell G2 and type ‘=sum(B2:F2)’ into it. Make sure that you start with the equals sign (=) as it is the equals sign that tells the spreadsheet to calculate what is shown. Do not type the single quotes, just type what is between the quotes.

And then press the ‘enter’ (or ‘Return’) key on the keyboard.

Cell G2 now contains a value equal to the sum of the five cells to the left.

Change C2 (Tuesday, week 1) to $40 and watch what happens to the total in G2

Highlight column A (by left clicking on the ‘A’ at the top of the column.

Now press the B (bold text formatting) button in the toolbar. The entire column A is now bold font. The bold text formatting button is above the column E in the images shown here.

Select cell G2, and drag and drop (using the bottom right hand corner of that cell) down to G7.

Each cell in G3 through G7 now contains a sum of the five columns to the left.

In G9, we will enter the statement ‘=sum(G2:G7)’ to sum the six weekly totals above.

Change the value of C5 (Tuesday Week 4) to $125, and both the totals in G5 and G9 will be updated automatically.

Highlight all cells from A1 through G7. (Left click in A1 to select, left click somewhere on the inside of cell A1, then drag down and right to G7. Don’t use the bottom right hand corner).

Now select the border button (looks like the Play School square window, above the G column)

Select Cell G9, and use the border button to select top border and bottom border. (Microsoft Excel also has a single button that has single top line and double bottom line)

Add the word ‘Total’ to cell G1, use some shading and formatting so that the spreadsheet looks great and is also functional.

To add a new row, select the number 8 in the row header to highlight the row, right click and select ‘Insert 1 below ‘

Select cell A8, and type ‘Average’

Select cell b8 and type ‘=average(b2:b7)’ and press enter

Drag and drop cell B8 across to cell F8 (use bottom right hand corner)

Some more formatting

In cell E6 (Thursday, week 5), change the cell contents to ‘=48*2’ and press enter (This is saying, let cell E6 be the product of 48 and 2, or let cell E6 be the value of 48 times 2)

Notice how the Thursday average (E8), the week total (G6) and the overall total (G10), all changed automatically.

In this module we will use spreadsheets to design rosters and to calculate budgets, staff wages and contractor doctor payments. Spreadsheets can be used for many purposes when measuring, recording and graphing. This primer has merely been an introduction to spreadsheets.