Hands on: Plan with Google spreadsheets

Excel isn’t the only spreadsheet in town – you can take figures online with Google

Imagine you’re trying to plan a family reunion. Your parents have retired in Australia. Your children are spread around Europe. Great Uncle Fred is in Canada.

There are lists to be made, schedules to be planned, itineraries to be proposed and adjusted. All jobs for a spreadsheet. But how can you all review and amend it, working together at the same time? A practical solution is a Google workbook that can be shared around the world.

Although you can only work on a Google spreadsheet online, it’s possible to start by creating in Excel the forms or lists you need offline. Then go online, open a new Google spreadsheet, and import your workbook.

Are there limitations? There are only a few, really. The workbook can have up to 20 worksheets, providing all the worksheets together don’t use more than 10,000 rows, or 256 columns, or 50,000 used cells. Up to 20 spreadsheets can be opened by all approved users at one time.

Bear in mind that although you can start in Excel, and download a Google spreadsheet into Excel to use offline, the folks you share a Google spreadsheet with don’t have to have Excel on their computers. In fact, they don’t have to own a computer.

They can pop into an internet café and, if they have registered and you’ve approved them to participate, they can view a Google spreadsheet and amend it or send you comments in real time. This means that even if a computer is lost or damaged, or the hard drive on their PC crashes, any Google spreadsheets are safe and can be accessed any time anywhere.

Safe and sound
This raises the matter of security. It’s wise to recognise that anything of value that you expose on the internet could eventually become insecure, but for documents that are of no value to anyone else, this is a good scheme. You are given normal privacy by Google’s registration system. Both you and those you wish to share spreadsheets with can go to https://www.google.com/accounts and register. All that’s needed is an email address where you can be reached and a password.

After that, enter the language you wish to use, and your time zone, on the Settings page. Google documents can be written in 14 languages and the spell check supports these and 16 more. The time zone is needed for timestamps of your files, for histories of revisions, and so that time-related spreadsheet functions such as NOW() will work.

The Settings page also has a Spreadsheets tab but there were no options offered at the time of writing. This is very much a work in progress; in fact under the title, Google Spreadsheets, in Flyspeck Light, it says Beta.

Getting back to the example of a weekend family reunion, you can lay out the worksheets first in Excel offline and then upload them. If you are using Excel 2007, you can either Save as an Xls file, or Copy and Paste the data into a Google spreadsheet. Most formatting entered in Excel will be recognised by a Google spreadsheet.

If you don’t want to use Excel, you can go online and start a new Google spreadsheet. New formatting is easy to arrange using the buttons under the Edit tab, with a choice of fonts and a palette of colours for fonts and cell backgrounds.

If your Excel workbook has several worksheets with named tabs, these will appear when you import it into a fresh Google spreadsheet file.

Once the file is stored, you can choose whom you wish to see it. Just enter their email addresses and specify whether they can only view the file or be a collaborator, that is someone who can amend the file.

You can also email them an invitation to view the file with a click of a button. There’s also a discussion option, so approved people can view and comment without making amendments.

Whenever you return to the Google documents site, you can see a listing of the spreadsheets you have stored, showing the dates they were last edited, and who is permitted to view or amend them.

After initially naming a Google spreadsheet file there is no need to keep saving it because it’s automatically saved in a secure online storage facility as you go. Even if you accidentally erase something, you can click the Revisions tab and step through all the changes made by yourself or others.

A pop-up box gives the time of each revision, the name of the person who made it, and the type of revision, such as ‘Made edits’ or ‘Sorted’. This is extremely helpful when a number of people are making changes to a spreadsheet.

Publishing
You can publish a Google spreadsheet as a web page if you like, or embed a spreadsheet into one of your existing web pages. It can be a snapshot of the Google spreadsheet or a dynamic version that will automatically update when your spreadsheet is changed.

You can also save a Google spreadsheet file to your own computer in XLS, CSV, or HTML formats. In other words, you can’t save Google spreadsheet software to your computer, but you can save the data to display in other software.

Going the other way, Google documents and spreadsheets can import XLS, CSV and RTF files. They also recognise the Open Office/Star Office Opt and Ods extensions. Google reckons that all your formulas and formatting will be understood.

Although Google spreadsheets don’t currently import or export in the new Office 2007 spreadsheet format, they do have a gadget feature, as in Windows Vista. You can have a Google spreadsheets Gadget on the homepage of your website. From here it will list your most recently active spreadsheets so you can choose and activate them.

There is even a Google application program interface (API) called Gdata, which enables software writers to use a Google spreadsheet as a database, from which the records can be retrieved by their own application software. Gdata can also be used to access an external graphing library.

All the most basic Excel shortcuts like Ctrl & A, B, C, I, K and S for Select All, Bold, Copy, Italicise, Insert Link and Save respectively are recognised in a Google spreadsheet.

You don’t need any more software than a browser to access and use Google spreadsheets. Internet Explorer 6 and higher, and recent versions of Mozilla Firefox and Netscape will work – providing cookies and Javascript are enabled. Safari and Opera are not currently supported though.

Google spreadsheets also offer simple charting facilities. You can highlight a table and, via the Insert menu, automatically create a column, bar, line, pie or scatter chart.

There are also five sub-type variations. The formatting options are limited compared with Excel, but you can produce perfectly adequate graphs.

Google spreadsheets might well currently be compared to the Microsoft Works Spreadsheet module.

They only have a fraction of the features of a spreadsheet such as Excel or Lotus 1-2-3, but their value is the ability to be shared online in real time by a selected group of people, without any ownership of spreadsheet software or even a computer.

Google’s extra functions
Googlefinance() lets you download live stock information to your spreadsheet. Currently data is only available from the New York Stock Exchange, but doubtless other world exchanges, such as thor the stock (like MSFT for Microsoft) and the information you want.

e FTSE, will eventually be offered. The function has two arguments: the stock exchange symbol fSo in different columns you could store the current price, the high and low for the day, volume of sales, earnings per share and other common statistics for a particular share.

Googlelookup() helps you insert encyclopaedic information in your spreadsheet on any subject. There are two arguments: entity, and attribute. For example, to find the population of London you would enter Googlelookup(“London”,”population”). This function sounds like a good idea but, unlike normal Googling, only one nugget of data is downloaded, so you have to be very specific about what you ask for.