Google Workspace: Sheets

Sheets is Google’s answer to Microsoft Excel, in your web browser or app. It is an online, collaborative spreadsheet program that is part of Google Drive and Google Workspace. As long as you are connected to the internet, all your changes are saved instantly.

You can access Sheets by going to sheets.google.com or you will see your Sheets files in your Drive.

The Toolbar

You can access Sheets by going to sheets.google.com or you will see your Sheets files in your Drive.

We’ll go through some of the toolbar menus now, others you can discover on your own.

Files

Make a copy… – Make a duplicate of the document you are in.

Download as – Download your document in other formats, such as Excel or PDF.

Version History – See all the changes you and others have made to the document or revert it to earlier documents.

Edit

Delete row or Delete column – Delete the row or column that the selected cell is in.

Delete cells and shift left or Delete cells and shift up – Delete the data from the selected cell and move all other data in the row or column left or up into its place.

Insert

Row above or Row below – Insert an empty row above or below the selected cell.

Column left or Column right – Insert an empty column to the left or right of the selected cell.

Cells and shift right or Cells and shift down – Move the selected cell right or down and place an empty cell in its place.

Chart – Create a graph based on the data in your spreadsheet.

Drawing – Create pictures, flowcharts, diagrams and more.

Tick box – Add a small tick box to a cell, click it to tick it then click it to untick it.

Note – Add a small note to a cell, only displays if you hover your mouse over the cell.

Formatting Toolbar

Sheets has a lot of the same features as Excel, it just doesn’t have every single formula or functions as Excel but it does allow for much greater collaboration..

The image below shows the main editing features of Sheets, the Format data option lets you quickly change the format of a cell for example from plain text in to currency as well as move the decimal place. The other settings allow you to add borders, merge cells, change the text formatting and how the cell reacts to text. There are also quick buttons for adding in links, comments, charts and filters.

If you need extra settings that aren’t here, click on the Format button along the main toolbar for other settings such as Strike-through or to add Conditional Formatting into your document.

Split text into columns

You can easily split some data from a single cell into separate columns. A good example of this is a list of student names where their first and last name is in the same cell, you can split the first and last name into different columns with a couple of clicks. This then lets you filter the data by first name or by last name.

Explore

The Explore button is available in all G Suite programs but in Sheets is where it’s probably best utilized. It gives you suggestions for charts and graphs based on the information that you have on the sheet.

It can look through the data in a whole sheet or you can highlight a specific area of data and it will give you suggestions for charts, tables and information.

Specialist Functions

Google have added in some formulas that you can’t do in Excel, there are 4 specialist formulas that can come in handy for you, maybe not always for teaching but maybe for reporting.

The GOOGLEFINANCE formula will provide you with stocks and shares information about your desired company. There is a host of different options you can ask it for and it updates every 20 minutes. Look at all the possibilities on the Google Help site.

GOOGLETRANSLATE is exactly what you think it is. Easily translate text from one language to another right in Google Sheets, all you need to know is the standardized 2 digit country code, you can see a current list here, but be aware that not all languages are supported in Sheets. You can also use “auto” instead of a country code for it to automatically work out what language you have entered.

IMAGE allows you to insert an image into a cell, it will be sized to the width and height of the cell so you will need to fiddle about with the sizings a little. You just need an image link from Google and to paste it into the formula then it will display. You can also use the IMAGE function to create your own QR codes right in Google Sheets.

To add a QR code, copy the below into a cell but replace the text between the two & signs with the text you want the code to represent or the cell number for the data you want.

=IMAGE(“https://chart.googleapis.com/chart?chs=200×200&cht=qr&chl=”&B6&””)

SPARKLINE is something that Excel does have but you can’t use it as a function, you have to crawl the menu looking for it. You can use SPARKLINE to easily display a small visual graph of some data.

Lock Cells

You can easily share a sheets document with people and set certain permissions on the whole document using the SHARE button but you can also lock individual cells or ranges. You can give someone edit access to add information to a sheet but stop them editing certain cells you’ve got key data in.

Select the cells you want to lock, right click and click Protect Range then a new panel will open up on the right of the page allowing you to set a name for it and fine-tuning the range of cells. Click Set Permissions and then you can set the restrictions on who you want to edit it.

Data Validation

Data Validation isn’t anything new but it isn’t something widely used however it links well with locking cells. You can lock the cells you don’t want people editing and then set Data Validation on the cells you do want people to edit so they have to input the exact data you are looking for. You can use this to stop students messing around with the responses or just to maybe provide a set number of choices, ensure students put 35% instead of just 35. Overall it helps to make sure that the data you are given is in the exact format you want it in.

Select an individual cell or a range of cells, click Data and then Data Validation to bring up the menu and set your validation terms.

Page updated

Report abuse