If you carry a laptop around just so you can show clients your latest marketing forecasts or call up sales figures at company meetings, you can save yourself some bother by using Google Spreadsheets instead. The program provides editable spreadsheets that are compatible with Microsoft Excel—and, best of all, you and your colleagues can modify them simultaneously.
When you open a new document in Spreadsheets, you see a standard spreadsheet grid with resizable rows and columns, and the usual array of options for formatting. You enter text or numbers in spreadsheet cells, as you would with any other spreadsheet application. Using icons and menus on the toolbar, you can merge cells; apply borders and highlighting; change fonts, font sizes, and styles; and choose whether to wrap text within a cell. You may miss some of Excel’s more-advanced features, but for basic data formatting and calculations, the necessary features are there.
Formulas and More
You can enter Excel-style formulas manually, or click on the Formulas tab to see links that quickly insert Sum, Count, Average, Min, Max, and Product formulas. For more options, click on the More link to display a pop-up list of hundreds of formulas (see “Hundreds of Formulas”). Hover over a formula name to see its syntax, or click to insert it in the active cell. Spreadsheets lacks advanced Excel features such as macros, PivotTables, auditing, data filtering, and validation.
If you need to create a quick visual representation of your data, such as a pie chart or a bar graph, Spreadsheets can help. Begin by selecting the range of cells you want to chart, and then click on the pie-chart icon in the toolbar. Select the type of chart you want to make, and supply any optional data you choose—such as a title, or labels for horizontal and vertical axes. Click on Save Chart. The chart will appear right on top of your spreadsheet; you can move it or resize it by clicking and dragging. Note that although your spreadsheet can include multiple charts, a Spreadsheets chart cannot represent noncontiguous ranges of cells.
Importing and Exporting
You can upload Excel spreadsheets (.xls), comma-separated text files (.csv), and OpenDocument spreadsheets (.ods) in one of two ways (and as with Docs, there is a 500K size limit).
The first method is to go to the Docs & Spreadsheets home page, click on the Upload link, and follow the instructions for uploading a file, just as you would for a text document.
Alternatively, when you’re viewing an existing Spreadsheets file, you can choose Import from the File pop-up menu, click on Browse, locate the file on your hard drive, click on Open, and then click on the Open Now link. At press time, importing via e-mail was not available. Also, unlike Docs, Spreadsheets currently has no option that lets you e-mail a finished document directly from the program.
Working with Multiple Sheets
As in Excel, each document can have multiple sheets. To create a second sheet, click on the Add Sheet button at the bottom of the document window. You can rename or rearrange the current sheet by clicking on its name and choosing Rename, Move Left, or Move Right from the pop-up menu. You can also cut and paste cells from one sheet to another. For best results, use the toolbar’s Cut, Copy, and Paste icons.
The Spreadsheets program automatically saves your work after almost every change, as well as when you click on the Save & Close button. To see any previous version of a file, click on the Revisions tab and select the version you want from the Revision pop-up menu. Although Spreadsheets tracks revisions, it doesn’t highlight specific changes in each version, as Excel’s Track Changes feature does, nor does it let you compare two versions, as Docs does.
Sometimes you need input from several people—for instance, forecasts from the sales department, history from accounting, and strategy from project management. Spreadsheets makes it easy for multiple people to view and edit the same spreadsheet simultaneously. As with Docs, you can publish your spreadsheet to a Web page where anyone can see it. You can also export your spreadsheet in a variety of file formats, and invite other people to collaborate on it with you.
Use the form on the Collaborate tab to invite other people to edit or view a spreadsheet. To make the spreadsheet available to anyone with a Google account, click on Allow Anyone To View. The site produces a URL that you can send to anyone you want. To publish your spreadsheet for anyone to view without logging in, click on the Publish tab and then on Publish Now.
Have a quick question about a spreadsheet change? Because Google’s chat program,
Google Talk, is integrated with Spreadsheets, you can have a live chat with every collaborator who’s currently viewing or editing the same spreadsheet (see “Spreadsheet Chat”). Click on the Discuss tab to see a list of available collaborators. Type your message into the field at the bottom, and a message pops up on the other users’ screens, asking them to join the chat.
Because Spreadsheets shares many interface elements with Excel, most people should be able to pick up the basics without ever looking at the online help documentation. But a few interesting features may not be evi-dent at first glance.
Look Up Data on the Web
One thing Spreadsheets offers that Excel doesn’t is a pair of special functions that rely on Google’s extensive index of Web-based information. Access these functions by clicking on the Formulas tab, clicking on the More link, and then choosing Google in the Insert A Function box.
The first function, GoogleFinance, gives you an easy way to insert dynamic information about publicly traded companies and mutual funds. For example, you can enter
in a cell to display Apple’s current market capitalization. You can access stock prices, mutual-fund net asset values, and many other types of data with this function.
The second function, GoogleLookup, lets you insert a wide variety of statistical data that can be inferred from Web searches. This formula takes two arguments—an entity (what you want information about) and an attribute (the information you want about the entity). Entering
in a spreadsheet cell, for example, displays the current population of France (a little over 63,000,000). Entering
=GoogleLookup("Halle Berry", "place of birth")
gives a result of
Cleveland, Oh, and
=GoogleLookup("platinum", "atomic weight")
. GoogleLookup can’t display every piece of information you may want, but it can make entering some data much easier.
Fill In a Range of Values
Spreadsheets doesn’t have a feature comparable to Excel’s AutoFill, which intelli-gently extends a series into new rows or columns. But it does have a limited fill feature for columns. Start by entering a formula that refers to the cell immediately above it. For example, suppose cell D1 contains the value
. Enter a formula—for instance,
—in cell D2. Then select (using the mouse or keyboard) cell D2 (but not D1) and a few cells below it. Press control-D, and Spreadsheets applies the formula you entered to all the selected cells—if you entered
, cells D3 through D5 would contain
Put Part of a Spreadsheet on a Web Page
Sometimes you may want to embed just a portion of a spreadsheet in a Web page (or in, say, a blog entry), rather than providing a link to the whole file. To do this, go to the Publish tab and click on Publish Now. Next, click on the More Publishing Options link, which opens a new window. From the File Format pop-up menu, choose HTML To Embed In A Webpage.
Select the sheet containing the cells you want to publish, and enter a range of cells on that sheet (such as
). Click on Generate URL, and copy the code that appears in the box. Paste this code into your blog or Web page to display the selected portion of your spreadsheet there. (If you select the Publish tab’s Automatically Update Every 5 Minutes option, the spreadsheet copy on your blog or Web page will dynamically reflect changes made to your spreadsheet, without manual intervention.)
Is it for you?
Google Spreadsheets is a good choice for maintaining everything from shopping lists to product-launch plans, especially if you want several people to be able to view or modify them. And even though it lacks some of Excel’s flashier features, Spreadsheets can crunch numbers—including highly complex formulas—with the best of them. For traditional spreadsheet tasks such as tracking sales figures or account balances, Spreadsheets is a perfectly capable application. It’s also a good option for people who want basic spreadsheet capabilities but can’t afford Excel, or who often need to do quick calculations when they’re away from their regular computers—while traveling, for example.
Just like Docs, however, Spreadsheets is useless when you’re not connected to the Internet. And if you need high-powered features, you’ll have to turn to Excel or another full-featured spreadsheet application.Hundreds of Formulas: Google Spreadsheets’ formulas are available through this pop-up window, which appears when you click on the More link. Hover over a formula to see its syntax, or click to insert it in your spreadsheet.Spreadsheet Chat: Multiple people can not only edit a Google Spreadsheets document simultaneously, but also carry on a live chat while doing so. All collaborators who are currently viewing the spreadsheet appear in the Discuss tab.
All the Google applications described in this article are available free, à la carte, to individual users. But Google also offers them in a special package known as Google Apps (formerly called Google Apps for Your Domain). The big difference is that if you own a domain and you sign up for Google Apps, Google can essentially take over hosting your domain’s e-mail and Web site—for free or a very low price. You get the full power of Gmail, but with addresses in your own domain name (rather than gmail.com). And you get all the collaboration features of Docs, Spreadsheets, and Calendar for your group, as well as the ability to add as many users as you wish.
Google Apps comes in three editions. All three give a group administrator the ability to set up user accounts and control how users share various kinds of information within and outside the group’s domain. To give the programs a corporate flair, organizations can use their own logo on each page and provide companywide information on each user’s start page (see “Custom Apps”).
The free Standard Edition is probably what you’ll want if you just need to run a small business’s Web site or manage your family’s domain online. It includes Docs, Spreadsheets, Gmail, Calendar, Google Talk, and Page Creator (an online Web-design program), but it offers less storage (2GB per user) than the Premier Edition and no advanced features such as telephone support or conference-room scheduling. The Premier edition costs $50 per year—for an entire organization—and adds more e-mail storage space (10GB per user); it also includes telephone support and several other administrative features. The Education edition is free to accredited institutions. It’s similar to the Premier edition but includes less storage (only 2GB per user) and gives the option of removing advertisements from students’ e-mail. (Get
about the editions.)
To sign up for Google Apps for a group, visit
www.google.com/a/, click on the Get Started button, and follow the prompts. If you don’t already own your own domain name, you can search for and purchase one during sign-up for $10 per year. You can use Google Apps without interrupting your domain’s existing Web and e-mail services, but if you want Google to host your domain’s e-mail or Web site, you must follow Google’s instructions for modifying your DNS records.
Joe Kissell is the senior editor of
and the author of
Real World Mac Maintenance and Backups
(Peachpit Press, 2007).
Custom Apps: When you subscribe to Google Apps, you can add your corporate logo to each user’s start page; users can also add or rearrange items on their individual pages, including news items, weather reports, and access to company resources (such as a phone directory).