2 of 20

Basics

Columns

Rows

Crosshair

Pointer

Hand

Cells

3 of 20

Setting up your sheet

Freeze your columns and rows to lock your headings in place. This will keep your first row and column in place when scrolling through your data.

4 of 20

Setting up your sheet

Selecting and then resizing multiple columns together will keep the sizing consistent.

5 of 20

Basic calculations and formulas - Sum

Finding the Sum (total) of a value range:

Click on target cell
Find and click on the formula button on the toolbar and select SUM
Select data range
Press the enter (Return) key

6 of 20

Basic calculations and formulas

Applying formula to multiple cells:

Select cell that contains the formula
Click on the small square in the bottom right of the cell and drag over all of the desired cells

7 of 20

Basic calculations and formulas - Average

Calculating the Average:

Click on target cell
Find and click on the formula button on the toolbar and select AVERAGE
Select data range
Press the enter (Return) key

8 of 20

Splitting text

Insert an empty column
Select data
Split text to columns
Set separator

9 of 20

Number Formatting

Applying formula to multiple cells:

Select data
Change the number formatting as desired

10 of 20

Filtering Data

Enabling Filters:

Press the Filter tool
Columns will now be filterable by order, individual values

11 of 20

Filters and Filter View

It can be helpful to create and save filter views within your sheet to display specific filtered data.

12 of 20

Autofill

Autofill:

Sequences and patterns will be recognised
Select the first two numbers in the sequence
Click and drag from the square in the bottom right corner of the cell

13 of 20

Conditional Formatting

You can create conditional formatting rules to colour code values within a range of cells. There are two options for conditional formatting- Single colour and colour range. This can be useful when organising student data, responses in from Forms, or timetabling.

14 of 20

Conditional Formatting - continued

Use conditional formatting to identify duplicates in a data set.

15 of 20

Protecting cells and ranges

Locking down a particular cell or range is a useful way of protecting the integrity of the sheet, particularly when it is widely shared.

How to freeze rows and columns

16 of 20

Useful Tips and Tricks:

Use the Explore tab for quick analysis, formatting and breakdown of the data.

"Command + “forward slash” (Mac) or “Control” + “Forward Slash” (Windows) brings up the ultimate list of shortcuts that will allow you to get things done in a snap.

Add Image to cell with formula of =IMAGE(“url”)

In the parenthesis, put the link of the image from online in quotation marks

Use option/alt key to drop text a line without using text wrap

Embed a sheet in a google site and it will automatically be up to date (great for class timetable with tabbed weeks)

Use comments within sheets and direct them to particular users by using +emailaddress

17 of 20

Add ons

There are a large number of very useful add-ons available through the Add-ons tab.

Crop Sheet - cropping sheets to the data or to your selection

Save As doc - Easily exporting data within a sheet to Docs

Random Generator - Populate cells with random numbers

Remove duplicates - Identifying and removing duplicates within a set of data

rowCall - filters rows from your main Google Sheet by whatever column you select and then creates individual sheets for every unique cell in that column.

Lucidchart - Embedding flowcharts into sheets

18 of 20

Functions and Scripts in Sheets

Google spreadsheets function list

Google Translate

=GOOGLETRANSLATE(F2, "en", "fr")

Sparkline

=SPARKLINE(A1:F1)

QR Code

Pull in the data from cells to create a QR code:

=IMAGE("https://chart.googleapis.com/chart?chs=200x200&cht=qr&chl="&A1&"")

Some Fun Formulas:

19 of 20

Macros

A macro is an automated input sequence that imitates keystrokes or mouse actions.

Record your own repetitive actions

1 of 20