5 Tips for Google Spreadsheets

I see posts on business oriented sites almost daily shelling out tips for every inch of the Microsoft Office Suite. An equal number of sites talk about Gmail, giving tips and ways to be more productive while using it. What about Google Docs? Docs can do some equally fancy stuff too.

Slowly but surely Google has been adding a feature here and a feature there. Some have been bigger steps than others; all of them are chipping away at the reasons you need to buy an office suite. I’m not going to get on a soapbox and shout to the world. I will however, give you a few nifty tips to make using Google Docs spreadsheets a little easier.

1. Lookup

Much like other spreadsheet programs, Docs can do calculations and formulas. The question to ask is the other programs would be, “Do you have built in Google search?” No? Hmmm…. well anyway, here are a couple handy formulas to enter into the desired cell to utilize Google’s search. Make sure to keep the quotation marks surrounding the terms you are adding.

The values will automatically update as the information changes; imagine the possibilities.

=GoogleLookup(“entity”; “attribute”)

Replace entity with something like Ireland and attribute with population.

=GoogleFinance(“symbol”, “attribute”)

An example for this function would be:  =GoogleFinance(“AAPL”, “Price”) The result would end up being what ever the current stock price is.

=GoogleTranslate(“text”, “source language”,”target language”)

The translation function is really cool. It allows you to translate a word or a few sentences from English to Modern Greek or Chinese to Spanish. The most difficult part is having to use ISO 639-1 country abbreviations.

For easy access check out this list of all the functions available.

2. Google Doc Forms

A Google Docs Form is a very simple way to add a survey to an email or website. The basic idea behind these forms is to gather information and automatically aggregate the results into a spreadsheet. You also have the choice to get an email notification when someone completes the form or add a gadget to your iGoogle page to track all of your surveys.

Creating a form is really straightforward.

1. From the main Google Docs screen, choose New — Form

2. If you don’t like the plain blue and white color scheme, you can pick a new one by clicking Theme.

3. Click apply on the new theme to start editing.

4. From there, simply type in the question, choose the type of question you’d like it to be and if it is required to complete the survey.

5. If you click the Share button, there is an option to email the survey or even get the code to embed it into a web page on your site.

3. List View

List view is a fast loading version of a spreadsheet you can publish or embed to your website. The settings you determine in the Share menu will carry over to the spreadsheet you embed.

One of the coolest parts is the spreadsheet is auto-updating. If, for example, you have stock quotes or dollar amounts from a fundraiser; these will all be updated on the embeded version instantly.

If you are a Google Sites user, you can add a editable version of the spreadsheet onto the page by going to the Insert menu.

List view provides a few basic ways to modify data:

  • edit cells
  • add rows
  • filter and sort data
  • cells with formulas can’t be edited in this view

4. From Your Mobile

If you have an Android, iPod touch/iPhone or Nokia S60, you can access and manipulate your Google Doc spreadsheets. I was a little disappointed there isn’t a Blackberry or Windows Mobile access, but I’ll live.

  • Create shortcuts to your spreadsheets on your home screen
  • Edit cells
  • Add data
  • Sort columns to give you relevant data first

5. Styling

Even though it isn’t an option in a drop down menu, the forms can be modified. You do have over 70 themes available, but if that isn’t enough for you, here are a few tips to spice them up even more by Gill Smith.

What are your productivity tips for Google spreadsheets and forms?

image credit: TaopaiC