IT Pro is supported by its audience. When you purchase through links on our site, we may earn an affiliate commission. Learn more

Google Sheets top tips and tricks

Here's how to get the best out of Google's cloud-based Excel killer

Google sheets

Google sheets is a free and cloud-based alternative to Microsoft's Excel package, forming part of Google's suite of Microsoft-compatible document creators, Google Drive.

Excel users often appreciate Microsoft's great number of shortcuts, utilities and formulas, which even included a Doom-style FPS at one point and although this might not be known to everyone using Google's alternative, the app also has similar tools built within it, which make keeping tabs on expenses, working out figures and keeping your life orderly a breeze.

Below is a list of tips and tricks that can help you get the most out of Google Sheets.

Importing Excel spreadsheets

The first thing those switching from Excel to Sheets might want to do, is import old spreadsheets from one system to the other. 

Google makes importing .CSV, .XLS or .XLSX spreadsheets onto sheets easy. You can do that my navigating to the 'file' menu in Sheets, and selecting 'import'. 

If the spreadsheets you are trying to import are stored on your Google Drive, you can import them directly. If not, click on the 'upload' tab to select them from your local storage. 

You can then choose whether to convert them into separate Sheets files, add them as new sheets in an existing file, or use them to replace existing spreadsheets. 

Using templates

Google Sheets has a huge array of spreadsheet templates to help you keep track of everything without having to set up the logitics of tracking time, expenses or invoices. In fact, there's a whole template gallery you can browse to in order to find the best-fitting template for your particular project.

To find them, simply head to https://docs.google.com/spreadsheets and at the top of the page, you'll see a selection of templates you can use. To see more, click on the 'templat gallery' tab and you'll find a huge array, split into personal, work and education.

Although they may not be exactly to your specification, you can edit them and re-save to ensure you have quick access in the future.

Accessing Sheets offline

Although Google Sheets operates in the cloud, you can also create, view and edit files offline, making the application just as handy as using Office on your laptop.

However, you will need to tweak some settings in your Google Drive account before you can use Sheets offline. While connected to the internet, head to your Drive settings and in the offline section, tick he box to give Google permission to share your files offline.

Now, you'll need to grant Google permission to display the individual file while you're offline. To do this, open the Sheet you want to view offlineand click More. Here, you'll be able to choose to view and edit while offline. When it's available to view, you'll see a checkmark in the bottom corner of the left corner.

Formulas and shortcuts

One crucial tool for working with spreadsheets is formulas, used to quickly perform various mathematical calculations. Thankfully, Google has made sure that most of the formulae Excel users are familiar with will still work in Sheets.

Not only that, but if you start typing a formula in, Sheets will offer a list of suggestions, much like Google's autocomplete function. This means you won't have to memorise a laundry-list of formulae, which is a major plus.

On a similar note, pressing ctrl+/ on a PC, or cmd+/ on a Mac, will bring up a list of all Sheets' keyboard shortcuts, which make navigating around your documents a breeze.

Freezing

This tip isn't unique to Sheets, but it's a useful trick nonetheless, and can surprise even long-time spreadsheet users. By using the 'freeze' function, users can lock rows and columns, so they always remain in view no matter how far you scroll.

This is especially useful for large datasets, as it means that column and row headings can be kept on-screen, eliminating the need to keep checking which column or row represents which value.

Click the dark grey, L-shaped band in the top left corner of the table, and then drag the transparent outline to select which rows and/or columns you'd like to freeze. They'll appear at the top of your spreadsheet no matter how far you scroll.

Sharing

As you'd expect from a cloud-based app, Sheets includes robust sharing functionality for collaborating on spreadsheets. Click the giant blue 'share' button in the top right-hand corner, and the sharing menu will pop up.

The easiest way to collaborate on documents is through link sharing. A button at the top will generate a shareable link, which can be shared through the app of your choice. You can set various levels of permission, allowing users to view, edit and comment on the sheet.

You can also add people by inputting their email address, or by name if they're one of your contacts. For the security-conscious, there are also in-depth controls to block viewers from using functions such as downloading and printing the sheet.

Forms

One of the best features of Sheets is the integration with Google's Forms tool. This allows you to create in-depth forms and questionnaires, send them out to people, and then integrate the results into your spreadsheets.

To create a form, go to the tools menu and click 'create a form'. You can change the colour, add images and video, and divide the questions into different sections and types, such as multiple choice, checkbox, and short paragraph.

Once your form's done, you can send it out via email, shareable links, and social media, or embed it in a webpage. Form results appear in your spreadsheet instantly, stamped with the date and time, with basic analytics available through the 'form' tab.

Featured Resources

Activation playbook: Deliver data that powers impactful, game-changing campaigns

Bringing together data and technology to drive better business outcomes

Free Download

In unpredictable times, a data strategy is key

Data processes are crucial to guide decisions and drive business growth

Free Download

Achieving resiliency with Everything-as-a-Service (XAAS)

Transforming the enterprise IT landscape

Free Download

What is contextual analytics?

Creating more customer value in HR software applications

Free Download

Recommended

What is Amazon S3?
Amazon S3

What is Amazon S3?

16 May 2022
EDB unveils world-first openly governed Kubernetes Postgres operator
Cloud

EDB unveils world-first openly governed Kubernetes Postgres operator

13 May 2022
How the cloud primed Markerstudy for an M&A spree
Cloud

How the cloud primed Markerstudy for an M&A spree

9 May 2022
How to hold more productive meetings
Whitepaper

How to hold more productive meetings

5 May 2022

Most Popular

16 ways to speed up your laptop
Laptops

16 ways to speed up your laptop

13 May 2022
(ISC)2 launches free scheme to get 100,000 UK citizens into cyber security
Careers & training

(ISC)2 launches free scheme to get 100,000 UK citizens into cyber security

17 May 2022
Preparing for the 3G sunset
Network & Internet

Preparing for the 3G sunset

18 May 2022