The best Google Sheets tips and shortcuts

When it comes to spreadsheets, a lot of business users are evangelical about Microsoft Excel and it's easy to see why. It comfortably remains the go-to application for any business with more complex data handling needs.

In terms of users Microsoft still flattens its competition but an increasing number of businesses are testing out the G-Suite to take advantage of its real-time collaboration features and unbeatable usability. (For a fuller analysis, see our full review of Google Sheets vs Microsoft Excel.)

For all the G-Suite users out there, we run through all of the most interesting things you can do with the application, as well as the best tips and tricks for getting the most out of Google Sheets.

Additional reporting by Laurie Clarke

Read next: How to use Google Analytics effectively

Add a drop-down menu to your cells

Add a drop-down menu to your cells

Finding the best way to display your data is often half the battle. Using drop-down menus is a space-saving technique and should make your data much more readable and intuitive.

Here's how to do it:

- In your Sheet, click on the cell (in the sheet) you want to put the drop-down menu in.

- Next, right-click and go to 'data validation' and in 'Criteria:' make sure 'list from a range' is selected.

- You'll then need to add in the cell range you want to filter into the drop-down menu. So if you want cells A2,3 and 4, you can enter either 'A2:A4' or be more specific depending on what sheet you want to pull the data from, so something like 'Sheet2!A1:A4'.

Import data from another spreadsheet

Import data from another spreadsheet

Even though most businesses use either Google or Microsoft, you'll still find formatting issues between both firms' spreadsheet offerings. This makes collaborating with business teams using opposing software a little tricky.

To combat this, here's how to import spreadsheets to Google Sheets:

Go to 'File', then 'Import'. Next, select the data you want. Then select the file and click 'Open'.

However, if you want to import data from one Google Sheet to another, you can use the Import Range function.

To do this you should write in a cell (not the Sheet you want to import into) "=IMPORTRANGE (insert Google Sheet URL), range_string". You can simply find the Sheet URL by clicking on the Google Sheet you want to take data from and copying its URL at the top and pasting it in.

And the range string refers to the cells from the other Sheet that you wish to import across. So your formula could look something like this:

=IMPORTRANGE (https://docs.google.com/spreadsheets/d/1hr5t8P-HjfHfkYSOBKtsgGKEaskfDFEOAg/edit#gid=0, Sheet4!A1:C35

Create an app with Google Sheets

Create an app with Google Sheets

AppSheets is an add-on available in Google Sheets which lets you put your data into a readable app. This is best for simple apps displaying listicle data or simple table data.

Here's how:

1. Make sure your spreadsheet has a title

2. Insert your data

4. From the menu, choose "add-ons > AppSheet > Launch" and click "GO" in the sidebar

5. The browser opens the AppSheet site and your app is auto-created.

You'll then be able to use the dashboard to design your app.

(See also: How to market apps)

Make your own custom Google Map

Make your own custom Google Map

With Google Sheets, you'll be able to create a custom Google Map packed full of your own data. This is very useful for tracking the location of customers and also common places of interest relating to your business.

1. Download the 'Mapping Sheets' add on (just click on the 'add-ons' button, go to 'get add-ons' and type it in)

2. Enter address data into the Sheet, the headings name, category and address will work best

3. Select these three categories and click 'add-ons' then 'Mapping Sheets' and finally 'start mapping'.

You will now be able to view and build your map and select the way the information is presented via the title, location and filter buttons on the right hand side.

Create surveys and collect the data

Create surveys and collect the data

Many businesses rely on surveys to gather important information on their customers and markets but survey creation and gathering its data can be a chore. Here's how to do it faster and easier:

Click 'Insert' and then 'Form'.

You'll then be prompted to 'ask questions'. This will open a new window and you can add questions and their format (e.g. multiple choice).

Create a data heat map

Create a data heat map

A data heat map allows users to identify cells and important information depending on its colour. This helps businesses see trends and patterns within its own data. Here's how to do it:

1. Input and select data in Google Sheets

2. Go to format and click 'conditional formatting'

3. This will open a side-panel. If you select 'colour scale' you'll be able to choose a colour for the 'minpoint', 'midpoint' and 'maxpoint'

4. You can add new rules or just click done.

Create a chatbot

Create a chatbot

If you run a customer-facing business you might feel the pull of creating a chatbot. And why wouldn't you? They can provide a simple way of communicating with customers and can manage simple tasks to keep things ticking over.

If you're really invested in building a chatbot, you might want to check out these building platforms, but Google Sheets is a good platform to test the water.

Here's how to do it on Google Sheets:

1. Get the Chatbot add-on from AppSheet

2. Fill in at least five lines of data (or use a sample set for inspiration)

3. Select 'Go' and a new window will open. From there you can use the dashboard to perfect your chatbot

Create QR codes
Photo: Google

Create QR codes

QR codes are ubiquitous these days. They're a great way to share contact details, URLs, product information, and other brief amounts of text.

1. Install the QR Code Generator add-on

2. Input the data required (i.e. name of customer and unique code)

3. Click 'add-ons' and then 'QR Code Generator'. From there you can click 'generate' to create QR codes.

Keyboard shortcuts
Photo: Google

Keyboard shortcuts

Google Sheets is packed full of handy little shortcuts, but you have to be in the know first. Here's how to pull up the entire list of shortcuts:

Windows: Control and /
Mac: Command and /

Get more currencies

Get more currencies

To access more currencies couldn't be simpler, but not many people know how.

Click the '123' button and then 'more formats', you'll see a tab marked 'more currencies' click that and you can choose from a huge list.

Email notifications
Photo: Google

Email notifications

This tip might not be for everyone, depending on how often a sheet is updated, but here's how to turn on email notifications on Google Sheets.

Go to 'tools' then 'notification rules'. From there you can choose whether to be notified when a change is made or when a user submits a form. You can also limit notifications to a 'daily digest' so not to be bombarded with emails.

Use templates

Use templates

Like all Google Apps, a variety of templates can be used, from annual budget documents and travel planners to invoices and expense reports. Here's how to do it:

Go to 'file', then 'new' and select 'from template', from there you can browse all available templates.

Make the most out of Twitter

Make the most out of Twitter

With social media playing an obvious part in most businesses, the Twitter Curator add-on can save, search, filter and curate Tweets.

Searches can centre on hashtags, @mentions and general search terms.

To enable this add-on, download it from the add-on centre, login to Twitter and allow the add-on to connect.

Insert images into your sheet

Insert images into your sheet

Sickened by the sight of rows upon rows of numbers? Why not spruce up your spreadsheet with a picture or two. Add style and flair to any sheet project by clicking on the =IMAGE and pasting the URL of your chosen image within the brackets.

Spell check in Google Sheets

Spell check in Google Sheets

Not automatically correcting or highlighting spelling mistakes could leave fast typers a little red-faced. Now Google Sheets can spell-check entire sheets or individual cells for you.

Highlight the chosen area (or don't for the whole sheet) and go to 'tools', from there click 'spelling' and it will open a panel with a familiar spell check interface.

Lock up your data

Lock up your data

Although the collaborative features are some of the most attractive about Google Sheets, there is some data in any spreadsheet that is not to be tampered with. To prevent any unwanted changes on certain spreadsheets or certain data sets, simply access the Data > Protected sheets and ranges option. You can then click to highlight the data you wish to protect.

Copyright © 2018 IDG Communications, Inc.