Click along with us: https://docs.google.com/spreadsheets/d/1TUxKijYFb3_RwfvDN-IyzAGEXCj3COByuo6wo6nnrY0/edit?usp=sharing

Google sheets is Google’s response to Excel, one of the most commonly found applications on any work or home computer. The online based alternative boasts many of the features Excel does, despite having a bit of a reputation for being a simpler and less advanced version.

A lot of work is going online, collaborative and cloud-based. Google Sheets is more than equipped for this.

Here are our favourite features of Google Sheets that you might not have known it can do:

 

Collaborate

Google sheets offers the ability to collaborate with colleagues or friends on one single document, where every change is recorded in real-time. Google sheets offers the ability to share a spreadsheet so that anyone with link can either view your document, comment on it, or edit it with you.

.
Collaborative

 

Revision History (The more advanced undo)

Revision history allows you to see all your changes, accompanied by a timestamp and the person who made the changes. Unlike the undo button where you can only revert your changes one by one, revision history allows you to completely backtrack on your changes based on a certain time.

Revision history starts tracking and saving versions of your spreadsheet from the day it’s created, so it’s possible to use a spreadsheet for months, or years, and then be able to quickly and easily view or revert back to how it was weeks after it was created.

Simply click the ‘All changed saved in drive’ link at the top of the sheet to view it.

.
Revision-History

Automatic Saving

Ever closed a spreadsheet and had the sudden realisation that you didn’t save your past 2 hours of work? Not with Google Sheets. No matter how small the change, whether you’ve added in 500 cells of data or made a cell bold, it automatically saves all changes to Google drive the second you made it.

 

‘Copy’

It isn’t always the case that you want to completely start from scratch with a spreadsheet. Maybe you want the basic structure or formatting the same but just want a new sheet for a separate data-set. With Google Sheets, you don’t need to save the spreadsheet as ‘Example Spreadsheet (1)’ and rename it, you simply need to press ‘copy’ and the entire workbook is copied to your Google Drive.

 

Conditional Formatting

Conditional formatting is quick, easy, effective and elegant in Google Sheet. At the click of a button it’s easy to format colour schemes and styles.  

You can use conditional formatting on rules such as

 

  • ‘Date is before/after/on’
  • Cell contains…
  • Text is…
  • Greater than/less than/ equal to
  • Colour Scales
  • & more

Conditional-Formatting

 

‘Download As’

Google understand that not everyone is familiar or comfortable using their online excel alternative, which is why the ‘Download as’ feature is a very useful and time saving feature. If your boss or colleague really can’t stand using sheets, and demands the work in Excel format, you don’t need to banish yourself to the Microsoft alternative, or have to copy across all your data.

Simply hit File > Download As… You’re then presented with a list of options, including:

 

  • Excel
  • Open Document
  • PDF
  • Web Page
  • CSV
  • TSV

 

Download-As

 

Notification Rules

Getting notified whenever an edit is made to a spreadsheet might seem like a useless feature, until you consider the collaborative features in Google Sheets.

It’s possible to set up notification alerts to specified email addresses whenever a change is made to a spreadsheet. You can receive this notification as and when a change happens, or as a daily digest of all changes

 

Notification-Rules

 

Publish to the web

Because Google Docs in an online based platform, publishing to the web can be a very handy feature. It allows your spreadsheet to be searchable on Google as well as allowing anyone who can find it the ability to edit, comment or view it.

This opens up many opportunities for giant collaboration projects, or just making it easier to embed a sheet, therefore making it even easier to share and run through with people.

.

Publish-Web

 

Comments & +Mentions

The comments Google Sheets is an often underused yet powerful feature. It offers the ability to add a comment on certain cells and allows others other people to reply or resolve your issues/comments. On top of this, it’s possible to +Mention an email address in a comment, which in turn notifies the person via email.

.
Comments

 

‘Hide Gridlines’

Whilst this isn’t an advanced feature, the option to be able to hide gridlines can really transform an ugly sheet into a clean and easily digestible one. If styling is your thing, hiding gridlines is for you.

.
Gridlines

 

Data validation

The trouble with collaborative spreadsheets is letting other people play around in your spreadsheet. If you’re quite protective over your spreadsheets and like to ensure that everything is correct and well formatted, data validation is for you.

This allows you to set restrictions or guidelines on certain cells. Right click on a cell selection and hit Data > Data Validation. From here, you can set criteria for what can and cannot go into the cell. You can set it to:

 

  • Only items in a list
  • Only items in a selection (If a list is too hard to type out but you have the values somewhere else on the spreadsheet
  • Numbers (between/greater than/equal to etc)
  • Text (Contains/valid url/valid email etc)
  • Date (is valid/between/before/after etc)

 

  • Custom formula

 

 

After you’ve added your settings, you can make it possible to either show the person inputting the wrong entry a warning, or block them from inputting at all. As well as this, you can fill in a help box which can show upon an incorrect input explaining what should be entered.

 

Data-Validation

 

Protect Ranges

In a similar vein to ‘Data validation’, protect ranges can be used to block off access to sections of your spreadsheet. If you only want to people to be able to input values in specific cells, but not grant them access to play around with your cells which hold complex formulas, you can simply highlight a selection, hit Data > Protect Sheets & Ranges and set your rules.

You can enter a description for the range you’re protecting, then choose between a range of cells or an entire sheet. You’re then greeted with the option to either display a warning when the range is edited, or completely block people from editing the selection at all apart from specific people.

.
Protect-Ranges

Quick and easy link search

Adding a link to a spreadsheet can be a tedious task. Having to exit the sheet, fire a search into Google, open the page, copy the link and paste it back into the sheet. Too much effort…

With Google Sheets, this is all built into the spreadsheet for quick and easy addition.

Right click > Insert Link > Type in your Anchor Text > Use the Link box to either paste a link or start typing to search the web.

.
Insert-Link

 

Quick & Easy Charts

Charts are a great way to visualise and present data in a way that’s less of an eye sore and more telling than simple cells with digits. Google sheets makes it easy to create simple and effective looking charts at the click of a button.

Highlight the area or data you want to create a chart of, tap the insert chart icon in the tool bar, and you’ll be presented with a multitude of pre-created suggestions for charts. If possible from your selection, the charts will fill in all the data, complete with X & Y Axis, title, legend and scales. 90% of the time, it’s possible to hit ‘insert’ right away and enjoy your charts. If you like to tinker – the ‘customisation’ tab is for you.

.
Charts

 

Auto Updating Date & Time

This isn’t a feature specific to Google Sheets, however it’s something that given the flexibility of Google Sheets can help to transform your use of it.

For items such as tasks sheets or deadline planners, having a regularly updating clock, date or day can be handy in tracking time until or time passed since given deadlines.

There are many different ways to format the date. The easiest two functions are:

 

 

  • =TODAY()  to display the date in dd/mm/yyyy format
  • =NOW() to display the date and time in dd/mm/yyy hh:mm:ss format

 

 

It’s also possible to customise how your date looks using the function =TEXT(NOW() , “dddd d mmmm”) Where the items between the “ “ can be switched out to customise how your date looks. View a list of all formula here!

To find out the result in column E, we used the formula =D4-TODAY().

.

Auto-Update-Time

 

Insert Drawings

As well as the standard ability to insert images into spreadsheets, it’s also possible with Google Sheets to insert custom created drawings using the built in ‘Drawings’ tool. There are many many uses for this which would make it useful, such as inserting triangle decision nodes on timeline trackers. One of the more simplistic uses for it is to make your headings pop out a little more.

.
Drawings

Script Editor

If you’re a bit of a coding wizard, or just a novice with an idea who knows where to find Youtube tutorials, you might take a fancy to the ‘Script Editor’ built into Google Sheets.

Head to Tools > Script Editor and get playing.

 

Script-Editor

 

Add-Ons

Add-Ons are bolt-on extensions from Google or third party applications that can be used to enhance your sheets experience. With everything from Google Analytics tie ins, styling, remove duplicates, automated workflows, mail merges, reminders, sudoku generators and more.

Much like the Chrome App Store, Add-ons offer a wealth of possibilities to tailor and ramp up your sheets workflow.

 

Add-ons

 

Connect with forms

Being able to connect with Google Forms is a great tie-in to Google Sheets that allows you to create complex forms that tie in to a specific spreadsheet and populate it after every form completion.

A good use of this is tasks. Creating a form for new tasks that you can send round to colleagues and managers, whilst getting all the responses automatically shot into your spreadsheet as a new row. The possibilities for this are countless, and the functionality is seamless and easy to set up.

Head to Insert > Form, create your form and watch it map to your spreadsheet.

Below is an example screenshot of a form that maps to the spreadsheet on the right to create a manageable task list in an easy to access online spreadsheet.

 

Forms-integration1

 

Sparklines

Sparklines are a more integrated version of charts, only contained within a single cell. It’s possible to create dynamic and reactive sparklines next to a set of data to better visualise the results.

Sparklines is a recent and small addition, that helps to very quickly visualise data without the need for bulky charts taking up lots of space. If getting a snapshot view of data is your thing, sparklines are for you.

Read more on: https://support.google.com/docs/answer/3093289

Sparklines

 

Go Fullscreen

If you’re quickly and easily adding data into a sheet, the standard view works well enough – however, if you’re a power spreadsheet user, devoting large amounts of time and focus into your spreadsheet work – the added focus of going fullscreen can be really beneficial.

 

Fullscreen

 

Filter Views

Everyone knows about filters – slap one onto a column or row to be able to quickly and easily segment your data based on certain parameters.

Filters views goes one step further, and allows you to save regularly selected filter queries so you’re not constantly fiddling around with the little ticks at the top of your cells.

In our spreadsheet we have a random number assigned to 5 people. 3 boys, 2 girls. If we regularly need to filter out the girls, or filter out the boys, we can save a filter view called ‘Girls’, or ‘Boys’ and quickly and easily flick between the two without having to manually select each boy’s name from the filter option every time.

This can work for Spreadsheets linked to task assignment forms too. Adding a filter view for tasks ‘To do’ is a really easy way to see only what needs doing, not what’s done or is being worked on.

.

Filter-View

 

Try it for yourself

We’ve shared the spreadsheet we used to screenshot these explanations below. 

View our experimental spreadsheet below:

https://docs.google.com/spreadsheets/d/1TUxKijYFb3_RwfvDN-IyzAGEXCj3COByuo6wo6nnrY0/edit?usp=sharing

 




    We respect your privacy - view our privacy policy

    Back