Get More Out Of Google Sheets : Google Drive Secrets

Get More Out Of Google Sheets

Get More Out Of Google Sheets : Google Drive Secrets

It’s fair to say that we are fans of Google’s online productivity tools.

This feature was written in Google Docs, and much of the copy that appears in print has been shared using Google Drive.

So over the years, we’ve gathered more than our fair share of tips for getting the most out of the online office suite: here, we shares the best of them.

Get More Out Of Google Sheets

Get More Out Of Google Sheets

Limit users’ options 

Google Sheets is the perfect platform for sharing spreadsheets and forms within an organisation.

you might use it for expenses forms, for example. However, in this sort of scenario you probably want to restrict the options that can be entered in specific cells.

[quads id=6]

And happily, this is quite simple to implement in Sheets.

To do this, create a second sheet by clicking the “+” below your active spreadsheet and enter the acceptable variables.

Get More Out Of Google Sheets

For example, if you want staff to be able to pick from ten different expense types, such as mileage, accommodation, sustenance and so on, you might enter these categories in the first ten cells of column A.

  • You can create as many variable sets as you want for different tests.

Now switch back to the front sheet, which staff will use to file their claim, and select the range of cells that should only accept data that appears within your defined list of valid options.

Pick “Data validation” from the Data menu and use the box beside “Criteria” to locate the range of cells that contain the valid responses: in our example, with the ten expenses categories, that would be “Sheet2!A1:A10”.

[quads id=5]

By default, Sheets will show the options in a dropdown menu within each of the cells that you’re formatting so that staff can quickly click to pick.

In the unlikely instance you’d rather they typed the data directly, uncheck the box beside “Show drop-down list in cell”.

Also Read: Get More Out Of Google Docs – Google Drive Secrets

Import live data 

In Sheets, as in Microsoft Excel, you can easily reference data from other sheets in a single spreadsheet by using a reference like the one we used above.

So, to make a cell reflect the contents of cell B3 on the second sheet of the active spreadsheet, you’d enter the formula “=Sheet2!B3”.

If any formula or equation changes affect the contents of cell B3 on sheet 2, the cell containing the formula will automatically update to reflect that.

With Google Sheets, however, you can take this further, to an extent which isn’t always possible with Excel: as all of your assets are stored online, you can pull in data from any number of completely separate spreadsheets.

[quads id=4]

For example, you could create an end-of-year summary of business performance drawn from a dozen or more other spreadsheets that stored in Google Drive, or shared with you.

To do this, you can use a function called IMPORTRANGE, giving as parameters the source spreadsheet’s online address and the reference to the cell or range you want to incorporate, like this: 

=IMPORTRANGE(“ com/spreadsheets/ d/11g0d1Y6sWfLqzUM3 V9Hd wcVnjwOWvwnVii8DiNrI Gg/”,“Sheet1 !B4”). 

When you enter this, it will initially throw up a “#Ref” error; all you need to do is click back into the cell and click the Authorise button that appears so that it links the new spreadsheet with your existing data.

You can even import data from public sources, such as XML or RSS feeds, HTML documents or CSV files saved to the web.

Use the IMPORTXML, IMPORTFEED, IMPORTHTML and IMPORTDATA respectively for each of these.

Also Read: 5 Tech Stories Not to Miss ! (Apr 2019)

Explore your spreadsheet

We’ve seen how the Explore function built into Docs can simplify the task of researching content for documents.

You’ll find the same function in Sheets, and it works in a similar manner –

but it focuses on helping you get more out of your data by creating pivot tables, analysing trends or applying formatting.

It’s worth giving it a click just to see the options it gives you.

You’ll find the icon at the bottom of your window, on the same row as the Sheet tabs.

Also Read: Dropbox Business Advanced Review: Pros, Security….

[quads id=3]

Use a form 

If you haven’t created an online form before, you might not realise how simple they are to set up in Sheets.

Pick “Create a form” from the Tools menu and you’ll be walked you through the process of writing a series of questions, whose responses will then be fed into your spreadsheet.

You can gather answers using a range of advanced inputs, including draggable scales, multiple choice grids, predefined responses, dates, times and so on.

We suggest you avoid using the “Short answer” or Paragraph options unless there’s no alternative:

the more you lock things down at the data entry stage, the easier it will be to tabulate and analyse the results once they’re in.

Create a macro

Microsoft Excel has VBA; Sheets supports the powerful Apps Script environment which you can use to write all sorts of automation scripts.

If you don’t enjoy getting elbow-deep in code, you can also record and execute macros.

To set up a macro, pick “Record macro” from the Macros flyout on the Tools menu, then step through the actions you want to automate.

[quads id=2]

You can assign keyboard shortcuts to macros when saving them.

Our tip here is to make sure you select the appropriate option for saving either absolute or relative cell locations.

If you select the former, the macro will always operate on the exact cells you click; select the latter to make it take effect relative to your cursor position.

Also Read: How to spend the right amount on your next phone ?

Scale embedded pictures

As well as text, numbers and formulae, cells in Sheets can contain images.

Use the IMAGE(“url”) formula to embed an online image, with the following syntax:

=IMAGE(“ png”, 1)

The optional “,1” at the end tells Sheets to

scale the picture so it fits within the cell. Use 2 to stretch the image to fill the cell, or 3 to embed it at 100%, with the image cut off at the cell edges. To specify the size with pixel precision, use 4, followed by the height and width like this:

=IMAGE(“ png”, 4, 25, 100)

[quads id=9]

Also Read:


Please enter your comment!
Please enter your name here