5 Top Tips for Google Sheets

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.

Bonus Tip

Use Ctrl + / on Windows or cmd + / on a Mac to show all possible keyboard shortcuts for Google Sheets, learn some of these and you’ll be saving yourself a lot of time in the long run.